Page Comparison

-- Inventory Reorder By Location with Site and All Warehouse totals for MySQL
SELECT
  CONCAT (`EQP_CATALOG_RECID`, '_', `SITE_RECID`, '_', `WHS_RECID`) AS `RECIDS`,
  -- Warehouse path
  `WAREHOUSE` AS `WAREHOUSE`,
  -- Equipment Catalog info
  `PART_NUMBER` AS `PART_NUMBER`,
  `DESCRIPTION` AS `DESCRIPTION`,
  `MANUFACTURER` AS `MANUFACTURER`,
  `MANU_PART_NUM` AS `MANU_PART_NUM`,
  -- Current warehouse counts/quantities
  `ONHAND_CNT` AS `ONHAND_CNT`,
  `ONHAND_QTY` AS `ONHAND_QTY`,
  `PENDING_CNT` AS `PENDING_CNT`,
  `PENDING_QTY` AS `PENDING_QTY`,
  `ONORDER_CNT` AS `ONORDER_CNT`,
  `ONORDER_QTY` AS `ONORDER_QTY`,
  -- Available prices
  GROUP_CONCAT(DISTINCT(`COST`)) AS `COST_OPTIONS`,
  -- Unit of measure
  `QTY_UNITS` AS `QTY_UNITS`,
  -- Reorder threshold
  `TARGET_QTY` AS `TARGET_QTY`,
  -- Quantity required to meet pending orders and inventory specifications at current warehouse location
  `ORDER_QTY` AS `ORDER_QTY`,
  -- Quantity in excess of pending orders and inventory specifications at current warehouse location
  `EXCESS_QTY` AS `EXCESS_QTY`,
  -- Available vendors
  GROUP_CONCAT(DISTINCT(`VENDOR_NAME`)) AS `VENDOR_NAMES`,
  -- Site (parent warehouse or current warehouse if no parent warehouse) counts/quantities
  `SITE_ONHAND_CNT` AS `SITE_ONHAND_CNT`,
  `SITE_ONHAND_QTY` AS `SITE_ONHAND_QTY`,
  `SITE_PENDING_CNT` AS `SITE_PENDING_CNT`,
  `SITE_PENDING_QTY` AS `SITE_PENDING_QTY`,
  `SITE_ONORDER_CNT` AS `SITE_ONORDER_CNT`,
  `SITE_ONORDER_QTY` AS `SITE_ONORDER_QTY`,
  -- Total counts/quantities across all warehouses
  `AW_ONHAND_CNT` AS `AW_ONHAND_CNT`,
  `AW_ONHAND_QTY` AS `AW_ONHAND_QTY`,
  `AW_PENDING_CNT` AS `AW_PENDING_CNT`,
  `AW_PENDING_QTY` AS `AW_PENDING_QTY`,
  `AW_ONORDER_CNT` AS `AW_ONORDER_CNT`,
  `AW_ONORDER_QTY` AS `AW_ONORDER_QTY`,
  -- Related RECIDs
  `EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
  `SITE_RECID` AS `SITE_RECID`,
  `WHS_RECID` AS `WHS_RECID`,
  GROUP_CONCAT(DISTINCT(`VENDOR_RECID`)) AS `VENDOR_RECIDS`
FROM (
  SELECT
    -- Current location reorder data
    `CRD`.`WHS_PATH` AS `WAREHOUSE`,
    `CRD`.`PART_NUMBER` AS `PART_NUMBER`,
    `CRD`.`DESCRIPTION` AS `DESCRIPTION`,
    `CRD`.`MANUFACTURER` AS `MANUFACTURER`,
    `CRD`.`MANU_PART_NUM` AS `MANU_PART_NUM`,
    COALESCE(`CRD`.`WHS_ONHAND_CNT`, 0) AS `ONHAND_CNT`,
    COALESCE(`CRD`.`WHS_ONHAND_QTY`, 0) AS `ONHAND_QTY`,
    COALESCE(`CRD`.`WHS_PENDING_CNT`, 0) AS `PENDING_CNT`,
    COALESCE(`CRD`.`WHS_PENDING_QTY`, 0) AS `PENDING_QTY`,
    COALESCE(`CRD`.`WHS_ONORDER_CNT`, 0) AS `ONORDER_CNT`,
    COALESCE(`CRD`.`WHS_ONORDER_QTY`, 0) AS `ONORDER_QTY`,
    `CRD`.`COST` AS `COST`,
    `CRD`.`QTY_UNITS` AS `QTY_UNITS`,
    `CRD`.`WHS_TARGET_QTY` AS `TARGET_QTY`,
    `CRD`.`WHS_ORDER_QTY` AS `ORDER_QTY`,
    `CRD`.`WHS_EXCESS_QTY` AS `EXCESS_QTY`,
    `CRD`.`VENDOR_NAME` AS `VENDOR_NAME`,
    -- Site reorder data
    COALESCE(`SITE_ONHAND`.`ONHAND_CNT`, 0) AS `SITE_ONHAND_CNT`,
    COALESCE(`SITE_ONHAND`.`ONHAND_QTY`, 0) AS `SITE_ONHAND_QTY`,
    COALESCE(`SITE_PENDING`.`PENDING_CNT`, 0) AS `SITE_PENDING_CNT`,
    COALESCE(`SITE_PENDING`.`PENDING_QTY`, 0) AS `SITE_PENDING_QTY`,
    COALESCE(`SITE_ONORDER`.`ONORDER_CNT`, 0) AS `SITE_ONORDER_CNT`,
    COALESCE(`SITE_ONORDER`.`ONORDER_QTY`, 0) AS `SITE_ONORDER_QTY`,
    -- All warehouse reorder data
    COALESCE(`AW_ONHAND`.`ONHAND_CNT`, 0) AS `AW_ONHAND_CNT`,
    COALESCE(`AW_ONHAND`.`ONHAND_QTY`, 0) AS `AW_ONHAND_QTY`,
    COALESCE(`AW_PENDING`.`PENDING_CNT`, 0) AS `AW_PENDING_CNT`,
    COALESCE(`AW_PENDING`.`PENDING_QTY`, 0) AS `AW_PENDING_QTY`,
    COALESCE(`AW_ONORDER`.`ONORDER_CNT`, 0) AS `AW_ONORDER_CNT`,
    COALESCE(`AW_ONORDER`.`ONORDER_QTY`, 0) AS `AW_ONORDER_QTY`,
    -- Related RECIDs
    `CRD`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
    `CRD`.`SSLOC_RECID` AS `SITE_RECID`,
    `CRD`.`WHS_RECID` AS `WHS_RECID`,
    `CRD`.`VENDOR_RECID` AS `VENDOR_RECID`,
    `CRD`.`TENANTS_RECID` AS `TENANTS_RECID`
  FROM (
    -- Current warehouse reorder data
    SELECT
      -- Location paths
      `IRD`.`SSLOC_PATH` AS `SSLOC_PATH`,
      `IRD`.`WHS_PATH` AS `WHS_PATH`,
      -- Equipment catalog
      `IRD`.`PART_NUMBER` AS `PART_NUMBER`,
      `IRD`.`DESCRIPTION` AS `DESCRIPTION`,
      `IRD`.`MANUFACTURER` AS `MANUFACTURER`,
      `IRD`.`MANU_PART_NUM` AS `MANU_PART_NUM`,
      -- Current location counts/quantities
      `IRD`.`WHS_ONHAND_CNT` AS `WHS_ONHAND_CNT`,
      `IRD`.`WHS_ONHAND_QTY` AS `WHS_ONHAND_QTY`,
      `IRD`.`WHS_PENDING_CNT` AS `WHS_PENDING_CNT`,
      `IRD`.`WHS_PENDING_QTY` AS `WHS_PENDING_QTY`,
      `IRD`.`WHS_ONORDER_CNT` AS `WHS_ONORDER_CNT`,
      `IRD`.`WHS_ONORDER_QTY` AS `WHS_ONORDER_QTY`,
      -- Vendor data
      `VND`.`COST` AS `COST`,
      `VND`.`VENDOR_RECID` AS `VENDOR_RECID`,
      `VND`.`VENDOR_NAME` AS `VENDOR_NAME`,
      -- Unit of measure
      `ECUL`.`VALUE` AS `QTY_UNITS`,
      -- Equipment Catalog Inventory reorder threshold
      `IRD`.`WHS_TARGET_QTY` AS `WHS_TARGET_QTY`,
      -- Quantity required to meet pending orders and inventory specifications at current warehouse location
      `IRD`.`WHS_ORDER_QTY` AS `WHS_ORDER_QTY`,
      -- Quantity in excess of pending orders and inventory specifications at current warehouse location
      `IRD`.`WHS_EXCESS_QTY` AS `WHS_EXCESS_QTY`,
      -- Related RECIDs
      `IRD`.`WHS_RECID` AS `WHS_RECID`,
      `IRD`.`SSLOC_RECID` AS `SSLOC_RECID`,
      `IRD`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      `IRD`.`TENANTS_RECID` AS `TENANTS_RECID`
    FROM (
      SELECT
        -- Parent warehouse location or the current warehouse if there is no parent warehouse
        `WHS`.`SSLOC_RECID` AS `SSLOC_RECID`,
        `SSLT`.`PATH` AS `SSLOC_PATH`,
        -- Current warehouse
        `WHS`.`RECID` AS `WHS_RECID`,
        `WHS`.`WAREHOUSE_PATH` AS `WHS_PATH`,
        -- Equipment catalog
        `WEC`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
        `EC`.`PART_NUMBER` AS `PART_NUMBER`,
        `EC`.`DESCRIPTION` AS `DESCRIPTION`,
        `EC`.`MANUFACTURER` AS `MANUFACTURER`,
        `EC`.`MANU_PART_NUM` AS `MANU_PART_NUM`,
        `EC`.`UNIT_LISTS_RECID` AS `ECUL_RECID`,
        -- Current warehouse counts/quantities
        `WEC`.`UNASSIGNED_COUNT` AS `WHS_ONHAND_CNT`,
        `WEC`.`UNASSIGNED_QTY` AS `WHS_ONHAND_QTY`,
        `WEC`.`PENDING_COUNT` AS `WHS_PENDING_CNT`,
        `WEC`.`PENDING_QTY` AS `WHS_PENDING_QTY`,
        `WEC`.`ON_ORDER_COUNT` AS `WHS_ONORDER_CNT`,
        `WEC`.`ON_ORDER_QTY` AS `WHS_ONORDER_QTY`,
        -- Equipment Catalog Inventory reorder threshold
        `WEC`.`REORDER_THRESHOLD` AS `WHS_TARGET_QTY`,
        -- Quantity required to meet pending orders and inventory specifications at current warehouse location
        CASE
          WHEN `WEC`.`REC_ORDER_QTY` > 0 THEN `WEC`.`REC_ORDER_QTY`
          ELSE 0
        END AS `WHS_ORDER_QTY`,
        -- Quantity in excess of pending orders and inventory specifications at current warehouse location
        CASE
          WHEN `WEC`.`REC_ORDER_QTY` < 0 THEN ABS(`WEC`.`REC_ORDER_QTY`)
          ELSE 0
        END AS `WHS_EXCESS_QTY`,
        -- Related RECIDs
        `WEC`.`TENANTS_RECID` AS `TENANTS_RECID`
      FROM (
        SELECT
          `ECECBL`.`LOCATIONS_RECID` AS `LOCATIONS_RECID`,
          `ECECBL`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
          -- Number of UNASSIGNED "Boxes"
          COALESCE(MAX(`ECECBL`.`UNASSIGNED_COUNT`), 0) AS `UNASSIGNED_COUNT`,
          -- Number of "Screws" in the UNASSIGNED "Boxes"
          COALESCE(MAX(`ECECBL`.`UNASSIGNED_QTY`), 0) AS `UNASSIGNED_QTY`,
          -- Number of "Boxes" On Order
          COALESCE(MAX(`ECECBL`.`ON_ORDER_COUNT`), 0) AS `ON_ORDER_COUNT`,
          -- Number of "Screws" in the "Boxes" On Order
          COALESCE(MAX(`ECECBL`.`ON_ORDER_QTY`), 0) AS `ON_ORDER_QTY`,
          -- Number of "Boxes" for PENDING equipment adds
          COALESCE(MAX(`ECECBL`.`PENDING_COUNT`), 0) AS `PENDING_COUNT`,
          -- Number of "Screws" in the "Boxes" for PENDING equipment adds
          COALESCE(MAX(`ECECBL`.`PENDING_QTY`), 0) AS `PENDING_QTY`,
          -- Number of "Screws" below which more should be ordered
          COALESCE(MAX(`ECECBL`.`REORDER_THRESHOLD`), 0) AS `REORDER_THRESHOLD`,
          -- Recommended order quantity
          (
                COALESCE(MAX(`ECECBL`.`PENDING_QTY`), 0)
              + COALESCE(MAX(`ECECBL`.`REORDER_THRESHOLD`), 0)
              - COALESCE(MAX(`ECECBL`.`UNASSIGNED_QTY`), 0)
              - COALESCE(MAX(`ECECBL`.`ON_ORDER_QTY`), 0)
          ) AS `REC_ORDER_QTY`,
          -- Related RECIDs
          `ECECBL`.`TENANTS_RECID` AS `TENANTS_RECID`
        FROM (
          -- Warehouse UNASSIGNED
          SELECT
            `EQ`.`LOCATIONS_RECID` AS `LOCATIONS_RECID`,
            `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
            -- Number of UNASSIGNED EQUIPMENT records
            COUNT(*)  AS `UNASSIGNED_COUNT`,
            -- Quantity of UNASSIGNED equipment
            SUM(
              CASE
                WHEN `EC`.`HAS_UNITS` = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
                ELSE 1
              END
            ) AS `UNASSIGNED_QTY`,
            -- Placeholders for coalesced data
            NULL AS `ON_ORDER_COUNT`,
            NULL AS `ON_ORDER_QTY`,
            NULL AS `REORDER_THRESHOLD`,
            NULL AS `PENDING_COUNT`,
            NULL AS `PENDING_QTY`,
            -- Related RECIDs
            `EQ`.`TENANTS_RECID` AS `TENANTS_RECID`
          FROM `EQUIPMENT` `EQ`
          JOIN `LOCATIONS` `LOC`
            ON `LOC`.`RECID` = `EQ`.`LOCATIONS_RECID`
              AND `LOC`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
              AND `LOC`.`WAREHOUSE_FLAG` = 1
          JOIN `EQP_CATALOG` `EC`
            ON `EC`.`RECID` = `EQ`.`EQP_CATALOG_RECID`
              AND `EC`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
          JOIN `LISTS` `ESL`
            ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
              AND `ESL`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
          WHERE
            `ESL`.`CODE` = 'UNASSIGNED'
            AND `EQ`.`LOCATIONS_RECID` IS NOT NULL
            AND `EQ`.`CONSUMED` = 0
          GROUP BY
            `EQ`.`LOCATIONS_RECID`,
            `EQ`.`EQP_CATALOG_RECID`,
            `EQ`.`TENANTS_RECID`

          UNION

          -- Warehouse ON_ORDER
          SELECT
            `EQ`.`LOCATIONS_RECID` AS `LOCATIONS_RECID`,
            `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
            -- Placeholders for coalesced data
            NULL AS `UNASSIGNED_COUNT`,
            NULL AS `UNASSIGNED_QTY`,
            -- Number of ON_ORDER EQUIPMENT records
            COUNT(*) AS `ON_ORDER_COUNT`,
            -- Quantity of ON_ORDER equipment
            SUM(
                CASE
                   WHEN `EC`.`HAS_UNITS` = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
                   ELSE 1
                END
            ) AS `ON_ORDER_QTY`,
            -- Placeholders for coalesced data
            NULL AS `REORDER_THRESHOLD`,
            NULL AS `PENDING_COUNT`,
            NULL AS `PENDING_QTY`,
            -- Related RECIDs
            `EQ`.`TENANTS_RECID` AS `TENANTS_RECID`
          FROM `EQUIPMENT` `EQ`
          JOIN `LOCATIONS` `LOC`
            ON `LOC`.`RECID` = `EQ`.`LOCATIONS_RECID`
              AND `LOC`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
              AND `LOC`.`WAREHOUSE_FLAG` = 1
          JOIN `EQP_CATALOG` `EC`
            ON `EC`.`RECID` = `EQ`.`EQP_CATALOG_RECID`
              AND `EC`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
          JOIN `LISTS` `ESL`
            ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
              AND `ESL`.`TENANTS_RECID` = `EQ`.`TENANTS_RECID`
          JOIN (
            -- Active purchase orders
            SELECT
              `POE`.`EQUIPMENT_RECID` AS `EQUIPMENT_RECID`,
              `POSL`.`CODE` AS `PO_STATUS_CODE`
            FROM `PURCHASE_ORDER_EQUIPMENT` `POE`
            INNER JOIN `PURCHASE_ORDER` `PO`
              ON `POE`.`PURCHASE_ORDER_RECID` = `PO`.`RECID`
            INNER JOIN `LISTS` `POSL`
              ON `PO`.`PO_STATUS_LISTS_RECID` = `POSL`.`RECID`
          ) `POE` ON `EQ`.`RECID` = `POE`.`EQUIPMENT_RECID`
          WHERE
            -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
            `ESL`.`CODE` = 'ON_ORDER'
            AND `POE`.`EQUIPMENT_RECID` IS NOT NULL
            AND `POE`.`PO_STATUS_CODE` NOT IN ('PENDING', 'CLOSED')
            AND `EQ`.`LOCATIONS_RECID` IS NOT NULL
            AND `EQ`.`CONSUMED` = 0
          GROUP BY
            `EQ`.`LOCATIONS_RECID`,
            `EQ`.`EQP_CATALOG_RECID`,
            `EQ`.`TENANTS_RECID`

          UNION

          -- Warehouse PENDING
          SELECT
            `SDEL`.`RECID` AS `LOCATIONS_RECID`,
            `SDE`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
            -- Placeholders for coalesced data
            NULL AS `UNASSIGNED_COUNT`,
            NULL AS `UNASSIGNED_QTY`,
            NULL AS `ON_ORDER_COUNT`,
            NULL AS `ON_ORDER_QTY`,
            NULL AS `REORDER_THRESHOLD`,
            -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
            SUM(
              CASE
                WHEN `EC`.`HAS_UNITS` = 1 AND COALESCE(`EC`.`MAX_QUANTITY`, 0) > 0 THEN
                    CEIL(
                        `SDE`.`QUANTITY` / COALESCE(`EC`.`MAX_QUANTITY`, 1)
                    )
                ELSE 1
              END
            ) AS `PENDING_COUNT`,
            -- Quantity of PENDING Service Desk Equipment ADDs
            SUM(COALESCE(`SDE`.`QUANTITY`, 0)) AS `PENDING_QTY`,
            `SDE`.`TENANTS_RECID` AS `TENANTS_RECID`
          FROM `SERVICE_DESK_EQP` `SDE`
          JOIN `EQP_CATALOG` `EC`
            ON `EC`.`RECID` = `SDE`.`EQP_CATALOG_RECID`
              AND `EC`.`TENANTS_RECID` = `SDE`.`TENANTS_RECID`
          JOIN `LOCATIONS` `SDEL`
            ON `SDE`.`FROM_LOCATIONS_RECID` = `SDEL`.`RECID`
              AND `SDE`.`TENANTS_RECID` = `SDEL`.`TENANTS_RECID`
          JOIN `LISTS` `SDESL`
            ON `SDESL`.`RECID` = `SDE`.`SD_EQP_STATUS_LISTS_RECID`
              AND `SDESL`.`TENANTS_RECID` = `SDE`.`TENANTS_RECID`
          JOIN `LISTS` `SDEAL`
            ON `SDE`.`SD_EQP_ACTIONS_LISTS_RECID` = `SDEAL`.`RECID`
              AND `SDE`.`TENANTS_RECID` = `SDEAL`.`TENANTS_RECID`
          WHERE
            `SDEAL`.`CODE` = 'ADD'
            AND `SDESL`.`CODE` = 'PENDING'
          GROUP BY
            `SDEL`.`RECID`,
            `SDE`.`EQP_CATALOG_RECID`,
            `SDE`.`TENANTS_RECID`

          UNION

          -- Warehouse Reorder Threshold
          SELECT
            `ECIL`.`RECID` AS `LOCATIONS_RECID`,
            `ECI`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
            -- Placeholders for coalesced data
            NULL AS `UNASSIGNED_COUNT`,
            NULL AS `UNASSIGNED_QTY`,
            NULL AS `ON_ORDER_COUNT`,
            NULL AS `ON_ORDER_QTY`,
            -- Equipment catalog inventory reorder threshold
            `ECI`.`REORDER` AS `REORDER_THRESHOLD`,
            -- Placeholders for coalesced data
            NULL AS `PENDING_COUNT`,
            NULL AS `PENDING_QTY`,
            -- Related RECIDs
            `ECI`.`TENANTS_RECID` AS `TENANTS_RECID`
          FROM `EQP_CATALOG_INVENTORY` `ECI`
          JOIN `LOCATIONS` `ECIL`
            ON `ECIL`.`RECID` = `ECI`.`LOCATIONS_RECID`
              AND `ECIL`.`TENANTS_RECID` = `ECI`.`TENANTS_RECID`
        ) `ECECBL` -- Equipment Catalog Equipment Counts by Location
        GROUP BY
          `ECECBL`.`LOCATIONS_RECID`,
          `ECECBL`.`EQP_CATALOG_RECID`,
          `ECECBL`.`TENANTS_RECID`
      ) `WEC` -- Warehouse equipment counts
      JOIN (
        -- Warehouse data
        SELECT
          `WL`.`RECID` AS `RECID`,
          `WL`.`NAME` AS `WAREHOUSE`,
          `WLT`.`PATH` AS `WAREHOUSE_PATH`,
          COALESCE(`SSLOC`.`RECID`, `PWL`.`RECID`, `WL`.`RECID`) AS `SSLOC_RECID`,
          `WL`.`TENANTS_RECID` AS `TENANTS_RECID`
        FROM `LOCATIONS` `WL`
        LEFT JOIN `LOCATIONS_TEXTPATH` `WLT`
          ON `WL`.`RECID` = `WLT`.`RECID`
            AND `WL`.`TENANTS_RECID` = `WLT`.`TENANTS_RECID`
        LEFT JOIN `LOCATIONS` `PWL`
          ON `PWL`.`RECID` = `WL`.`PARENT_LOCATIONS_RECID`
            AND `PWL`.`TENANTS_RECID` = `WL`.`TENANTS_RECID`
            AND `PWL`.`WAREHOUSE_FLAG` = 1
        LEFT JOIN `LOCATIONS` `SSLOC`
          ON `SSLOC`.`RECID` = COALESCE(`PWL`.`RECID`, `WL`.`RECID`)
        WHERE
          `WL`.`WAREHOUSE_FLAG` = 1
      ) `WHS`
        ON `WHS`.`RECID` = `WEC`.`LOCATIONS_RECID`
          AND `WHS`.`TENANTS_RECID` = `WEC`.`TENANTS_RECID`
      LEFT JOIN `LOCATIONS_TEXTPATH` `SSLT`
        ON `WHS`.`SSLOC_RECID` = `SSLT`.`RECID`
          AND `WHS`.`TENANTS_RECID` = `SSLT`.`TENANTS_RECID`

      JOIN `EQP_CATALOG` `EC`
        ON `WEC`.`EQP_CATALOG_RECID` = `EC`.`RECID`
          AND `WEC`.`TENANTS_RECID` = `EC`.`TENANTS_RECID`
          AND `EC`.`STATUS` = 1
    ) `IRD` -- Inventory reorder data
    LEFT JOIN (
      SELECT DISTINCT
        CAST(`ECV`.`COST` AS DECIMAL(11,2)) AS `COST`,
        `ECV`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
        `ECV`.`RECID` AS `VENDOR_RECID`,
        `VENDORS`.`NAME` AS `VENDOR_NAME`,
        `ECV`.`TENANTS_RECID` AS `TENANTS_RECID`
      FROM `EQP_CATALOG_VENDORS` `ECV`
      JOIN `VENDORS`
        ON `VENDORS`.`RECID` = `ECV`.`VENDORS_RECID`
          AND `VENDORS`.`TENANTS_RECID` = `ECV`.`TENANTS_RECID`
    ) `VND`
      ON `VND`.`EQP_CATALOG_RECID` = `IRD`.`EQP_CATALOG_RECID`
        AND `VND`.`TENANTS_RECID` = `IRD`.`TENANTS_RECID`
    LEFT JOIN `LISTS` `ECUL`
      ON `ECUL`.`RECID` = `IRD`.`ECUL_RECID`
        AND `ECUL`.`TENANTS_RECID` = `IRD`.`TENANTS_RECID`
  ) `CRD` -- Collected reorder data

  LEFT JOIN (
    -- ALL WAREHOUSES ON HAND
    SELECT
      `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      -- Number of UNASSIGNED EQUIPMENT records
      COUNT(*) AS `ONHAND_CNT`,
      -- Quantity of UNASSIGNED equipment
      SUM(
        CASE
          WHEN `EC`.`HAS_UNITS` = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
          ELSE 1
        END
      ) AS `ONHAND_QTY`
    FROM `EQUIPMENT` `EQ`
    INNER JOIN `EQP_CATALOG` `EC`
      ON `EQ`.`EQP_CATALOG_RECID` = `EC`.`RECID`

    INNER JOIN `LOCATIONS` `LOC`
      ON `LOC`.`RECID` = `EQ`.`LOCATIONS_RECID`
        AND `LOC`.`WAREHOUSE_FLAG` = 1

    INNER JOIN `LISTS` `ESL`
      ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
    WHERE
      `ESL`.`CODE` = 'UNASSIGNED'
      AND `EQ`.`CONSUMED` = 0
    GROUP BY
        `EQ`.`EQP_CATALOG_RECID`
      , `EC`.`HAS_UNITS`
  ) `AW_ONHAND`
    ON `CRD`.`EQP_CATALOG_RECID` = `AW_ONHAND`.`EQP_CATALOG_RECID`

  LEFT JOIN (
    -- SITE ON HAND
    SELECT
      `SSLOC`.`RECID` AS `SITE_RECID`,
      `ONHAND_DATA`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      -- Number of UNASSIGNED EQUIPMENT records
      SUM(`ONHAND_DATA`.`ONHAND_CNT`) AS `ONHAND_CNT`,
      -- Quantity of UNASSIGNED equipment
      SUM(`ONHAND_DATA`.`ONHAND_QTY`) AS `ONHAND_QTY`
    FROM (
      SELECT
        `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
        `EQ`.`LOCATIONS_RECID` AS `LOCATIONS_RECID`,
        `WL`.`PARENT_LOCATIONS_RECID` AS `PARENT_LOCATIONS_RECID`,
        -- Number of UNASSIGNED EQUIPMENT records
        COUNT(*) AS `ONHAND_CNT`,
        -- Quantity of UNASSIGNED equipment
        SUM(
          CASE
            WHEN EC.HAS_UNITS = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
            ELSE 1
          END
        ) AS `ONHAND_QTY`
      FROM `EQUIPMENT` `EQ`
      INNER JOIN `EQP_CATALOG` `EC`
        ON `EQ`.`EQP_CATALOG_RECID` = `EC`.`RECID`

      INNER JOIN `LOCATIONS` `WL`
        ON `WL`.`RECID` = `EQ`.`LOCATIONS_RECID`
          AND `WL`.`WAREHOUSE_FLAG` = 1

      INNER JOIN `LISTS` `ESL`
        ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
      WHERE
        `ESL`.`CODE` = 'UNASSIGNED'
        AND `EQ`.`CONSUMED` = 0
      GROUP BY
        `EQ`.`EQP_CATALOG_RECID`,
        `EQ`.`LOCATIONS_RECID`,
        `EC`.`HAS_UNITS`
    ) `ONHAND_DATA`
    LEFT JOIN `LOCATIONS` `PWL`
      ON `PWL`.`RECID` = `ONHAND_DATA`.`PARENT_LOCATIONS_RECID`
        AND `PWL`.`WAREHOUSE_FLAG` = 1
    JOIN `LOCATIONS` `SSLOC`
      ON `SSLOC`.`RECID` = COALESCE(`PWL`.`RECID`, `ONHAND_DATA`.`LOCATIONS_RECID`)
    GROUP BY
      `SSLOC`.`RECID`,
      `ONHAND_DATA`.`EQP_CATALOG_RECID`
  ) `SITE_ONHAND`
    ON `CRD`.`EQP_CATALOG_RECID` = `SITE_ONHAND`.`EQP_CATALOG_RECID`
      AND `CRD`.`SSLOC_RECID` = `SITE_ONHAND`.`SITE_RECID`

  LEFT JOIN (
    -- ALL WAREHOUSES ON ORDER
    SELECT
      `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      -- Number of ON_ORDER EQUIPMENT records with an associate PO that is not PENDING or CLOSED
      COUNT(*) AS `ONORDER_CNT`,
      -- Quantity of ON_ORDER equipment with an associate PO that is not PENDING or CLOSED
      SUM(
        CASE
          WHEN `EC`.`HAS_UNITS` = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
          ELSE 1
        END
      ) AS `ONORDER_QTY`
    FROM `EQUIPMENT` `EQ`
    INNER JOIN `EQP_CATALOG` `EC`
      ON `EQ`.`EQP_CATALOG_RECID` = `EC`.`RECID`
    INNER JOIN (
      `PURCHASE_ORDER_EQUIPMENT` `POE`
      INNER JOIN `PURCHASE_ORDER` `PO`
        ON `POE`.`PURCHASE_ORDER_RECID` = `PO`.`RECID`
      INNER JOIN `LISTS` `POSL`
        ON `PO`.`PO_STATUS_LISTS_RECID` = `POSL`.`RECID`
    ) ON `EQ`.`RECID` = `POE`.`EQUIPMENT_RECID`
    INNER JOIN `LOCATIONS` `WL`
      ON `EQ`.`LOCATIONS_RECID` = `WL`.`RECID`
        AND `WL`.`WAREHOUSE_FLAG` = 1
    LEFT JOIN `LISTS` `ESL`
      ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
    WHERE
      -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
      `ESL`.`CODE` = 'ON_ORDER'
      AND `POSL`.`CODE` NOT IN ('PENDING', 'CLOSED')
    GROUP BY
      `EQ`.`EQP_CATALOG_RECID`,
      `EC`.`HAS_UNITS`
  ) `AW_ONORDER`
    ON `CRD`.`EQP_CATALOG_RECID` = `AW_ONORDER`.`EQP_CATALOG_RECID`

  LEFT JOIN (
    -- SITE ON ORDER
    SELECT
      `SSLOC`.`RECID` AS `SITE_RECID`,
      `ONORDER_DATA`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      -- Number of ON_ORDER EQUIPMENT records with an associate PO that is not PENDING or CLOSED
      SUM(`ONORDER_DATA`.`ONORDER_CNT`) AS `ONORDER_CNT`,
      -- Quantity of ON_ORDER equipment with an associate PO that is not PENDING or CLOSED
      SUM(`ONORDER_DATA`.`ONORDER_QTY`) AS `ONORDER_QTY`
    FROM (
      SELECT
        `EQ`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
        `EQ`.`LOCATIONS_RECID` AS `LOCATIONS_RECID`,
        `WL`.`PARENT_LOCATIONS_RECID` AS `PARENT_LOCATIONS_RECID`,
        COUNT(*) AS `ONORDER_CNT`,
        SUM(
          CASE
            WHEN `EC`.`HAS_UNITS` = 1 THEN COALESCE(`EQ`.`UNITS`, 0)
            ELSE 1
          END
        ) AS `ONORDER_QTY`
      FROM `EQUIPMENT` `EQ`
      INNER JOIN `EQP_CATALOG` `EC`
        ON `EQ`.`EQP_CATALOG_RECID` = `EC`.`RECID`
      INNER JOIN (
        `PURCHASE_ORDER_EQUIPMENT` `POE`
        INNER JOIN `PURCHASE_ORDER` `PO`
          ON `POE`.`PURCHASE_ORDER_RECID` = `PO`.`RECID`
        INNER JOIN `LISTS` `POSL`
          ON `PO`.`PO_STATUS_LISTS_RECID` = `POSL`.`RECID`
      ) ON `EQ`.`RECID` = `POE`.`EQUIPMENT_RECID`
      INNER JOIN `LOCATIONS` `WL`
        ON `EQ`.`LOCATIONS_RECID` = `WL`.`RECID`
          AND `WL`.`WAREHOUSE_FLAG` = 1
      LEFT JOIN `LISTS` `ESL`
        ON `ESL`.`RECID` = `EQ`.`EQP_STATUS_LISTS_RECID`
      WHERE
        -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
        `ESL`.`CODE` = 'ON_ORDER'
        AND `POSL`.`CODE` NOT IN ('PENDING', 'CLOSED')
      GROUP BY
        `EQ`.`EQP_CATALOG_RECID`,
        `EQ`.`LOCATIONS_RECID`,
        `EC`.`HAS_UNITS`
    ) `ONORDER_DATA`
    LEFT JOIN `LOCATIONS` `PWL`
      ON `PWL`.`RECID` = `ONORDER_DATA`.`PARENT_LOCATIONS_RECID`
        AND `PWL`.`WAREHOUSE_FLAG` = 1
    JOIN `LOCATIONS` `SSLOC`
      ON `SSLOC`.`RECID` = COALESCE(`PWL`.`RECID`, `ONORDER_DATA`.`LOCATIONS_RECID`)
    GROUP BY
      `SSLOC`.`RECID`,
      `ONORDER_DATA`.`EQP_CATALOG_RECID`
  ) `SITE_ONORDER`
    ON `CRD`.`EQP_CATALOG_RECID` = `SITE_ONORDER`.`EQP_CATALOG_RECID`
      AND `CRD`.`SSLOC_RECID` = `SITE_ONORDER`.`SITE_RECID`

  LEFT JOIN (
    -- ALL WAREHOUSES PENDING
    SELECT
      -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
      SUM(
        CASE
          WHEN (`EC`.`HAS_UNITS` = 1 AND COALESCE(`EC`.`MAX_QUANTITY`, 0) > 0) THEN
            CEIL(`SDE`.`QUANTITY` / COALESCE(`EC`.`MAX_QUANTITY`, 1))
          ELSE 1
        END
      ) AS `PENDING_CNT`,
      -- Quantity of PENDING Service Desk Equipment adds
      SUM(COALESCE(`SDE`.`QUANTITY`, 0)) AS `PENDING_QTY`,
      `SDE`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`
    FROM `SERVICE_DESK_EQP` `SDE`
    LEFT JOIN `EQP_CATALOG` `EC`
      ON `EC`.`RECID` = `SDE`.`EQP_CATALOG_RECID`
    INNER JOIN `LOCATIONS` `WL`
      ON `SDE`.`FROM_LOCATIONS_RECID` = `WL`.`RECID`
        AND `WL`.`WAREHOUSE_FLAG` = 1
    LEFT JOIN `LISTS` `SDESL`
      ON `SDESL`.`RECID` = `SDE`.`SD_EQP_STATUS_LISTS_RECID`
    LEFT JOIN `LISTS` `SDEAL`
      ON `SDEAL`.`RECID` = `SDE`.`SD_EQP_ACTIONS_LISTS_RECID`
    WHERE
      `SDEAL`.`CODE` = 'ADD'
      AND `SDESL`.`CODE` = 'PENDING'
    GROUP BY `SDE`.`EQP_CATALOG_RECID`
  ) `AW_PENDING`
    ON `CRD`.`EQP_CATALOG_RECID` = `AW_PENDING`.`EQP_CATALOG_RECID`

  LEFT JOIN (
    -- SITE PENDING
    SELECT
      `SSLOC`.`RECID` AS `SITE_RECID`,
      `PENDING_DATA`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`,
      -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
      SUM(`PENDING_DATA`.`PENDING_CNT`) AS `PENDING_CNT`,
      -- Quantity of PENDING Service Desk Equipment adds
      SUM(`PENDING_DATA`.`PENDING_QTY`) AS `PENDING_QTY`
    FROM (
      SELECT
        -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
        SUM(
          CASE
            WHEN `EC`.`HAS_UNITS` = 1 AND COALESCE(`EC`.`MAX_QUANTITY`, 0) > 0 THEN
                CEIL(`SDE`.`QUANTITY` / COALESCE(`EC`.`MAX_QUANTITY`, 1))
            ELSE 1
          END
        ) AS `PENDING_CNT`,
        -- Quantity of PENDING Service Desk Equipment adds
        SUM(COALESCE(`SDE`.`QUANTITY`, 0)) AS `PENDING_QTY`,
        `WL`.`RECID` AS `LOCATIONS_RECID`,
        `WL`.`PARENT_LOCATIONS_RECID` AS `PARENT_LOCATIONS_RECID`,
        `SDE`.`EQP_CATALOG_RECID` AS `EQP_CATALOG_RECID`
      FROM `SERVICE_DESK_EQP` `SDE`
      LEFT JOIN `EQP_CATALOG` `EC`
        ON `EC`.`RECID` = `SDE`.`EQP_CATALOG_RECID`
      INNER JOIN `LOCATIONS` `WL`
        ON `SDE`.`FROM_LOCATIONS_RECID` = `WL`.`RECID`
          AND `WL`.`WAREHOUSE_FLAG` = 1
      LEFT JOIN `LISTS` `SDESL`
        ON `SDESL`.`RECID` = `SDE`.`SD_EQP_STATUS_LISTS_RECID`
      LEFT JOIN `LISTS` `SDEAL`
        ON `SDEAL`.`RECID` = `SDE`.`SD_EQP_ACTIONS_LISTS_RECID`
      WHERE
        `SDEAL`.`CODE` = 'ADD'
        AND `SDESL`.`CODE` = 'PENDING'
      GROUP BY
        `SDE`.`EQP_CATALOG_RECID`,
        `WL`.`RECID`,
        `WL`.`PARENT_LOCATIONS_RECID`
    ) `PENDING_DATA`
    LEFT JOIN `LOCATIONS` `PWL`
      ON `PWL`.`RECID` = `PENDING_DATA`.`PARENT_LOCATIONS_RECID`
        AND `PWL`.`WAREHOUSE_FLAG` = 1
    JOIN `LOCATIONS` `SSLOC`
        ON `SSLOC`.`RECID` = COALESCE(`PWL`.`RECID`, `PENDING_DATA`.`LOCATIONS_RECID`)
    GROUP BY
        `SSLOC`.`RECID`
      , `PENDING_DATA`.`EQP_CATALOG_RECID`
  ) `SITE_PENDING`
    ON `CRD`.`EQP_CATALOG_RECID` = `SITE_PENDING`.`EQP_CATALOG_RECID`
      AND `CRD`.`SSLOC_RECID` = `SITE_ONORDER`.`SITE_RECID`
  GROUP BY
    `CRD`.`EQP_CATALOG_RECID`,
    `CRD`.`SSLOC_RECID`,
    `CRD`.`WHS_RECID`,
    `CRD`.`VENDOR_RECID`,
    `CRD`.`WHS_PATH`,
    `CRD`.`PART_NUMBER`,
    `CRD`.`DESCRIPTION`,
    `CRD`.`MANUFACTURER`,
    `CRD`.`MANU_PART_NUM`,
    `CRD`.`WHS_ONHAND_CNT`,
    `CRD`.`WHS_ONHAND_QTY`,
    `CRD`.`WHS_PENDING_CNT`,
    `CRD`.`WHS_PENDING_QTY`,
    `CRD`.`WHS_ONORDER_CNT`,
    `CRD`.`WHS_ONORDER_QTY`,
    `CRD`.`COST`,
    `CRD`.`QTY_UNITS`,
    `CRD`.`WHS_TARGET_QTY`,
    `CRD`.`WHS_ORDER_QTY`,
    `CRD`.`WHS_EXCESS_QTY`,
    `SITE_ONHAND`.`ONHAND_CNT`,
    `SITE_ONHAND`.`ONHAND_QTY`,
    `SITE_PENDING`.`PENDING_CNT`,
    `SITE_PENDING`.`PENDING_QTY`,
    `SITE_ONORDER`.`ONORDER_CNT`,
    `SITE_ONORDER`.`ONORDER_QTY`,
    `AW_ONHAND`.`ONHAND_CNT`,
    `AW_ONHAND`.`ONHAND_QTY`,
    `AW_PENDING`.`PENDING_CNT`,
    `AW_PENDING`.`PENDING_QTY`,
    `AW_ONORDER`.`ONORDER_CNT`,
    `AW_ONORDER`.`ONORDER_QTY`,
    `CRD`.`VENDOR_NAME`,
    `CRD`.`TENANTS_RECID`
) `INVENTORY_REORDER_DETAILS`
GROUP BY
  `WAREHOUSE`,
  `PART_NUMBER`,
  `DESCRIPTION`,
  `MANUFACTURER`,
  `MANU_PART_NUM`,
  `ONHAND_CNT`,
  `ONHAND_QTY`,
  `PENDING_CNT`,
  `PENDING_QTY`,
  `ONORDER_CNT`,
  `ONORDER_QTY`,
  `QTY_UNITS`,
  `TARGET_QTY`,
  `ORDER_QTY`,
  `EXCESS_QTY`,
  `SITE_ONHAND_CNT`,
  `SITE_ONHAND_QTY`,
  `SITE_PENDING_CNT`,
  `SITE_PENDING_QTY`,
  `SITE_ONORDER_CNT`,
  `SITE_ONORDER_QTY`,
  `AW_ONHAND_CNT`,
  `AW_ONHAND_QTY`,
  `AW_PENDING_CNT`,
  `AW_PENDING_QTY`,
  `AW_ONORDER_CNT`,
  `AW_ONORDER_QTY`,
  `EQP_CATALOG_RECID`,
  `SITE_RECID`,
  `WHS_RECID`,
  `TENANTS_RECID`

-- Inventory Reorder By Location with Site and All Warehouse totals for Oracle
SELECT
  "EQP_CATALOG_RECID" || '_' || "SITE_RECID" || '_' || "WHS_RECID" AS "RECIDS",
  -- Warehouse path
  "WAREHOUSE" AS "WAREHOUSE",
  -- Equipment Catalog info
  "PART_NUMBER" AS "PART_NUMBER",
  "DESCRIPTION" AS "DESCRIPTION",
  "MANUFACTURER" AS "MANUFACTURER",
  "MANU_PART_NUM" AS "MANU_PART_NUM",
  -- Current warehouse counts/quantities
  "ONHAND_CNT" AS "ONHAND_CNT",
  "ONHAND_QTY" AS "ONHAND_QTY",
  "PENDING_CNT" AS "PENDING_CNT",
  "PENDING_QTY" AS "PENDING_QTY",
  "ONORDER_CNT" AS "ONORDER_CNT",
  "ONORDER_QTY" AS "ONORDER_QTY",
  -- Available prices
  MAX("COST_OPTIONS") AS "COST_OPTIONS",
  -- Unit of measure
  "QTY_UNITS" AS "QTY_UNITS",
  -- Reorder threshold
  "TARGET_QTY" AS "TARGET_QTY",
  -- Quantity required to meet pending orders and inventory specifications at current warehouse location
  "ORDER_QTY" AS "ORDER_QTY",
  -- Quantity in excess of pending orders and inventory specifications at current warehouse location
  "EXCESS_QTY" AS "EXCESS_QTY",
  -- Available vendors
  MAX("VENDOR_NAMES") AS "VENDOR_NAMES",
  -- Site (parent warehouse or current warehouse if no parent warehouse) counts/quantities
  "SITE_ONHAND_CNT" AS "SITE_ONHAND_CNT",
  "SITE_ONHAND_QTY" AS "SITE_ONHAND_QTY",
  "SITE_PENDING_CNT" AS "SITE_PENDING_CNT",
  "SITE_PENDING_QTY" AS "SITE_PENDING_QTY",
  "SITE_ONORDER_CNT" AS "SITE_ONORDER_CNT",
  "SITE_ONORDER_QTY" AS "SITE_ONORDER_QTY",
  -- Total counts/quantities across all warehouses
  "AW_ONHAND_CNT" AS "AW_ONHAND_CNT",
  "AW_ONHAND_QTY" AS "AW_ONHAND_QTY",
  "AW_PENDING_CNT" AS "AW_PENDING_CNT",
  "AW_PENDING_QTY" AS "AW_PENDING_QTY",
  "AW_ONORDER_CNT" AS "AW_ONORDER_CNT",
  "AW_ONORDER_QTY" AS "AW_ONORDER_QTY",
  -- Related RECIDs
  "EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
  "SITE_RECID" AS "SITE_RECID",
  "WHS_RECID" AS "WHS_RECID",
  MAX("VENDOR_RECIDS") AS "VENDOR_RECIDS"
FROM (
  SELECT
    -- Current location reorder data
    "CRD"."EQP_CATALOG_RECID" || '_' || "CRD"."SSLOC_RECID" || '_' || "CRD"."WHS_RECID" AS "RECIDS",
    "CRD"."WHS_PATH" AS "WAREHOUSE",
    "CRD"."PART_NUMBER" AS "PART_NUMBER",
    "CRD"."DESCRIPTION" AS "DESCRIPTION",
    "CRD"."MANUFACTURER" AS "MANUFACTURER",
    "CRD"."MANU_PART_NUM" AS "MANU_PART_NUM",
    COALESCE("CRD"."WHS_ONHAND_CNT", 0) AS "ONHAND_CNT",
    COALESCE("CRD"."WHS_ONHAND_QTY", 0) AS "ONHAND_QTY",
    COALESCE("CRD"."WHS_PENDING_CNT", 0) AS "PENDING_CNT",
    COALESCE("CRD"."WHS_PENDING_QTY", 0) AS "PENDING_QTY",
    COALESCE("CRD"."WHS_ONORDER_CNT", 0) AS "ONORDER_CNT",
    COALESCE("CRD"."WHS_ONORDER_QTY", 0) AS "ONORDER_QTY",
    MAX("VENDOR_DATA"."COST_OPTIONS") AS "COST_OPTIONS",
    "CRD"."QTY_UNITS" AS "QTY_UNITS",
    "CRD"."WHS_TARGET_QTY" AS "TARGET_QTY",
    "CRD"."WHS_ORDER_QTY" AS "ORDER_QTY",
    "CRD"."WHS_EXCESS_QTY" AS "EXCESS_QTY",
    MAX("VENDOR_DATA"."VENDOR_NAMES") AS "VENDOR_NAMES",
    -- Site reorder data
    COALESCE("SITE_ONHAND"."ONHAND_CNT", 0) AS "SITE_ONHAND_CNT",
    COALESCE("SITE_ONHAND"."ONHAND_QTY", 0) AS "SITE_ONHAND_QTY",
    COALESCE("SITE_PENDING"."PENDING_CNT", 0) AS "SITE_PENDING_CNT",
    COALESCE("SITE_PENDING"."PENDING_QTY", 0) AS "SITE_PENDING_QTY",
    COALESCE("SITE_ONORDER"."ONORDER_CNT", 0) AS "SITE_ONORDER_CNT",
    COALESCE("SITE_ONORDER"."ONORDER_QTY", 0) AS "SITE_ONORDER_QTY",
    -- All warehouse reorder data
    COALESCE("AW_ONHAND"."ONHAND_CNT", 0) AS "AW_ONHAND_CNT",
    COALESCE("AW_ONHAND"."ONHAND_QTY", 0) AS "AW_ONHAND_QTY",
    COALESCE("AW_PENDING"."PENDING_CNT", 0) AS "AW_PENDING_CNT",
    COALESCE("AW_PENDING"."PENDING_QTY", 0) AS "AW_PENDING_QTY",
    COALESCE("AW_ONORDER"."ONORDER_CNT", 0) AS "AW_ONORDER_CNT",
    COALESCE("AW_ONORDER"."ONORDER_QTY", 0) AS "AW_ONORDER_QTY",
    -- Related RECIDs
    "CRD"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
    "CRD"."SSLOC_RECID" AS "SITE_RECID",
    "CRD"."WHS_RECID" AS "WHS_RECID",
    MAX("VENDOR_DATA"."VENDOR_RECIDS") AS "VENDOR_RECIDS",
    "CRD"."TENANTS_RECID" AS "TENANTS_RECID"
  FROM (
    -- Current warehouse reorder data
    SELECT
      -- Location paths
      "IRD"."SSLOC_PATH" AS "SSLOC_PATH",
      "IRD"."WHS_PATH" AS "WHS_PATH",
      -- Equipment catalog
      "IRD"."PART_NUMBER" AS "PART_NUMBER",
      "IRD"."DESCRIPTION" AS "DESCRIPTION",
      "IRD"."MANUFACTURER" AS "MANUFACTURER",
      "IRD"."MANU_PART_NUM" AS "MANU_PART_NUM",
      -- Current location counts/quantities
      "IRD"."WHS_ONHAND_CNT" AS "WHS_ONHAND_CNT",
      "IRD"."WHS_ONHAND_QTY" AS "WHS_ONHAND_QTY",
      "IRD"."WHS_PENDING_CNT" AS "WHS_PENDING_CNT",
      "IRD"."WHS_PENDING_QTY" AS "WHS_PENDING_QTY",
      "IRD"."WHS_ONORDER_CNT" AS "WHS_ONORDER_CNT",
      "IRD"."WHS_ONORDER_QTY" AS "WHS_ONORDER_QTY",
      -- Unit of measure
      "ECUL"."VALUE" AS "QTY_UNITS",
      -- Equipment Catalog Inventory reorder threshold
      "IRD"."WHS_TARGET_QTY" AS "WHS_TARGET_QTY",
      -- Quantity required to meet pending orders and inventory specifications at current warehouse location
      "IRD"."WHS_ORDER_QTY" AS "WHS_ORDER_QTY",
      -- Quantity in excess of pending orders and inventory specifications at current warehouse location
      "IRD"."WHS_EXCESS_QTY" AS "WHS_EXCESS_QTY",
      -- Related RECIDs
      "IRD"."WHS_RECID" AS "WHS_RECID",
      "IRD"."SSLOC_RECID" AS "SSLOC_RECID",
      "IRD"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      "IRD"."TENANTS_RECID" AS "TENANTS_RECID"
    FROM (
      SELECT
        -- Parent warehouse location or the current warehouse if there is no parent warehouse
        "WHS"."SSLOC_RECID" AS "SSLOC_RECID",
        "SSLT"."PATH" AS "SSLOC_PATH",
        -- Current warehouse
        "WHS"."RECID" AS "WHS_RECID",
        "WHS"."WAREHOUSE_PATH" AS "WHS_PATH",
        -- Equipment catalog
        "WEC"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
        "EC"."PART_NUMBER" AS "PART_NUMBER",
        "EC"."DESCRIPTION" AS "DESCRIPTION",
        "EC"."MANUFACTURER" AS "MANUFACTURER",
        "EC"."MANU_PART_NUM" AS "MANU_PART_NUM",
        "EC"."UNIT_LISTS_RECID" AS "ECUL_RECID",
        -- Current warehouse counts/quantities
        "WEC"."UNASSIGNED_COUNT" AS "WHS_ONHAND_CNT",
        "WEC"."UNASSIGNED_QTY" AS "WHS_ONHAND_QTY",
        "WEC"."PENDING_COUNT" AS "WHS_PENDING_CNT",
        "WEC"."PENDING_QTY" AS "WHS_PENDING_QTY",
        "WEC"."ON_ORDER_COUNT" AS "WHS_ONORDER_CNT",
        "WEC"."ON_ORDER_QTY" AS "WHS_ONORDER_QTY",
        -- Equipment Catalog Inventory reorder threshold
        "WEC"."REORDER_THRESHOLD" AS "WHS_TARGET_QTY",
        -- Quantity required to meet pending orders and inventory specifications at current warehouse location
        CASE
          WHEN "WEC"."REC_ORDER_QTY" > 0 THEN "WEC"."REC_ORDER_QTY"
          ELSE 0
        END AS "WHS_ORDER_QTY",
        -- Quantity in excess of pending orders and inventory specifications at current warehouse location
        CASE
          WHEN "WEC"."REC_ORDER_QTY" < 0 THEN ABS("WEC"."REC_ORDER_QTY")
          ELSE 0
        END AS "WHS_EXCESS_QTY",
        -- Related RECIDs
        "WEC"."TENANTS_RECID" AS "TENANTS_RECID"
      FROM (
        SELECT
          "ECECBL"."LOCATIONS_RECID" AS "LOCATIONS_RECID",
          "ECECBL"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
          -- Number of UNASSIGNED "Boxes"
          COALESCE(MAX("ECECBL"."UNASSIGNED_COUNT"), 0) AS "UNASSIGNED_COUNT",
          -- Number of "Screws" in the UNASSIGNED "Boxes"
          COALESCE(MAX("ECECBL"."UNASSIGNED_QTY"), 0) AS "UNASSIGNED_QTY",
          -- Number of "Boxes" On Order
          COALESCE(MAX("ECECBL"."ON_ORDER_COUNT"), 0) AS "ON_ORDER_COUNT",
          -- Number of "Screws" in the "Boxes" On Order
          COALESCE(MAX("ECECBL"."ON_ORDER_QTY"), 0) AS "ON_ORDER_QTY",
          -- Number of "Boxes" for PENDING equipment adds
          COALESCE(MAX("ECECBL"."PENDING_COUNT"), 0) AS "PENDING_COUNT",
          -- Number of "Screws" in the "Boxes" for PENDING equipment adds
          COALESCE(MAX("ECECBL"."PENDING_QTY"), 0) AS "PENDING_QTY",
          -- Number of "Screws" below which more should be ordered
          COALESCE(MAX("ECECBL"."REORDER_THRESHOLD"), 0) AS "REORDER_THRESHOLD",
          -- Recommended order quantity
          (
              COALESCE(MAX("ECECBL"."PENDING_QTY"), 0)
            + COALESCE(MAX("ECECBL"."REORDER_THRESHOLD"), 0)
            - COALESCE(MAX("ECECBL"."UNASSIGNED_QTY"), 0)
            - COALESCE(MAX("ECECBL"."ON_ORDER_QTY"), 0)
          ) AS "REC_ORDER_QTY",
          -- Related RECIDs
          "ECECBL"."TENANTS_RECID" AS "TENANTS_RECID"
        FROM (
          -- Warehouse UNASSIGNED
          SELECT
            "EQ"."LOCATIONS_RECID" AS "LOCATIONS_RECID",
            "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
            -- Number of UNASSIGNED EQUIPMENT records
            COUNT(*)  AS "UNASSIGNED_COUNT",
            -- Quantity of UNASSIGNED equipment
            SUM(
              CASE
                WHEN "EC"."HAS_UNITS" = 1 THEN COALESCE("EQ"."UNITS", 0)
                ELSE 1
              END
            ) AS "UNASSIGNED_QTY",
            -- Placeholders for coalesced data
            NULL AS "ON_ORDER_COUNT",
            NULL AS "ON_ORDER_QTY",
            NULL AS "REORDER_THRESHOLD",
            NULL AS "PENDING_COUNT",
            NULL AS "PENDING_QTY",
            -- Related RECIDs
            "EQ"."TENANTS_RECID" AS "TENANTS_RECID"
          FROM "EQUIPMENT" "EQ"
          JOIN "LOCATIONS" "LOC"
            ON "LOC"."RECID" = "EQ"."LOCATIONS_RECID"
              AND "LOC"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
              AND "LOC"."WAREHOUSE_FLAG" = 1
          JOIN "EQP_CATALOG" "EC"
            ON "EC"."RECID" = "EQ"."EQP_CATALOG_RECID"
              AND "EC"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
          JOIN "LISTS" "ESL"
            ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
              AND "ESL"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
          WHERE
            "ESL"."CODE" = 'UNASSIGNED'
            AND "EQ"."LOCATIONS_RECID" IS NOT NULL
            AND "EQ"."CONSUMED" = 0
          GROUP BY
            "EQ"."LOCATIONS_RECID",
            "EQ"."EQP_CATALOG_RECID",
            "EQ"."TENANTS_RECID"

          UNION

          -- Warehouse ON_ORDER
          SELECT
            "EQ"."LOCATIONS_RECID" AS "LOCATIONS_RECID",
            "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
            -- Placeholders for coalesced data
            NULL AS "UNASSIGNED_COUNT",
            NULL AS "UNASSIGNED_QTY",
            -- Number of ON_ORDER EQUIPMENT records
            COUNT(*) AS "ON_ORDER_COUNT",
            -- Quantity of ON_ORDER equipment
            SUM(
              CASE
                WHEN "EC"."HAS_UNITS" = 1 THEN COALESCE("EQ"."UNITS", 0)
                ELSE 1
              END
            ) AS "ON_ORDER_QTY",
            -- Placeholders for coalesced data
            NULL AS "REORDER_THRESHOLD",
            NULL AS "PENDING_COUNT",
            NULL AS "PENDING_QTY",
            -- Related RECIDs
            "EQ"."TENANTS_RECID" AS "TENANTS_RECID"
          FROM "EQUIPMENT" "EQ"
          JOIN "LOCATIONS" "LOC"
            ON "LOC"."RECID" = "EQ"."LOCATIONS_RECID"
              AND "LOC"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
              AND "LOC"."WAREHOUSE_FLAG" = 1
          JOIN "EQP_CATALOG" "EC"
            ON "EC"."RECID" = "EQ"."EQP_CATALOG_RECID"
              AND "EC"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
          JOIN "LISTS" "ESL"
            ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
              AND "ESL"."TENANTS_RECID" = "EQ"."TENANTS_RECID"
          JOIN (
            -- Active purchase orders
            SELECT
              "POE"."EQUIPMENT_RECID" AS "EQUIPMENT_RECID",
              "POSL"."CODE" AS "PO_STATUS_CODE"
            FROM "PURCHASE_ORDER_EQUIPMENT" "POE"
            INNER JOIN "PURCHASE_ORDER" "PO"
              ON "POE"."PURCHASE_ORDER_RECID" = "PO"."RECID"
            INNER JOIN "LISTS" "POSL"
              ON "PO"."PO_STATUS_LISTS_RECID" = "POSL"."RECID"
          ) "POE" ON "EQ"."RECID" = "POE"."EQUIPMENT_RECID"
          WHERE
            -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
            "ESL"."CODE" = 'ON_ORDER'
            AND "POE"."EQUIPMENT_RECID" IS NOT NULL
            AND "POE"."PO_STATUS_CODE" NOT IN ('PENDING', 'CLOSED')
            AND "EQ"."LOCATIONS_RECID" IS NOT NULL
            AND "EQ"."CONSUMED" = 0
          GROUP BY
            "EQ"."LOCATIONS_RECID",
            "EQ"."EQP_CATALOG_RECID",
            "EQ"."TENANTS_RECID"

          UNION

          -- Warehouse PENDING
          SELECT
            "SDEL"."RECID" AS "LOCATIONS_RECID",
            "SDE"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
            -- Placeholders for coalesced data
            NULL AS "UNASSIGNED_COUNT",
            NULL AS "UNASSIGNED_QTY",
            NULL AS "ON_ORDER_COUNT",
            NULL AS "ON_ORDER_QTY",
            NULL AS "REORDER_THRESHOLD",
            -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
            SUM(
              CASE
                WHEN "EC"."HAS_UNITS" = 1 AND COALESCE("EC"."MAX_QUANTITY", 0) > 0 THEN
                  CEIL("SDE"."QUANTITY" / COALESCE("EC"."MAX_QUANTITY", 1))
                ELSE 1
              END
            ) AS "PENDING_COUNT",
            -- Quantity of PENDING Service Desk Equipment ADDs
            SUM(COALESCE("SDE"."QUANTITY", 0)) AS "PENDING_QTY",
            "SDE"."TENANTS_RECID" AS "TENANTS_RECID"
          FROM "SERVICE_DESK_EQP" "SDE"
          JOIN "EQP_CATALOG" "EC"
            ON "EC"."RECID" = "SDE"."EQP_CATALOG_RECID"
              AND "EC"."TENANTS_RECID" = "SDE"."TENANTS_RECID"
          JOIN "LOCATIONS" "SDEL"
            ON "SDE"."FROM_LOCATIONS_RECID" = "SDEL"."RECID"
              AND "SDE"."TENANTS_RECID" = "SDEL"."TENANTS_RECID"
          JOIN "LISTS" "SDESL"
            ON "SDESL"."RECID" = "SDE"."SD_EQP_STATUS_LISTS_RECID"
              AND "SDESL"."TENANTS_RECID" = "SDE"."TENANTS_RECID"
          JOIN "LISTS" "SDEAL"
            ON "SDE"."SD_EQP_ACTIONS_LISTS_RECID" = "SDEAL"."RECID"
              AND "SDE"."TENANTS_RECID" = "SDEAL"."TENANTS_RECID"
          WHERE
            "SDEAL"."CODE" = 'ADD'
            AND "SDESL"."CODE" = 'PENDING'
          GROUP BY
            "SDEL"."RECID",
            "SDE"."EQP_CATALOG_RECID",
            "SDE"."TENANTS_RECID"

          UNION

          -- Warehouse Reorder Threshold
          SELECT
            "ECIL"."RECID" AS "LOCATIONS_RECID",
            "ECI"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
            -- Placeholders for coalesced data
            NULL AS "UNASSIGNED_COUNT",
            NULL AS "UNASSIGNED_QTY",
            NULL AS "ON_ORDER_COUNT",
            NULL AS "ON_ORDER_QTY",
            -- Equipment catalog inventory reorder threshold
            "ECI"."REORDER" AS "REORDER_THRESHOLD",
            -- Placeholders for coalesced data
            NULL AS "PENDING_COUNT",
            NULL AS "PENDING_QTY",
            -- Related RECIDs
            "ECI"."TENANTS_RECID" AS "TENANTS_RECID"
          FROM "EQP_CATALOG_INVENTORY" "ECI"
          JOIN "LOCATIONS" "ECIL"
            ON "ECIL"."RECID" = "ECI"."LOCATIONS_RECID"
              AND "ECIL"."TENANTS_RECID" = "ECI"."TENANTS_RECID"

        ) "ECECBL" -- Equipment Catalog Equipment Counts by Location
        GROUP BY
          "ECECBL"."LOCATIONS_RECID",
          "ECECBL"."EQP_CATALOG_RECID",
          "ECECBL"."TENANTS_RECID"
      ) "WEC" -- Warehouse equipment counts
      JOIN (
        -- Warehouse data
        SELECT
          "WL"."RECID" AS "RECID",
          "WL"."NAME" AS "WAREHOUSE",
          "WLT"."PATH" AS "WAREHOUSE_PATH",
          COALESCE("SSLOC"."RECID", "PWL"."RECID", "WL"."RECID") AS "SSLOC_RECID",
          "WL"."TENANTS_RECID" AS "TENANTS_RECID"
        FROM "LOCATIONS" "WL"
        LEFT JOIN "LOCATIONS_TEXTPATH" "WLT"
          ON "WL"."RECID" = "WLT"."RECID"
            AND "WL"."TENANTS_RECID" = "WLT"."TENANTS_RECID"
        LEFT JOIN "LOCATIONS" "PWL"
          ON "PWL"."RECID" = "WL"."PARENT_LOCATIONS_RECID"
            AND "PWL"."TENANTS_RECID" = "WL"."TENANTS_RECID"
            AND "PWL"."WAREHOUSE_FLAG" = 1
        LEFT JOIN "LOCATIONS" "SSLOC"
          ON "SSLOC"."RECID" = COALESCE("PWL"."RECID", "WL"."RECID")
        WHERE
          "WL"."WAREHOUSE_FLAG" = 1
      ) "WHS"
        ON "WHS"."RECID" = "WEC"."LOCATIONS_RECID"
          AND "WHS"."TENANTS_RECID" = "WEC"."TENANTS_RECID"
      LEFT JOIN "LOCATIONS_TEXTPATH" "SSLT"
        ON "WHS"."SSLOC_RECID" = "SSLT"."RECID"
          AND "WHS"."TENANTS_RECID" = "SSLT"."TENANTS_RECID"

      JOIN "EQP_CATALOG" "EC"
        ON "WEC"."EQP_CATALOG_RECID" = "EC"."RECID"
          AND "WEC"."TENANTS_RECID" = "EC"."TENANTS_RECID"
          AND "EC"."STATUS" = 1
    ) "IRD" -- Inventory reorder data
    LEFT JOIN "LISTS" "ECUL"
      ON "ECUL"."RECID" = "IRD"."ECUL_RECID"
        AND "ECUL"."TENANTS_RECID" = "IRD"."TENANTS_RECID"
  ) "CRD" -- Collected reorder data

  LEFT JOIN (
    -- ALL WAREHOUSES ON HAND
    SELECT
      "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      -- Number of UNASSIGNED EQUIPMENT records
      COUNT(*) AS "ONHAND_CNT",
      -- Quantity of UNASSIGNED equipment
      SUM(
        CASE
          WHEN "EC"."HAS_UNITS" = 1 THEN COALESCE("EQ"."UNITS", 0)
          ELSE 1
        END
      ) AS "ONHAND_QTY"
    FROM "EQUIPMENT" "EQ"
    INNER JOIN "EQP_CATALOG" "EC"
      ON "EQ"."EQP_CATALOG_RECID" = "EC"."RECID"

    INNER JOIN "LOCATIONS" "LOC"
      ON "LOC"."RECID" = "EQ"."LOCATIONS_RECID"
        AND "LOC"."WAREHOUSE_FLAG" = 1

    INNER JOIN "LISTS" "ESL"
      ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
    WHERE
      "ESL"."CODE" = 'UNASSIGNED'
      AND "EQ"."CONSUMED" = 0
    GROUP BY
      "EQ"."EQP_CATALOG_RECID",
      "EC"."HAS_UNITS"
  ) "AW_ONHAND"
    ON "CRD"."EQP_CATALOG_RECID" = "AW_ONHAND"."EQP_CATALOG_RECID"

  LEFT JOIN (
    -- SITE ON HAND
    SELECT
      "SSLOC"."RECID" AS "SITE_RECID",
      "ONHAND_DATA"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      -- Number of UNASSIGNED EQUIPMENT records
      SUM("ONHAND_DATA"."ONHAND_CNT") AS "ONHAND_CNT",
      -- Quantity of UNASSIGNED equipment
      SUM("ONHAND_DATA"."ONHAND_QTY") AS "ONHAND_QTY"
    FROM (
      SELECT
        "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
        "EQ"."LOCATIONS_RECID" AS "LOCATIONS_RECID",
        "WL"."PARENT_LOCATIONS_RECID" AS "PARENT_LOCATIONS_RECID",
        -- Number of UNASSIGNED EQUIPMENT records
        COUNT(*) AS "ONHAND_CNT",
        -- Quantity of UNASSIGNED equipment
        SUM(
          CASE
            WHEN EC.HAS_UNITS = 1 THEN COALESCE("EQ"."UNITS", 0)
            ELSE 1
          END
        ) AS "ONHAND_QTY"
      FROM "EQUIPMENT" "EQ"
      INNER JOIN "EQP_CATALOG" "EC"
        ON "EQ"."EQP_CATALOG_RECID" = "EC"."RECID"

      INNER JOIN "LOCATIONS" "WL"
        ON "WL"."RECID" = "EQ"."LOCATIONS_RECID"
          AND "WL"."WAREHOUSE_FLAG" = 1

      INNER JOIN "LISTS" "ESL"
        ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
      WHERE
        "ESL"."CODE" = 'UNASSIGNED'
        AND "EQ"."CONSUMED" = 0
      GROUP BY
        "EQ"."EQP_CATALOG_RECID",
        "EQ"."LOCATIONS_RECID",
        "WL"."PARENT_LOCATIONS_RECID",
        "EC"."HAS_UNITS"
    ) "ONHAND_DATA"
    LEFT JOIN "LOCATIONS" "PWL"
      ON "PWL"."RECID" = "ONHAND_DATA"."PARENT_LOCATIONS_RECID"
        AND "PWL"."WAREHOUSE_FLAG" = 1
    JOIN "LOCATIONS" "SSLOC"
      ON "SSLOC"."RECID" = COALESCE("PWL"."RECID", "ONHAND_DATA"."LOCATIONS_RECID")
    GROUP BY
      "SSLOC"."RECID",
      "ONHAND_DATA"."EQP_CATALOG_RECID"
  ) "SITE_ONHAND"
    ON "CRD"."EQP_CATALOG_RECID" = "SITE_ONHAND"."EQP_CATALOG_RECID"
      AND "CRD"."SSLOC_RECID" = "SITE_ONHAND"."SITE_RECID"

  LEFT JOIN (
    -- ALL WAREHOUSES ON ORDER
    SELECT
      "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      -- Number of ON_ORDER EQUIPMENT records with an associate PO that is not PENDING or CLOSED
      COUNT(*) AS "ONORDER_CNT",
      -- Quantity of ON_ORDER equipment with an associate PO that is not PENDING or CLOSED
      SUM(
        CASE
          WHEN "EC"."HAS_UNITS" = 1 THEN COALESCE("EQ"."UNITS", 0)
          ELSE 1
        END
      ) AS "ONORDER_QTY"
    FROM "EQUIPMENT" "EQ"
    INNER JOIN "EQP_CATALOG" "EC"
      ON "EQ"."EQP_CATALOG_RECID" = "EC"."RECID"
    INNER JOIN (
      "PURCHASE_ORDER_EQUIPMENT" "POE"
      INNER JOIN "PURCHASE_ORDER" "PO"
        ON "POE"."PURCHASE_ORDER_RECID" = "PO"."RECID"
      INNER JOIN "LISTS" "POSL"
        ON "PO"."PO_STATUS_LISTS_RECID" = "POSL"."RECID"
    ) ON "EQ"."RECID" = "POE"."EQUIPMENT_RECID"
    INNER JOIN "LOCATIONS" "WL"
      ON "EQ"."LOCATIONS_RECID" = "WL"."RECID"
        AND "WL"."WAREHOUSE_FLAG" = 1
    LEFT JOIN "LISTS" "ESL"
      ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
    WHERE
      -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
      "ESL"."CODE" = 'ON_ORDER'
      AND "POSL"."CODE" NOT IN ('PENDING', 'CLOSED')
    GROUP BY
        "EQ"."EQP_CATALOG_RECID"
      , "EC"."HAS_UNITS"
  ) "AW_ONORDER"
    ON "CRD"."EQP_CATALOG_RECID" = "AW_ONORDER"."EQP_CATALOG_RECID"

  LEFT JOIN (
    -- SITE ON ORDER
    SELECT
      "SSLOC"."RECID" AS "SITE_RECID",
      "ONORDER_DATA"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      -- Number of ON_ORDER EQUIPMENT records with an associate PO that is not PENDING or CLOSED
      SUM("ONORDER_DATA"."ONORDER_CNT") AS "ONORDER_CNT",
      -- Quantity of ON_ORDER equipment with an associate PO that is not PENDING or CLOSED
      SUM("ONORDER_DATA"."ONORDER_QTY") AS "ONORDER_QTY"
    FROM (
      SELECT
        "EQ"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
        "EQ"."LOCATIONS_RECID" AS "LOCATIONS_RECID",
        MAX("WL"."PARENT_LOCATIONS_RECID") AS "PARENT_LOCATIONS_RECID",
        COUNT(*) AS "ONORDER_CNT",
        SUM(
          CASE
            WHEN "EC"."HAS_UNITS" = 1 THEN COALESCE("EQ"."UNITS", 0)
            ELSE 1
          END
        ) AS "ONORDER_QTY"
      FROM "EQUIPMENT" "EQ"
      INNER JOIN "EQP_CATALOG" "EC"
        ON "EQ"."EQP_CATALOG_RECID" = "EC"."RECID"
      INNER JOIN (
        "PURCHASE_ORDER_EQUIPMENT" "POE"
        INNER JOIN "PURCHASE_ORDER" "PO"
          ON "POE"."PURCHASE_ORDER_RECID" = "PO"."RECID"
        INNER JOIN "LISTS" "POSL"
          ON "PO"."PO_STATUS_LISTS_RECID" = "POSL"."RECID"
      ) ON "EQ"."RECID" = "POE"."EQUIPMENT_RECID"
      INNER JOIN "LOCATIONS" "WL"
        ON "EQ"."LOCATIONS_RECID" = "WL"."RECID"
          AND "WL"."WAREHOUSE_FLAG" = 1
      LEFT JOIN "LISTS" "ESL"
        ON "ESL"."RECID" = "EQ"."EQP_STATUS_LISTS_RECID"
      WHERE
        -- ON_ORDER records for the ON_ORDER count must have a PO and the PO can't be PENDING or CLOSED
        "ESL"."CODE" = 'ON_ORDER'
        AND "POSL"."CODE" NOT IN ('PENDING', 'CLOSED')
      GROUP BY
        "EQ"."EQP_CATALOG_RECID",
        "EQ"."LOCATIONS_RECID",
        "EC"."HAS_UNITS"
    ) "ONORDER_DATA"
    LEFT JOIN "LOCATIONS" "PWL"
      ON "PWL"."RECID" = "ONORDER_DATA"."PARENT_LOCATIONS_RECID"
        AND "PWL"."WAREHOUSE_FLAG" = 1
    JOIN "LOCATIONS" "SSLOC"
      ON "SSLOC"."RECID" = COALESCE("PWL"."RECID", "ONORDER_DATA"."LOCATIONS_RECID")
    GROUP BY
      "SSLOC"."RECID",
      "ONORDER_DATA"."EQP_CATALOG_RECID"
  ) "SITE_ONORDER"
    ON "CRD"."EQP_CATALOG_RECID" = "SITE_ONORDER"."EQP_CATALOG_RECID"
      AND "CRD"."SSLOC_RECID" = "SITE_ONORDER"."SITE_RECID"

  LEFT JOIN (
    -- ALL WAREHOUSES PENDING
    SELECT
      -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
      SUM(
        CASE
          WHEN ("EC"."HAS_UNITS" = 1 AND COALESCE("EC"."MAX_QUANTITY", 0) > 0) THEN
            CEIL("SDE"."QUANTITY" / COALESCE("EC"."MAX_QUANTITY", 1))
          ELSE 1
        END
      ) AS "PENDING_CNT",
      -- Quantity of PENDING Service Desk Equipment adds
      SUM(COALESCE("SDE"."QUANTITY", 0)) AS "PENDING_QTY",
      "SDE"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID"
    FROM "SERVICE_DESK_EQP" "SDE"
    LEFT JOIN "EQP_CATALOG" "EC"
      ON "EC"."RECID" = "SDE"."EQP_CATALOG_RECID"
    INNER JOIN "LOCATIONS" "WL"
      ON "SDE"."FROM_LOCATIONS_RECID" = "WL"."RECID"
        AND "WL"."WAREHOUSE_FLAG" = 1
    LEFT JOIN "LISTS" "SDESL"
      ON "SDESL"."RECID" = "SDE"."SD_EQP_STATUS_LISTS_RECID"
    LEFT JOIN "LISTS" "SDEAL"
      ON "SDEAL"."RECID" = "SDE"."SD_EQP_ACTIONS_LISTS_RECID"
    WHERE
      "SDEAL"."CODE" = 'ADD'
      AND "SDESL"."CODE" = 'PENDING'
    GROUP BY "SDE"."EQP_CATALOG_RECID"
  ) "AW_PENDING"
    ON "CRD"."EQP_CATALOG_RECID" = "AW_PENDING"."EQP_CATALOG_RECID"

  LEFT JOIN (
    -- SITE PENDING
    SELECT
      "SSLOC"."RECID" AS "SITE_RECID",
      "PENDING_DATA"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
      -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
      SUM("PENDING_DATA"."PENDING_CNT") AS "PENDING_CNT",
      -- Quantity of PENDING Service Desk Equipment adds
      SUM("PENDING_DATA"."PENDING_QTY") AS "PENDING_QTY"
    FROM (
      SELECT
        -- Number of "Boxes" for PENDING Service Desk Equipment ADDs (based on Equipment Catalog MAX_QUANTITY)
        SUM(
          CASE
            WHEN ("EC"."HAS_UNITS" = 1 AND COALESCE("EC"."MAX_QUANTITY", 0) > 0) THEN
              CEIL("SDE"."QUANTITY" / COALESCE("EC"."MAX_QUANTITY", 1))
            ELSE 1
          END
        ) AS "PENDING_CNT",
        -- Quantity of PENDING Service Desk Equipment adds
        SUM(COALESCE("SDE"."QUANTITY", 0)) AS "PENDING_QTY",
        "WL"."RECID" AS "LOCATIONS_RECID",
        "WL"."PARENT_LOCATIONS_RECID" AS "PARENT_LOCATIONS_RECID",
        "SDE"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID"
      FROM "SERVICE_DESK_EQP" "SDE"
      LEFT JOIN "EQP_CATALOG" "EC"
        ON "EC"."RECID" = "SDE"."EQP_CATALOG_RECID"
      INNER JOIN "LOCATIONS" "WL"
        ON "SDE"."FROM_LOCATIONS_RECID" = "WL"."RECID"
          AND "WL"."WAREHOUSE_FLAG" = 1
      LEFT JOIN "LISTS" "SDESL"
        ON "SDESL"."RECID" = "SDE"."SD_EQP_STATUS_LISTS_RECID"
      LEFT JOIN "LISTS" "SDEAL"
        ON "SDEAL"."RECID" = "SDE"."SD_EQP_ACTIONS_LISTS_RECID"
      WHERE
        "SDEAL"."CODE" = 'ADD'
        AND "SDESL"."CODE" = 'PENDING'
      GROUP BY
        "SDE"."EQP_CATALOG_RECID",
        "WL"."RECID",
        "WL"."PARENT_LOCATIONS_RECID"
    ) "PENDING_DATA"
    LEFT JOIN "LOCATIONS" "PWL"
      ON "PWL"."RECID" = "PENDING_DATA"."PARENT_LOCATIONS_RECID"
        AND "PWL"."WAREHOUSE_FLAG" = 1
    JOIN "LOCATIONS" "SSLOC"
      ON "SSLOC"."RECID" = COALESCE("PWL"."RECID", "PENDING_DATA"."LOCATIONS_RECID")
    GROUP BY
      "SSLOC"."RECID",
      "PENDING_DATA"."EQP_CATALOG_RECID"
  ) "SITE_PENDING"
    ON "CRD"."EQP_CATALOG_RECID" = "SITE_PENDING"."EQP_CATALOG_RECID"
      AND "CRD"."SSLOC_RECID" = "SITE_ONORDER"."SITE_RECID"
  LEFT JOIN (
    SELECT
      COALESCE(MAX("COST_OPTIONS"), '') AS "COST_OPTIONS",
      COALESCE(MAX("VENDOR_NAMES"), '') AS "VENDOR_NAMES",
      COALESCE(MAX("VENDOR_RECIDS"), '') AS "VENDOR_RECIDS",
      "EQP_CATALOG_RECID",
      "TENANTS_RECID"
    FROM (
      SELECT
        LISTAGG("COST", ',') WITHIN GROUP (ORDER BY "COST") AS "COST_OPTIONS",
        NULL AS "VENDOR_NAMES",
        NULL AS "VENDOR_RECIDS",
        "EQP_CATALOG_RECID",
         "TENANTS_RECID"
      FROM (
        SELECT DISTINCT
          CAST("ECV"."COST" AS DECIMAL (11, 2)) AS "COST",
          "ECV"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
          "ECV"."TENANTS_RECID" AS "TENANTS_RECID"
        FROM "EQP_CATALOG_VENDORS" "ECV"
        JOIN "VENDORS"
          ON "VENDORS"."RECID" = "ECV"."VENDORS_RECID"
            AND "VENDORS"."TENANTS_RECID" = "ECV"."TENANTS_RECID"
        GROUP BY
          "ECV"."COST",
          "ECV"."EQP_CATALOG_RECID",
          "ECV"."TENANTS_RECID"
      ) "AGG_COSTS"
      GROUP BY
        "EQP_CATALOG_RECID",
        "TENANTS_RECID"

      UNION

      SELECT
        NULL AS "COST",
        LISTAGG("VENDOR_NAME", ',') WITHIN GROUP (ORDER BY "VENDOR_NAME") AS "VENDOR_NAMES",
        LISTAGG("VENDOR_RECID", ',') WITHIN GROUP (ORDER BY "VENDOR_NAME") AS "VENDOR_RECIDS",
        "EQP_CATALOG_RECID",
        "TENANTS_RECID"
      FROM (
        SELECT DISTINCT
          "VENDORS"."NAME" AS "VENDOR_NAME",
          "VENDORS"."RECID" AS "VENDOR_RECID",
          "ECV"."EQP_CATALOG_RECID" AS "EQP_CATALOG_RECID",
          "ECV"."TENANTS_RECID" AS "TENANTS_RECID"
        FROM "EQP_CATALOG_VENDORS" "ECV"
        JOIN "VENDORS"
          ON "VENDORS"."RECID" = "ECV"."VENDORS_RECID"
            AND "VENDORS"."TENANTS_RECID" = "ECV"."TENANTS_RECID"
        GROUP BY
          "VENDORS"."NAME",
          "VENDORS"."RECID",
          "ECV"."EQP_CATALOG_RECID",
          "ECV"."TENANTS_RECID"
      ) "AGG_NAMES"
      GROUP BY
        "EQP_CATALOG_RECID",
        "TENANTS_RECID"
    ) "COSTS_AND_NAMES"
    GROUP BY
      "EQP_CATALOG_RECID",
      "TENANTS_RECID"
  ) "VENDOR_DATA"
    ON "VENDOR_DATA"."EQP_CATALOG_RECID" = "CRD"."EQP_CATALOG_RECID"
  GROUP BY
    "CRD"."EQP_CATALOG_RECID",
    "CRD"."SSLOC_RECID",
    "CRD"."WHS_RECID",
    "CRD"."WHS_PATH",
    "CRD"."PART_NUMBER",
    "CRD"."DESCRIPTION",
    "CRD"."MANUFACTURER",
    "CRD"."MANU_PART_NUM",
    "CRD"."WHS_ONHAND_CNT",
    "CRD"."WHS_ONHAND_QTY",
    "CRD"."WHS_PENDING_CNT",
    "CRD"."WHS_PENDING_QTY",
    "CRD"."WHS_ONORDER_CNT",
    "CRD"."WHS_ONORDER_QTY",
    "CRD"."QTY_UNITS",
    "CRD"."WHS_TARGET_QTY",
    "CRD"."WHS_ORDER_QTY",
    "CRD"."WHS_EXCESS_QTY",
    "SITE_ONHAND"."ONHAND_CNT",
    "SITE_ONHAND"."ONHAND_QTY",
    "SITE_PENDING"."PENDING_CNT",
    "SITE_PENDING"."PENDING_QTY",
    "SITE_ONORDER"."ONORDER_CNT",
    "SITE_ONORDER"."ONORDER_QTY",
    "AW_ONHAND"."ONHAND_CNT",
    "AW_ONHAND"."ONHAND_QTY",
    "AW_PENDING"."PENDING_CNT",
    "AW_PENDING"."PENDING_QTY",
    "AW_ONORDER"."ONORDER_CNT",
    "AW_ONORDER"."ONORDER_QTY",
    "CRD"."TENANTS_RECID"
) "INVENTORY_REORDER_DETAILS"
GROUP BY
  "WAREHOUSE",
  "PART_NUMBER",
  "DESCRIPTION",
  "MANUFACTURER",
  "MANU_PART_NUM",
  "ONHAND_CNT",
  "ONHAND_QTY",
  "PENDING_CNT",
  "PENDING_QTY",
  "ONORDER_CNT",
  "ONORDER_QTY",
  "QTY_UNITS",
  "TARGET_QTY",
  "ORDER_QTY",
  "EXCESS_QTY",
  "SITE_ONHAND_CNT",
  "SITE_ONHAND_QTY",
  "SITE_PENDING_CNT",
  "SITE_PENDING_QTY",
  "SITE_ONORDER_CNT",
  "SITE_ONORDER_QTY",
  "AW_ONHAND_CNT",
  "AW_ONHAND_QTY",
  "AW_PENDING_CNT",
  "AW_PENDING_QTY",
  "AW_ONORDER_CNT",
  "AW_ONORDER_QTY",
  "EQP_CATALOG_RECID",
  "SITE_RECID",
  "WHS_RECID",
  "TENANTS_RECID"