(2022.1) Service Desk Cabling Query

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