Versions Compared

Old Version 1

changes.mady.by.user Joe Parish

Saved on

compared with

New Version 2

changes.mady.by.user Karis Hathaway

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: moved collapse to cloaks because of macro bug

The output from this query indicates which locations do not have enough on hand or on order equipment to meet all pending equipment adds and inventory threshold specifications, as well as inventory available at other warehouse locations in excess of

...

the pending equipment adds and inventory threshold specifications for that location. Additionally, the output includes comma-separated lists of available costs and vendors

...

...

Inventory Reorder Details Report for MySQL

...

Toggle cloak
id@mysql
 Cloak
id@mysql

 Code Block
languagesql
linenumberstrue
-- 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 Details Report for Oracle
 Toggle cloak
id@oracle
collapse
 Cloak
id@oracle

 Code Block
languagesql
titleInventory Reorder Details Report for Oracle
linenumbers
true
true
-- 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"