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
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
-- 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` |
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
-- 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" |