AdHoc Grids allow for the creation of data Grids based on a User-Defined SQL query; any AdHoc Grid can be used to generate reports. Knowledge of the SQL language is Required.
For information on how to access a User-Defined Field in an AdHoc Query, please see our article on UDFs.
Creating an AdHoc Grid
When writing your own AdHoc Grid can be a difficult process for an inexperienced User. PCR-360 provides useful tools in both the AdHoc Grid's Query Builder and the Data Dictionary.
-
Navigate to Admin > AdHoc Grids > Manage Grids.
-
Click the
-
Enter the desired SQL Statement
-
Click the
-
Click the
-
Click the
-
Provide a Name, Description, as well as any desired Role Permissions and any other customizations to the look of the new Grid.
-
Click the Save button to save the Grid.
The following functions are not allowed for use: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, EMPTY, and SELECT INTO.
When writing AdHoc grids do not use spaces and other characters in the SQL column Aliases. Using characters other than UPPER-CASE and UNDERSCORE characters, the AdHoc Grid will break and cause any filters to not work.
AdHoc Grid Library
For the convenience of our customers, this Library contains pre-built AdHoc Grids that anyone may copy and add to their PCR-360 application. If you require an AdHoc query not provided by this Library, please contact your Account Manager to request Professional Services; your Account Manager will provide you with a quote to create an AdHoc tailored to your needs.
Using the Library
To load the provided queries into your Organization's instance of PCR-360, simply enter the desired query as your SQL Statement when creating an AdHoc
RECID vs. ROW ID
The usage of RECID or ROW ID is for clarity about what the RECID or ROW ID represents.
ROW IDs - Row Identification Numbers (ROW IDs) are unique IDs to the row of data in the grid they're assigned to. Some grids do not have a RECID but all grids have ROW IDs.
RECIDs - Record Identification Numbers (RECIDs) are unique IDs for records within a given table. When a grid references a specific record from a table, the Table Name is usually the column name of the RECID that the grid is referencing.
-
IE: SERVICES_RECID in any given grid is referencing the RECID value from the SERVICES Table.
-
Some table names are long and need to be abbreviated to meet the 30 character limit imposed by Oracle Databases.
There are several reasons both are used.
-
Some grids have multiple RECIDs and a ROW ID is unique to that row of data in the grid.
-
Some grids don’t have a RECID and the ROW ID is unique to that row of data.
-
Some ROW IDs are actually combined RECIDs for two or more records in the row.
Library of AdHoc Queries
Cable Locations Report Query
We don't have a way to export this macro.
This AdHoc Query was created to replace the Cable Locations Report when it was removed from the built-in reports. This AdHoc has notable performance improvements on the original report. This report will display all Locations for all Cable items. This includes from and to different Locations and also from and to different Equipment.
Cable Locations Report for Oracle and MySQL
Cable Locations Report
-- This query is valid for MySQL AND Oracle
SELECT
C.RECID,
C.NAME,
CL.VALUE AS C_CABLE_TYPE_VALUE,
ORIGIN_EQUIPMENT.FROM_EQP_TYPE,
ORIGIN_EQUIPMENT.FROM_EQP,
ORIGIN_EQUIPMENT.FROM_LOC,
DESTINATION_PAIRS.LOW_PAIR,
DESTINATION_PAIRS.HIGH_PAIR,
DESTINATION_EQUIPMENT.TO_EQP_TYPE,
DESTINATION_EQUIPMENT.TO_EQP,
DESTINATION_EQUIPMENT.TO_LOC,
C.SIGNAL_LOSS,
C.LENGTH
FROM CABLES C
LEFT JOIN LISTS CL ON CL.RECID = C.CABLE_TYPE_LISTS_RECID
LEFT JOIN (
SELECT
CE.CABLES_RECID,
LTP.PATH AS FROM_LOC,
COALESCE(E.EQUIPMENT_ID, E.ASSET_TAG, NULL) AS FROM_EQP,
L.VALUE AS FROM_EQP_TYPE
FROM CABLES_EQUIPMENT CE
LEFT JOIN EQUIPMENT E ON CE.EQUIPMENT_RECID = E.RECID
LEFT JOIN LISTS L ON L.RECID = E.CABLING_EQP_TYPE_LISTS_RECID
LEFT JOIN LOCATIONS_TEXTPATH LTP ON E.LOCATIONS_RECID = LTP.RECID
WHERE CE.FROM_EQUIPMENT_FLAG = 1
)ORIGIN_EQUIPMENT ON ORIGIN_EQUIPMENT.CABLES_RECID = C.RECID
LEFT JOIN (
SELECT
CE.CABLES_RECID,
CE.EQUIPMENT_RECID,
LTP.PATH AS TO_LOC,
COALESCE(E.EQUIPMENT_ID, E.ASSET_TAG, NULL) AS TO_EQP,
L.VALUE AS TO_EQP_TYPE
FROM CABLES_EQUIPMENT CE
LEFT JOIN EQUIPMENT E ON CE.EQUIPMENT_RECID = E.RECID
LEFT JOIN LISTS L ON L.RECID = E.CABLING_EQP_TYPE_LISTS_RECID
LEFT JOIN LOCATIONS_TEXTPATH LTP ON E.LOCATIONS_RECID = LTP.RECID
WHERE CE.FROM_EQUIPMENT_FLAG = 0
) DESTINATION_EQUIPMENT ON DESTINATION_EQUIPMENT.CABLES_RECID = C.RECID
LEFT JOIN (
SELECT
PSE.TO_EQUIPMENT_RECID,
MIN(PS.NUM) AS LOW_PAIR,
MAX(PS.NUM) AS HIGH_PAIR
FROM PAIRS_STRANDS_TO_EQP PSE
LEFT JOIN PAIRS_STRANDS PS ON PS.RECID = PSE.PAIRS_STRANDS_RECID
GROUP BY PSE.TO_EQUIPMENT_RECID
) DESTINATION_PAIRS ON DESTINATION_PAIRS.TO_EQUIPMENT_RECID = DESTINATION_EQUIPMENT.EQUIPMENT_RECID
GROUP BY
C.RECID,
C.NAME,
CL.VALUE,
C.SIGNAL_LOSS,
C.LENGTH,
ORIGIN_EQUIPMENT.FROM_EQP_TYPE,
ORIGIN_EQUIPMENT.FROM_EQP,
ORIGIN_EQUIPMENT.FROM_LOC,
DESTINATION_EQUIPMENT.TO_EQP_TYPE,
DESTINATION_EQUIPMENT.TO_EQP,
DESTINATION_EQUIPMENT.TO_LOC,
DESTINATION_PAIRS.LOW_PAIR,
DESTINATION_PAIRS.HIGH_PAIR
Service Desk Cabling Query
We don't have a way to export this macro.
This AdHoc Query was created to replace the Service Desk Cabling Report when it was removed from the built-in reports. This AdHoc has notable performance improvements on the original report. This report will display all Cabling Records associated with Service Desk Records.
Service Desk Cabling for Oracle and MySQL
Service Desk Cabling
-- This query is valid for MySQL AND Oracle
SELECT
-- SERVICE ORDER
SD.SD_NUMBER,
L_SD_STATUS.VALUE AS SD_STATUS,
SD.DESCRIPTION AS SD_DESCRIPTION,
-- SERVICE DESK ACTION
LPAD(SD_ACTIONS.SDA_NUMBER, 3, 0) AS SD_ACTION_NUMBER,
L_SD_ACTIONS_TYPE.VALUE AS SD_ACTION_TYPE,
L_SD_ACTIONS_STATUS.VALUE AS SD_ACTION_STATUS,
COALESCE(SD_ACTION_SERVICE.SERVICE_ID_FMT, SD_ACTION_SERVICE.SERVICE_ID) AS SD_ACTION_SERVICE,
-- SERVICE DESK CABLING
SD_CABLE.INSTANCE AS SD_CABLE_INSTANCE,
L_SD_CABLE_TYPE.VALUE AS SD_CABLE_TYPE,
-- FROM EQUIPMENT
COALESCE(FROM_EQUIPMENT.EQUIPMENT_ID, FROM_EQUIPMENT.ASSET_TAG) AS FROM_EQUIPMENT_ID,
FROM_EQP_CATALOG.DESCRIPTION AS FROM_EQP_DESCRIPTION,
COALESCE(FROM_PPL.ALIAS, FROM_PPL.PORT) AS FROM_PPL,
L_FROM_PPL_STATUS.VALUE AS FROM_PPL_STATUS,
-- CABLES
CABLES.NAME AS CABLE,
PAIRS_STRANDS.NUM AS PAIR_STRAND,
-- TO EQUIPMENT
COALESCE(TO_EQUIPMENT.EQUIPMENT_ID, TO_EQUIPMENT.ASSET_TAG) AS TO_EQUIPMENT_ID,
TO_EQP_CATALOG.DESCRIPTION AS TO_EQP_DESCRIPTION,
COALESCE(TO_PPL.ALIAS, TO_PPL.PORT) AS TO_PPL,
L_TO_PPL_STATUS.VALUE AS TO_PPL_STATUS,
-- BACKBONE
COALESCE(BACKBONE_SERVICE_REAL.SERVICE_ID_FMT, BACKBONE_SERVICE_REAL.SERVICE_ID) AS BACKBONE,
-- SERVICE ORDER DATES
SD.CREATED_DATE AS SD_CREATED_DATE,
SD.DUE_DATE AS SD_DUE_DATE,
SD.COMPLETE_DATE AS SD_COMPLETED_DATE,
SD.FINALIZED_DATE AS SD_FINALIZED_DATE,
-- SERVICE DESK ACTION DATES
SD_ACTIONS.CREATED_DATE AS SD_ACTION_CREATED_DATE,
SD_ACTIONS.DUE_DATE AS SD_ACTION_DUE_DATE,
SD_ACTIONS.COMPLETE_DATE AS SD_ACTION_COMPLETED_DATE,
SD_ACTIONS.FINALIZED_DATE AS SD_ACTION_FINALIZED_DATE
-- SERVICE DESK CABLING
FROM SERVICE_DESK_CABLING SD_CABLE
JOIN LISTS L_SD_CABLE_TYPE
ON L_SD_CABLE_TYPE.RECID = SD_CABLE.SD_CABLE_ACTION_LISTS_RECID
-- FROM EQUIPMENT
LEFT JOIN EQUIPMENT FROM_EQUIPMENT
ON FROM_EQUIPMENT.RECID = SD_CABLE.FROM_EQUIPMENT_RECID
LEFT JOIN EQP_CATALOG FROM_EQP_CATALOG
ON FROM_EQP_CATALOG.RECID = FROM_EQUIPMENT.EQP_CATALOG_RECID
LEFT JOIN PORTS_PINS_LENS FROM_PPL
ON FROM_PPL.RECID = SD_CABLE.FROM_PORTS_PINS_LENS_RECID
LEFT JOIN LISTS L_FROM_PPL_STATUS
ON L_FROM_PPL_STATUS.RECID = FROM_PPL.PAIR_STATUS_LISTS_RECID
-- CABLES/PAIRS/STRANDS
LEFT JOIN CABLES
ON CABLES.RECID = SD_CABLE.CABLES_RECID
LEFT JOIN PAIRS_STRANDS
ON PAIRS_STRANDS.RECID = SD_CABLE.PAIRS_STRANDS_RECID
-- TO EQUIPMENT
LEFT JOIN EQUIPMENT TO_EQUIPMENT
ON TO_EQUIPMENT.RECID = SD_CABLE.TO_EQUIPMENT_RECID
LEFT JOIN EQP_CATALOG TO_EQP_CATALOG
ON TO_EQP_CATALOG.RECID = TO_EQUIPMENT.EQP_CATALOG_RECID
LEFT JOIN PORTS_PINS_LENS TO_PPL
ON TO_PPL.RECID = SD_CABLE.TO_PORTS_PINS_LENS_RECID
LEFT JOIN LISTS L_TO_PPL_STATUS
ON L_TO_PPL_STATUS.RECID = TO_PPL.PAIR_STATUS_LISTS_RECID
-- BACKBONE
LEFT JOIN BACKBONE_SERVICES
ON BACKBONE_SERVICES.RECID = SD_CABLE.BACKBONE_SERVICES_RECID
LEFT JOIN SERVICES BACKBONE_SERVICE_REAL
ON BACKBONE_SERVICE_REAL.RECID = BACKBONE_SERVICES.SERVICES_RECID
-- SERVICE ORDER
JOIN SERVICE_DESK SD
ON SD.RECID = SD_CABLE.SERVICE_DESK_RECID
JOIN LISTS L_SD_STATUS
ON L_SD_STATUS.RECID = SD.SD_STATUS_LISTS_RECID
-- SERVICE DESK ACTION
JOIN SERVICE_DESK_ACTIONS SD_ACTIONS
ON SD_ACTIONS.RECID = SD_CABLE.SERVICE_DESK_ACTIONS_RECID
JOIN LISTS L_SD_ACTIONS_TYPE
ON L_SD_ACTIONS_TYPE.RECID = SD_ACTIONS.SD_ACTION_LISTS_RECID
JOIN LISTS L_SD_ACTIONS_STATUS
ON L_SD_ACTIONS_STATUS.RECID = SD_ACTIONS.SD_STATUS_LISTS_RECID
JOIN SERVICES SD_ACTION_SERVICE
ON SD_ACTION_SERVICE.RECID = SD_ACTIONS.SERVICES_RECID
Inventory Reorder Details Report
We don't have a way to export this macro.
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
We don't have a way to export this macro.
-- 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
We don't have a way to export this macro.
-- 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"
Duplicate MAC Address Locator Query
We don't have a way to export this macro.
This query only needs to be run prior to requesting an upgrade to 2020.3 or newer. It will allow your Organization to locate multiple Equipment that shares a MAC Address. In 2020.3, a constraint is placed on MAC Address fields being unique across the system.
Duplicate MAC Address Locator for Oracle and MySQL
Service Desk Cabling
SELECT * FROM EQUIPMENT WHERE MAC_ADDRESS IN (SELECT MAC_ADDRESS FROM EQUIPMENT E2 GROUP BY E2.MAC_ADDRESS HAVING COUNT(E2.MAC_ADDRESS) > 1)