From time to time users may report orders not populating on the production dashboard.
Use this query to understand why it isn't showing. Remember if changes are made to the sproc, this diagnostic query may need updating too.
DECLARE @OrderNumber VARCHAR(20) = 'PUT_ORDER_NUMBER_HERE';
-- Match the values used when loading the sundry screen
DECLARE @MakeAt CHAR(1) = 'W';
DECLARE @ShowOrders VARCHAR(20) = 'All';
DECLARE @ItemType VARCHAR(20) = '';
DECLARE @Team VARCHAR(50) = '';
DECLARE @OrderType VARCHAR(10) = 'All';
DECLARE @Wtops BIT = 0;
IF (@Team = 'All')
SET @Team = '';
;WITH OrderInfo AS
(
SELECT TOP (1)
JT.OrderNo,
CAST(JT.DeliveryDate AS DATE) AS DeliveryDate,
JT.MakeAt AS JobMakeAt
FROM dbo.JobTrack JT
WHERE JT.OrderNo = @OrderNumber
),
ItemChecks AS
(
SELECT DISTINCT
LOI.OrderNumber,
OI.DeliveryDate,
OI.JobMakeAt,
LOI.LineItemRef,
LOI.Item,
LOI.Code,
LOI.Description,
LOI.Comments,
LOI.PrintItem,
LOI.MakeAt AS ItemMakeAt,
LOI.OrderType AS LiveOrdOrderType,
CASE
WHEN CAU.Code IS NULL THEN 0
ELSE 1
END AS IsUnit,
CASE
WHEN CAU.Code IS NULL THEN ISNULL(CIT.ItemType, '')
ELSE 'Unit'
END AS DerivedItemType,
COALESCE
(
IT.PIT_Team,
IIF(LOI.Code = 'SHORTAGE', 'Sundry', NULL),
CASE
WHEN OI.OrderNo LIKE '%/%'
THEN COALESCE(CT.PIT_Team, FullTeam.PIT_Team)
ELSE
COALESCE(FullTeam.PIT_Team, CT.PIT_Team, '')
END,
''
) AS DerivedTeam,
---------------------------------------------------------------------
-- Original procedure inclusion checks
---------------------------------------------------------------------
CASE
WHEN LOI.LineItemRef LIKE OI.OrderNo + '[a-Z]%' THEN 1
ELSE 0
END AS Pass_LineItemRefJoin,
CASE
WHEN
(
@Wtops = 1
OR @MakeAt =
CASE
WHEN OI.JobMakeAt = 'S' THEN ''
ELSE REPLACE(LOI.MakeAt, 'H', 'W')
END
)
THEN 1
ELSE 0
END AS Pass_MakeAt,
CASE
WHEN LOI.Code NOT IN
(
'ORIG_DEL_DATE',
'INST',
'INSTRUCTION',
'ORIG_JOB',
'PCC_SQ'
)
THEN 1
ELSE 0
END AS Pass_ExcludedCodes,
CASE
WHEN LOI.OrderType NOT IN ('his', 'upg') THEN 1
ELSE 0
END AS Pass_ExcludedOrderTypes,
CASE
WHEN
(
CASE
WHEN CAU.Code IS NULL THEN ISNULL(CIT.ItemType, '')
ELSE 'Unit'
END <> 'FitKit'
OR LOI.Code = 'FitKit'
)
THEN 1
ELSE 0
END AS Pass_FitKitRule,
CASE
WHEN LOI.PrintItem = 1 THEN 1
ELSE 0
END AS Pass_PrintItem,
CASE
WHEN LOI.Description NOT LIKE '%DELETED%' THEN 1
ELSE 0
END AS Pass_NotDeleted,
CASE
WHEN LOI.Description NOT LIKE '%REMOVED%' THEN 1
ELSE 0
END AS Pass_NotRemoved,
CASE
WHEN LOI.Description NOT LIKE '%PRICING%' THEN 1
ELSE 0
END AS Pass_NotPricing,
CASE
WHEN
(
ISNULL(LOI.Code, '')
+ '_'
+ ISNULL(LOI.Description, '')
+ ISNULL(LOI.Comments, '')
) <> 'SUNDRIES_SUNDRY ITEMS'
THEN 1
ELSE 0
END AS Pass_NotSundriesPlaceholder,
CASE
WHEN LOI.Code NOT LIKE 'FIN%' THEN 1
ELSE 0
END AS Pass_NotFinCode,
CASE
WHEN
(
ISNULL(LOI.Description, '')
+ ' '
+ ISNULL(LOI.Comments, '')
) NOT LIKE '%NOT PRICED IN CAT%'
THEN 1
ELSE 0
END AS Pass_NotPricedInCat,
---------------------------------------------------------------------
-- Later filtering in the procedure
---------------------------------------------------------------------
CASE
WHEN @OrderType = 'K'
AND OI.OrderNo LIKE '%/%'
THEN 0
WHEN @OrderType = 'S'
AND OI.OrderNo NOT LIKE '%/%'
THEN 0
ELSE 1
END AS Pass_OrderTypeFilter,
CASE
WHEN @ItemType = '' THEN 1
WHEN
CASE
WHEN
CASE
WHEN CAU.Code IS NULL THEN ISNULL(CIT.ItemType, '')
ELSE 'Unit'
END = ''
THEN 'Other'
ELSE
CASE
WHEN CAU.Code IS NULL THEN ISNULL(CIT.ItemType, '')
ELSE 'Unit'
END
END = @ItemType
THEN 1
ELSE 0
END AS Pass_ItemTypeFilter,
CASE
WHEN @Team = '' THEN 1
WHEN COALESCE
(
IT.PIT_Team,
IIF(LOI.Code = 'SHORTAGE', 'Sundry', NULL),
CASE
WHEN OI.OrderNo LIKE '%/%'
THEN COALESCE(CT.PIT_Team, FullTeam.PIT_Team)
ELSE
COALESCE(FullTeam.PIT_Team, CT.PIT_Team, '')
END,
''
) = @Team
THEN 1
WHEN COALESCE
(
IT.PIT_Team,
IIF(LOI.Code = 'SHORTAGE', 'Sundry', NULL),
CASE
WHEN OI.OrderNo LIKE '%/%'
THEN COALESCE(CT.PIT_Team, FullTeam.PIT_Team)
ELSE
COALESCE(FullTeam.PIT_Team, CT.PIT_Team, '')
END,
''
) = ''
THEN 1
ELSE 0
END AS Pass_TeamFilter,
CASE
WHEN @ShowOrders <> 'Customer Care' THEN 1
WHEN CC.CC_OrderNumber IS NOT NULL THEN 1
ELSE 0
END AS Pass_CustomerCareFilter
FROM OrderInfo OI
INNER JOIN dbo.LiveOrdItems LOI
ON LOI.OrderNumber = OI.OrderNo
LEFT JOIN dbo.Count_As_Unit CAU
ON CAU.Code = LOI.Code
LEFT JOIN dbo.CAT_ItemType CIT
ON CIT.Code = LOI.Code
LEFT JOIN dbo.PROD_Item_Team CT
ON LOI.Code = CT.PIT_Code
AND CT.PIT_Sundry = 1
LEFT JOIN dbo.PROD_Item_Team IT
ON LOI.LineItemRef = IT.PIT_Code
LEFT JOIN dbo.PROD_Item_Team FullTeam
ON LOI.Code = FullTeam.PIT_Code
AND FullTeam.PIT_Sundry = 0
LEFT JOIN
(
SELECT DISTINCT
REPLACE
(
REPLACE(EventType, 'Addition, Order Number: ', ''),
', raised.',
''
) AS CC_OrderNumber
FROM dbo.InstEnquiryEvents
WHERE EventType LIKE 'Addition, Order Number:%'
AND DateTimeLogged > '01/jan/2024'
) CC
ON CC.CC_OrderNumber = OI.OrderNo
)
SELECT
OrderNumber,
LineItemRef,
Item,
Code,
Description,
Comments,
DerivedItemType,
DerivedTeam,
IsUnit,
CASE
WHEN Pass_LineItemRefJoin = 1
AND Pass_MakeAt = 1
AND Pass_ExcludedCodes = 1
AND Pass_ExcludedOrderTypes = 1
AND Pass_FitKitRule = 1
AND Pass_PrintItem = 1
AND Pass_NotDeleted = 1
AND Pass_NotRemoved = 1
AND Pass_NotPricing = 1
AND Pass_NotSundriesPlaceholder = 1
AND Pass_NotFinCode = 1
AND Pass_NotPricedInCat = 1
AND Pass_OrderTypeFilter = 1
AND Pass_ItemTypeFilter = 1
AND Pass_TeamFilter = 1
AND Pass_CustomerCareFilter = 1
THEN 1
ELSE 0
END AS WillAppear,
STUFF
(
(
SELECT ' | ' + Reason
FROM
(
VALUES
(
CASE
WHEN Pass_LineItemRefJoin = 0
THEN 'LineItemRef does not match OrderNo + letter pattern'
END
),
(
CASE
WHEN Pass_MakeAt = 0
THEN 'Fails MakeAt / Wtops filter'
END
),
(
CASE
WHEN Pass_ExcludedCodes = 0
THEN 'Code is explicitly excluded'
END
),
(
CASE
WHEN Pass_ExcludedOrderTypes = 0
THEN 'OrderType is his or upg'
END
),
(
CASE
WHEN Pass_FitKitRule = 0
THEN 'Derived ItemType is FitKit but Code is not FitKit'
END
),
(
CASE
WHEN Pass_PrintItem = 0
THEN 'PrintItem is not 1'
END
),
(
CASE
WHEN Pass_NotDeleted = 0
THEN 'Description contains DELETED'
END
),
(
CASE
WHEN Pass_NotRemoved = 0
THEN 'Description contains REMOVED'
END
),
(
CASE
WHEN Pass_NotPricing = 0
THEN 'Description contains PRICING'
END
),
(
CASE
WHEN Pass_NotSundriesPlaceholder = 0
THEN 'Generic SUNDRIES_SUNDRY ITEMS line is excluded'
END
),
(
CASE
WHEN Pass_NotFinCode = 0
THEN 'Code starts with FIN'
END
),
(
CASE
WHEN Pass_NotPricedInCat = 0
THEN 'Description/comments contain NOT PRICED IN CAT'
END
),
(
CASE
WHEN Pass_OrderTypeFilter = 0
THEN 'Removed by @OrderType K/S filter'
END
),
(
CASE
WHEN Pass_ItemTypeFilter = 0
THEN 'Removed by @ItemType filter'
END
),
(
CASE
WHEN Pass_TeamFilter = 0
THEN 'Removed by @Team filter'
END
),
(
CASE
WHEN Pass_CustomerCareFilter = 0
THEN 'Removed because ShowOrders = Customer Care and order is not flagged as CC'
END
)
) Reasons(Reason)
WHERE Reason IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
3,
''
) AS ReasonNotShown
FROM ItemChecks
ORDER BY
CASE
WHEN Pass_LineItemRefJoin = 1
AND Pass_MakeAt = 1
AND Pass_ExcludedCodes = 1
AND Pass_ExcludedOrderTypes = 1
AND Pass_FitKitRule = 1
AND Pass_PrintItem = 1
AND Pass_NotDeleted = 1
AND Pass_NotRemoved = 1
AND Pass_NotPricing = 1
AND Pass_NotSundriesPlaceholder = 1
AND Pass_NotFinCode = 1
AND Pass_NotPricedInCat = 1
AND Pass_OrderTypeFilter = 1
AND Pass_ItemTypeFilter = 1
AND Pass_TeamFilter = 1
AND Pass_CustomerCareFilter = 1
THEN 1
ELSE 0
END,
Item,
LineItemRef;