View: CSS_BRM_3D_DEFECT_V

View Text

SELECT DECODE( AUD.MAX_AUDIT_HISTORY_ID
, DEA.AUDIT_HISTORY_ID
, 'PRESENT'
, 'HISTORIC' ) RECORD_STATUS
, 'CSS_BRM_3D_DEFECT_V' OBJECT_TYPE
, DEA.DEFECT_ID OBJECT_ID
, DEA.DEFECT_ID DEFECT_ID
, DEA.CREATED_BY CREATED_BY
, DEA.LAST_UPDATE_DATE CREATION_DATE
, DEA.LAST_UPDATED_BY LAST_UPDATE_BY
, DEA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, DEA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, DEA.DEFECT_NUMBER
, DEA.TERRITORY_ID
, PCA.PROBLEM_CATEGORY_ID
, PCA.NAME PROBLEM_CATEGORY_NAME
, PTY.PROBLEM_TYPE_ID
, PTY.NAME PROBLEM_TYPE_NAME
, DEA.PHASE_ID
, PHA.NAME PHASE_NAME
, DEA.STATUS_ID
, STA.NAME STATUS_NAME
, DEA.SEVERITY_ID
, SEV.NAME SEVERITY_NAME
, DEA.PRIORITY_ID
, PRI.NAME PRIORITY_NAME
, PROD.ORGANIZATION_ID INV_ORGANIZATION_ID
, DEA.PRODUCT_INV_ITEM_ID PRODUCT_INV_ITEM_ID
, PROD.DESCRIPTION PRODUCT_DESCRIPTION
, DEA.PRODUCT_REVISION PRODUCT_REVISION
, DECODE(DEA.PRODUCT_REVISION
, NULL
, DEA.PRODUCT_REVISION_DESC
, PROD_REV.REVISION_DESCRIPTION) PRODUCT_REVISION_DESC
, NULL RESOLUTION_CODE
, NULL RESOLUTION_MEANING
, DECODE(DEA.FIRST_FOUND_IN_REVISION
, NULL
, TO_NUMBER(NULL)
, DEA.PRODUCT_INV_ITEM_ID) FIRST_FOUND_IN_INV_ITEM_ID
, DECODE(DEA.FIRST_FOUND_IN_REVISION
, NULL
, NULL
, PROD.DESCRIPTION) FIRST_FOUND_IN_DESCRIPTION
, DEA.FIRST_FOUND_IN_REVISION FIRST_FOUND_IN_REVISION
, DECODE(DEA.FIRST_FOUND_IN_REVISION
, NULL
, DEA.FIRST_FOUND_IN_REVISION_DESC
, FFI_REV.REVISION_DESCRIPTION) FIRST_FOUND_IN_REVISION_DESC
, DEA.COMPONENT_INV_ITEM_ID COMPONENT_INV_ITEM_ID
, COMP.DESCRIPTION COMPONENT_DESCRIPTION
, DEA.COMPONENT_REVISION COMPONENT_REVISION
, DECODE(DEA.COMPONENT_REVISION
, NULL
, DEA.COMPONENT_REVISION_DESC
, COMP_REV.REVISION_DESCRIPTION) COMPONENT_REVISION_DESC
, DEA.SUB_COMPONENT_INV_ITEM_ID SUB_COMP_INV_ITEM_ID
, SCOM.DESCRIPTION SUB_COMP_DESCRIPTION
, DEA.SUB_COMPONENT_REVISION SUB_COMPONENT_REVISION
, DECODE(DEA.SUB_COMPONENT_REVISION
, NULL
, DEA.SUB_COMPONENT_REVISION_DESC
, SCOM_REV.REVISION_DESCRIPTION) SUB_COMPONENT_REVISION_DESC
, DECODE(DEA.FIX_BY_LEVEL
, 1
, DEA.PRODUCT_INV_ITEM_ID
, 2
, DEA.COMPONENT_INV_ITEM_ID
, 3
, DEA.SUB_COMPONENT_INV_ITEM_ID) FIX_BY_INV_ITEM_ID
, DECODE(DEA.FIX_BY_LEVEL
, 1
, PROD.DESCRIPTION
, 2
, COMP.DESCRIPTION
, 3
, SCOM.DESCRIPTION) FIX_BY_DESCRIPTION
, DEA.FIX_BY_REVISION FIX_BY_REVISION
, DECODE(DEA.FIX_BY_REVISION
, NULL
, DEA.FIX_BY_REVISION_DESC
, FIXB_REV.REVISION_DESCRIPTION) FIX_BY_REVISION_DESC
, DECODE(DEA.FIXED_IN_LEVEL
, 1
, DEA.PRODUCT_INV_ITEM_ID
, 2
, DEA.COMPONENT_INV_ITEM_ID
, 3
, DEA.SUB_COMPONENT_INV_ITEM_ID) FIXED_IN_INV_ITEM_ID
, DECODE(DEA.FIXED_IN_LEVEL
, 1
, PROD.DESCRIPTION
, 2
, COMP.DESCRIPTION
, 3
, SCOM.DESCRIPTION) FIXED_IN_DESCRIPTION
, DEA.FIXED_IN_REVISION FIXED_IN_REVISION
, DECODE(DEA.FIXED_IN_REVISION
, NULL
, DEA.FIXED_IN_REVISION_DESC
, FIXI_REV.REVISION_DESCRIPTION) FIXED_IN_REVISION_DESC
, TO_NUMBER(NULL) DEV_OWNER_ID
, NULL DEV_OWNER_NAME
, DEA.PHASE_OWNER_ID
, DEA.PHASE_OWNER_RESOURCE_TYPE
, SUBSTR(CSS_DEF_RESOURCE_UTIL_PVT.GET_RESOURCE_NAME(DEA.PHASE_OWNER_ID
, DEA.PHASE_OWNER_RESOURCE_TYPE)
, 1
, 2000) PHASE_OWNER_NAME
, DEA.FILED_DATE
, DEA.RESOLVED_DATE
, DEA.CLOSED_DATE
FROM (SELECT DEFECT_ID
, MAX(AUDIT_HISTORY_ID) MAX_AUDIT_HISTORY_ID
FROM CSS_DEF_AUDIT_HISTORY_B GROUP BY DEFECT_ID) AUD
, CSS_DEF_AUDIT_HISTORY_B DEA
, CSS_DEF_PHASES_VL PHA
, CSS_DEF_PROB_TYPES_VL PTY
, CSS_DEF_PROB_CATEGORIES_VL PCA
, CSS_DEF_STATUSES_VL STA
, CSS_DEF_SEVERITIES_VL SEV
, CSS_DEF_PRIORITIES_VL PRI
, 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 AUD.DEFECT_ID = DEA.DEFECT_ID
AND DEA.PHASE_ID = PHA.PHASE_ID
AND DEA.PROBLEM_TYPE_ID = PTY.PROBLEM_TYPE_ID
AND PTY.PROBLEM_CATEGORY_ID = PCA.PROBLEM_CATEGORY_ID
AND DEA.STATUS_ID = STA.STATUS_ID(+)
AND DEA.SEVERITY_ID = SEV.SEVERITY_ID
AND DEA.PRIORITY_ID = PRI.PRIORITY_ID(+)
AND DEA.PRODUCT_INV_ITEM_ID = PROD.INVENTORY_ITEM_ID
AND PROD.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL)
AND DEA.COMPONENT_INV_ITEM_ID = COMP.INVENTORY_ITEM_ID(+)
AND (DEA.COMPONENT_INV_ITEM_ID IS NULL OR COMP.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL))
AND DEA.SUB_COMPONENT_INV_ITEM_ID = SCOM.INVENTORY_ITEM_ID(+)
AND (DEA.SUB_COMPONENT_INV_ITEM_ID IS NULL OR SCOM.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL))
AND DEA.PRODUCT_INV_ITEM_ID = PROD_REV.INVENTORY_ITEM_ID(+)
AND DEA.PRODUCT_REVISION = PROD_REV.REVISION(+)
AND DEA.PRODUCT_INV_ITEM_ID = FFI_REV.INVENTORY_ITEM_ID(+)
AND DEA.FIRST_FOUND_IN_REVISION = FFI_REV.REVISION(+)
AND DEA.COMPONENT_INV_ITEM_ID = COMP_REV.INVENTORY_ITEM_ID(+)
AND DEA.COMPONENT_REVISION = COMP_REV.REVISION(+)
AND DEA.SUB_COMPONENT_INV_ITEM_ID = SCOM_REV.INVENTORY_ITEM_ID(+)
AND DEA.SUB_COMPONENT_REVISION = SCOM_REV.REVISION(+)
AND DECODE(DEA.FIXED_IN_LEVEL
, 1
, DEA.PRODUCT_INV_ITEM_ID
, 2
, COMPONENT_INV_ITEM_ID
, 3
, SUB_COMPONENT_INV_ITEM_ID) = FIXI_REV.INVENTORY_ITEM_ID(+)
AND DEA.FIXED_IN_REVISION = FIXI_REV.REVISION(+)
AND DECODE(DEA.FIX_BY_LEVEL
, 1
, DEA.PRODUCT_INV_ITEM_ID
, 2
, COMPONENT_INV_ITEM_ID
, 3
, SUB_COMPONENT_INV_ITEM_ID) = FIXB_REV.INVENTORY_ITEM_ID(+)
AND DEA.FIX_BY_REVISION = FIXB_REV.REVISION(+)

Columns