Document toolboxDocument toolbox

(2024.2) Inventory Reorder Details Report

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



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



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