Hold State Reporting

Hold State Reporting Examples

This topic details the data entities used to support the Hold State reporting and examples of reports built during the POC. There are (2) physical files and (5) views as listed below.

Hold State Transaction File	Comments
EF.TagHoldState	This core transaction file maintains the actual start/stop times when a TagID was inventoried in a given State at a given location. The hold time is stored as universal 'ticks' which can be converted to hours, minutes, seconds using views as described further.
Table Script CREATE TABLE [EF].[TagHoldState]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [TagID] [nvarchar](30) NULL, [HoldState] [nvarchar](20) NULL, [InHoldState] [bit] NOT NULL, [HoldProcessed] [bit] NOT NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [LocationLevel1] [nvarchar](10) NULL, [LocationLevel2] [nvarchar](10) NULL, [LocationLevel3] [nvarchar](10) NULL, [LocationLevel4] [nvarchar](10) NULL, [LocationLevel5] [nvarchar](10) NULL, [LotNumber] [nvarchar](10) NULL, [LotStatus] [nvarchar](1) NULL, [HoldTimeTicks] [bigint] NULL, CONSTRAINT [PK_EF.TagHoldState] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Table Data Example

Hold State Config Table	Comments
EF.BaseItemHoldTimes	Base Item Hold Table with user defined Hold times and offsets for each Hold State. This is maintained in the EF menu,
Table Script CREATE TABLE [EF].[BaseItemHoldTimes]( [ID] [int] IDENTITY(1,1) NOT NULL, [BaseItemID] [nvarchar](14) NULL, [HoldState] [nvarchar](20) NULL, [HoldTimeDuration] [int] NOT NULL, [HoldTimeUnitOfTime] [nvarchar](14) NULL, [HoldTimeTotalMinutes] [int] NOT NULL, [OffsetDuration] [int] NOT NULL, [OffsetUnitOfTime] [nvarchar](14) NULL, [OffsetTotalMinutes] [int] NOT NULL, CONSTRAINT [PK_EF.BaseItemHoldTimes] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Table Data Example

View 1	Comments
XDEMOLIB. RPT_BaseItemHoldTimes	View on BaseItemHoldTimes to reformat for BI friendly reporting. Only displays the converted duration in base minutes.
Table Script CREATE VIEW [XDEMOLIB].[RPT_BaseItemHoldTimes] AS SELECT BaseItemID, HoldState, HoldTimeTotalMinutes AS HoldTimeMinutes, OffsetTotalMinutes AS OffsetMinutes FROM EF.BaseItemHoldTimes GO
View Data Example

View 2	Comments
XDEMOLIB. RPT_TagHoldStateDetailBase	View on TagHoldState to reformat for BI friendly reporting. Fills in EndTIme Nulls with current date and calculates HoldTimeTicks Nulls based on start/stop datetime
Table Script CREATE VIEW [XDEMOLIB].[RPT_TagHoldStateDetailBase] AS SELECT ID, TagID, HoldState, InHoldState, HoldProcessed, StartTime, ISNULL(EndTime, GETDATE()) AS EndTime, LocationLevel1, LocationLevel2, LocationLevel3, LocationLevel4, LocationLevel5, LotNumber, LotStatus, ISNULL(HoldTimeTicks, CAST(DATEDIFF(SECOND, StartTime, GETDATE()) AS bigint) * 10000000) AS HoldTimeTicks FROM EF.TagHoldState
View Data Example ID TagID HoldState InHoldState HoldProcessed StartTime EndTime LocationLevel1 LocationLevel2 LocationLevel3 LocationLevel4 LocationLevel5 LotNumber LotStatus HoldTimeTicks 129 FC0000000000000000003000 P 0 1 2020-03-16 09:28:01.440 2020-03-16 09:36:44.000 LTC B132 FLR_2 PRINTED D119730 5225600000 130 FC0000000000000000003001 P 0 1 2020-03-16 09:28:01.503 2020-03-16 09:36:45.000 LTC B132 FLR_2 PRINTED D119730 5234970000 131 FC0000000000000000003010 P 0 1 2020-03-16 09:28:01.550 2020-03-16 09:36:51.000 LTC B132 FLR_2 PRINTED D119731 5294500000 132 FC0000000000000000003011 P 0 1 2020-03-16 09:28:01.600 2020-03-16 09:36:54.000 LTC B132 FLR_2 PRINTED D119731 5324000000 133 FC0000000000000000003020 P 1 0 2020-03-16 09:28:01.660 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D119732 349249030000000 134 FC0000000000000000003021 P 1 0 2020-03-16 09:28:01.723 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D119732 349249030000000 135 FC0000000000000000003030 P 1 0 2020-03-16 09:28:01.787 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D119733 349249030000000 136 FC0000000000000000003031 P 1 0 2020-03-16 09:28:01.833 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D119733 349249030000000 137 FC0000000000000000003040 P 1 0 2020-03-16 09:28:01.897 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D125005 349249030000000 138 FC0000000000000000003041 P 1 0 2020-03-16 09:28:01.960 2021-04-24 14:49:44.890 LTC B132 FLR_2 PRINTED D125005 349249030000000

Comments

XDEMOLIB.

RPT_TagHoldStateDetail

View on TagHoldStateDetailBase to reformat for BI friendly reporting bringing in Base Item Number, filtering on current inventory and calculating HoldTime Minutes and Hours based from ticks.

Table Script

CREATE VIEW [XDEMOLIB].[RPT_TagHoldStateDetail] AS

SELECT

TagID,

UCCITMGTN AS BaseItemID,

HoldState,

StartTime,

EndTime,

LocationLevel1,

LocationLevel2,

LocationLevel3,

LocationLevel4,

LocationLevel5,

LotNumber,

LotStatus,

HoldTimeTicks,

CAST(ROUND((HoldTimeTicks / 600000000.0), 2) AS numeric(36,2)) AS HoldTimeMinutes,

CAST(ROUND((HoldTimeTicks / 36000000000.0), 2) AS numeric(36,2)) AS HoldTimeHours

FROM XDEMOLIB.RPT_TagHoldStateDetailBase

JOIN XDEMOLIB.RFDLOCIN ON RFDTAGUID = TagID

JOIN XDEMOLIB.UCCRCIM ON UCCITMGTN = RFDITEMID

View Data Example

TagID BaseItemID HoldState StartTime EndTime LocationLevel1 LocationLevel2 LocationLevel3 LocationLevel4 LocationLevel5 LotNumber LotStatus HoldTimeTicks HoldTimeMinutes HoldTimeHours

FC0000000000000000003000 P29 R 2020-03-16 09:49:29.000 2020-03-16 09:49:42.000 LTC B132 FLR_2 CHILL_ROOM D119730 130000000 0.22 0.00

FC0000000000000000003000 P29 A 2020-03-16 09:49:18.000 2020-03-16 09:49:29.000 LTC B132 FLR_1 INTRANSIT D119730 110000000 0.18 0.00

FC0000000000000000003000 P29 F 2020-03-16 09:40:00.000 2020-03-16 09:49:18.000 LTC B132 FLR_1 FREEZER_01 D119730 5580000000 9.30 0.16

FC0000000000000000003000 P29 A 2020-03-16 09:39:04.000 2020-03-16 09:40:00.000 LTC B132 FLR_1 INTRANSIT D119730 560000000 0.93 0.02

FC0000000000000000003000 P29 R 2020-03-16 09:36:44.000 2020-03-16 09:39:04.000 LTC B132 FLR_2 CHILL_ROOM D119730 1400000000 2.33 0.04

FC0000000000000000003000 P29 P 2020-03-16 09:28:01.440 2020-03-16 09:36:44.000 LTC B132 FLR_2 PRINTED D119730 5225600000 8.71 0.15

FC0000000000000000003001 P29 A 2020-03-16 09:49:19.000 2020-03-16 09:49:31.000 LTC B132 FLR_1 INTRANSIT D119730 120000000 0.20 0.00

FC0000000000000000003001 P29 F 2020-03-16 09:41:50.000 2020-03-16 09:49:19.000 LTC B132 FLR_1 FREEZER_01 D119730 4490000000 7.48 0.12

FC0000000000000000003001 P29 A 2020-03-16 09:39:06.000 2020-03-16 09:41:50.000 LTC B132 FLR_1 INTRANSIT D119730 1640000000 2.73 0.05

FC0000000000000000003001 P29 R 2020-03-16 09:36:45.000 2020-03-16 09:39:06.000 LTC B132 FLR_2 CHILL_ROOM D119730 1410000000 2.35 0.04

Izenda Report Examples

Container Lots by Location with Hold times (Pivot for Sum)

Hold Time Details Final

View 4	Comments
XDEMOLIB. RPT_TagHoldStateSummaryBase	View on TagHoldStateDetail to summarize for BI friendly reporting. Summarizes hold times per TagID/BaseItem/State for Allowable/consumed/Remaining.
Table Script CREATE VIEW [XDEMOLIB].[RPT_TagHoldStateSummaryBase] AS SELECT TagID, BaseItemID, HoldState, SUM(HoldTimeTicks) AS TotalHoldTimeTicks, SUM(HoldTimeMinutes) AS TotalHoldTimeMinutes, SUM(HoldTimeHours) AS TotalHoldTimeHours FROM XDEMOLIB.RPT_TagHoldStateDetail GROUP BY TagID, BaseItemID, HoldState
View Data Example TagID BaseItemID HoldState TotalHoldTimeTicks TotalHoldTimeMinutes TotalHoldTimeHours FC0000000000000000003000 P29 A 670000000 1.11 0.02 FC0000000000000000003000 P29 F 5580000000 9.30 0.16 FC0000000000000000003000 P29 P 5225600000 8.71 0.15 FC0000000000000000003000 P29 R 1530000000 2.55 0.04 FC0000000000000000003001 P29 A 1760000000 2.93 0.05 FC0000000000000000003001 P29 F 4490000000 7.48 0.12 FC0000000000000000003001 P29 P 5234970000 8.72 0.15 FC0000000000000000003001 P29 R 1560000000 2.60 0.04 FC0000000000000000003010 P29 A 1770000000 2.95 0.05 FC0000000000000000003010 P29 F 4480000000 7.47 0.12

Details

XDEMOLIB.

RPT_TagHoldStateSummary

View on TagHoldStateSummaryBase to further refine for BI friendly reporting. Joins on BaseHoldTimes and calculates remaining hours. Refer to IZENDA report Container Lots by Hold time Details Noble

Table Script

CREATE VIEW [XDEMOLIB].[RPT_TagHoldStateSummary] AS

SELECT

TagID,

RPT_TagHoldStateSummaryBase.BaseItemID,

RPT_TagHoldStateSummaryBase.HoldState,

CAST(ROUND(HoldTimeMinutes / 60.0, 2) AS numeric(36,2)) AS AllowableHoldTimeHours,

CAST(ROUND(TotalHoldTimeMinutes / 60.0, 2) AS numeric(36,2)) AS TotalHoldTimeHours,

CAST(ROUND(((HoldTimeMinutes / 60.0) - (TotalHoldTimeMinutes / 60.0)), 2) AS numeric(36,2)) AS RemainingHoldTimeHours

FROM XDEMOLIB.RPT_TagHoldStateSummaryBase

JOIN XDEMOLIB.BaseItemHoldTimes

ON BaseItemHoldTimes.BaseItemID = RPT_TagHoldStateSummaryBase.BaseItemID

AND BaseItemHoldTimes.HoldState = RPT_TagHoldStateSummaryBase.HoldState

View Data Example

TagID BaseItemID HoldState AllowableHoldTimeHours TotalHoldTimeHours RemainingHoldTimeHours

FC0000000000000000003000 P29 A 0.08 0.02 0.06

FC0000000000000000003000 P29 F 0.25 0.16 0.10

FC0000000000000000003000 P29 P 2400.00 0.15 2399.85

FC0000000000000000003000 P29 R 0.17 0.04 0.12

FC0000000000000000003001 P29 A 0.08 0.05 0.03

FC0000000000000000003001 P29 F 0.25 0.12 0.13

FC0000000000000000003001 P29 P 2400.00 0.15 2399.85

FC0000000000000000003001 P29 R 0.17 0.04 0.12

FC0000000000000000003010 P29 A 0.08 0.05 0.03

FC0000000000000000003010 P29 F 0.25 0.12 0.13

Izenda Report Example

Container Lots by Hold time Details Noble

Container Lots by Hold time Details VIEWS3 (sorted by last transaction time)