Service Desk Cabling Query

This AdHoc Query was created to replace the Service Desk Cabling Report when it was removed from the built-in reports. This AdHoc has notable performance improvements on the original report. This report will display all Cabling Records associated with Service Desk Records.
Service Desk Cabling for Oracle and MySQL

Service Desk Cabling

-- This query is valid for MySQL AND Oracle
SELECT
    -- SERVICE ORDER
    SD.SD_NUMBER,
    L_SD_STATUS.VALUE AS SD_STATUS,
    SD.DESCRIPTION AS SD_DESCRIPTION,

    -- SERVICE DESK ACTION
    LPAD(SD_ACTIONS.SDA_NUMBER, 3, 0) AS SD_ACTION_NUMBER,
    L_SD_ACTIONS_TYPE.VALUE AS SD_ACTION_TYPE,
    L_SD_ACTIONS_STATUS.VALUE AS SD_ACTION_STATUS,
    COALESCE(SD_ACTION_SERVICE.SERVICE_ID_FMT, SD_ACTION_SERVICE.SERVICE_ID) AS SD_ACTION_SERVICE,

    -- SERVICE DESK CABLING
    SD_CABLE.INSTANCE AS SD_CABLE_INSTANCE,
    L_SD_CABLE_TYPE.VALUE AS SD_CABLE_TYPE,

    -- FROM EQUIPMENT
    COALESCE(FROM_EQUIPMENT.EQUIPMENT_ID, FROM_EQUIPMENT.ASSET_TAG) AS FROM_EQUIPMENT_ID,
    FROM_EQP_CATALOG.DESCRIPTION AS FROM_EQP_DESCRIPTION,
    COALESCE(FROM_PPL.ALIAS, FROM_PPL.PORT) AS FROM_PPL,
    L_FROM_PPL_STATUS.VALUE AS FROM_PPL_STATUS,

    -- CABLES
    CABLES.NAME AS CABLE,
    PAIRS_STRANDS.NUM AS PAIR_STRAND,

    -- TO EQUIPMENT
    COALESCE(TO_EQUIPMENT.EQUIPMENT_ID, TO_EQUIPMENT.ASSET_TAG) AS TO_EQUIPMENT_ID,
    TO_EQP_CATALOG.DESCRIPTION AS TO_EQP_DESCRIPTION,
    COALESCE(TO_PPL.ALIAS, TO_PPL.PORT) AS TO_PPL,
    L_TO_PPL_STATUS.VALUE AS TO_PPL_STATUS,

    -- BACKBONE
    COALESCE(BACKBONE_SERVICE_REAL.SERVICE_ID_FMT, BACKBONE_SERVICE_REAL.SERVICE_ID) AS BACKBONE,

    -- SERVICE ORDER DATES
    SD.CREATED_DATE AS SD_CREATED_DATE,
    SD.DUE_DATE AS SD_DUE_DATE,
    SD.COMPLETE_DATE AS SD_COMPLETED_DATE,
    SD.FINALIZED_DATE AS SD_FINALIZED_DATE,

    -- SERVICE DESK ACTION DATES
    SD_ACTIONS.CREATED_DATE AS SD_ACTION_CREATED_DATE,
    SD_ACTIONS.DUE_DATE AS SD_ACTION_DUE_DATE,
    SD_ACTIONS.COMPLETE_DATE AS SD_ACTION_COMPLETED_DATE,
    SD_ACTIONS.FINALIZED_DATE AS SD_ACTION_FINALIZED_DATE

-- SERVICE DESK CABLING
FROM SERVICE_DESK_CABLING SD_CABLE
JOIN LISTS L_SD_CABLE_TYPE
    ON L_SD_CABLE_TYPE.RECID = SD_CABLE.SD_CABLE_ACTION_LISTS_RECID

-- FROM EQUIPMENT
LEFT JOIN EQUIPMENT FROM_EQUIPMENT
    ON FROM_EQUIPMENT.RECID = SD_CABLE.FROM_EQUIPMENT_RECID
LEFT JOIN EQP_CATALOG FROM_EQP_CATALOG
    ON FROM_EQP_CATALOG.RECID = FROM_EQUIPMENT.EQP_CATALOG_RECID
LEFT JOIN PORTS_PINS_LENS FROM_PPL
    ON FROM_PPL.RECID = SD_CABLE.FROM_PORTS_PINS_LENS_RECID
LEFT JOIN LISTS L_FROM_PPL_STATUS
    ON L_FROM_PPL_STATUS.RECID = FROM_PPL.PAIR_STATUS_LISTS_RECID

-- CABLES/PAIRS/STRANDS
LEFT JOIN CABLES
    ON CABLES.RECID = SD_CABLE.CABLES_RECID
LEFT JOIN PAIRS_STRANDS
    ON PAIRS_STRANDS.RECID = SD_CABLE.PAIRS_STRANDS_RECID

-- TO EQUIPMENT
LEFT JOIN EQUIPMENT TO_EQUIPMENT
    ON TO_EQUIPMENT.RECID = SD_CABLE.TO_EQUIPMENT_RECID
LEFT JOIN EQP_CATALOG TO_EQP_CATALOG
    ON TO_EQP_CATALOG.RECID = TO_EQUIPMENT.EQP_CATALOG_RECID
LEFT JOIN PORTS_PINS_LENS TO_PPL
    ON TO_PPL.RECID = SD_CABLE.TO_PORTS_PINS_LENS_RECID
LEFT JOIN LISTS L_TO_PPL_STATUS
    ON L_TO_PPL_STATUS.RECID = TO_PPL.PAIR_STATUS_LISTS_RECID

-- BACKBONE
LEFT JOIN BACKBONE_SERVICES
    ON BACKBONE_SERVICES.RECID = SD_CABLE.BACKBONE_SERVICES_RECID
LEFT JOIN SERVICES BACKBONE_SERVICE_REAL
    ON BACKBONE_SERVICE_REAL.RECID =  BACKBONE_SERVICES.SERVICES_RECID

-- SERVICE ORDER
JOIN SERVICE_DESK SD
    ON SD.RECID = SD_CABLE.SERVICE_DESK_RECID
JOIN LISTS L_SD_STATUS
    ON L_SD_STATUS.RECID = SD.SD_STATUS_LISTS_RECID

-- SERVICE DESK ACTION
JOIN SERVICE_DESK_ACTIONS SD_ACTIONS
    ON SD_ACTIONS.RECID = SD_CABLE.SERVICE_DESK_ACTIONS_RECID
JOIN LISTS L_SD_ACTIONS_TYPE
    ON L_SD_ACTIONS_TYPE.RECID = SD_ACTIONS.SD_ACTION_LISTS_RECID
JOIN LISTS L_SD_ACTIONS_STATUS
    ON L_SD_ACTIONS_STATUS.RECID = SD_ACTIONS.SD_STATUS_LISTS_RECID
JOIN SERVICES SD_ACTION_SERVICE
    ON SD_ACTION_SERVICE.RECID = SD_ACTIONS.SERVICES_RECID