.Service Desk Cabling Query v2019.7

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