sql

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

Browser not supported