Without Epicor being willing to provide a data fix for this I cobbled together the following SQL to insert the missing records into the ZDataTable & ZDataField tables so I can use the Warehouse Bin Attribute table in BAQ’s & BPMs. Just in case someone else needs this the code is below, use at your own risk, test out fully on a Test environment first! …Remember, I am not an SQL pro, this is truly cobbled together, mostly trial and error.
--DO NOT JUST EXECUTE THE ENTIRE SQL STATEMENT, understand what this does first.
--Check to see if the WhseBinAttr record already exists or not
IF
(select count(*) from Ice.ZDataTable
where DataTableID = 'WhseBinattr') = 1
PRINT 'The WhseBinattr table record already exists in Ice.ZDataTable'
Else
BEGIN
Print 'The WhseBinattr table record is missing from Ice.ZDataTable, inserting from Ice.TableAttribute'
--IF no records are returned then run the following Insert
Insert into Ice.ZDataTable (SystemCode, DataTableID, Description, SchemaName, DBTableName)
select SchemaName, TableName, TableDesc, SchemaName, TableName
From Ice.TableAttribute
where TableName = 'whsebinattr'
--Correct record with update
update Ice.ZdataTable
set SystemCode = 'ERP', SystemFlag = 1
where DataTableID = 'WhseBinattr'
END;
--Check to see if the WhseBinattr field records exist
IF
(select count(*) from Ice.ZDataField
where DataTableID = 'WhseBinattr') > 5
PRINT 'The WhseBinattr field records already exists in Ice.ZDataField'
Else
--Insert records from Ice.ColumnAttribute table
BEGIN
Insert into Ice.ZDataField (SystemCode, DataTableID, FieldName, Seq, DBTableName, DBFieldname, DataType, Description, Included, SystemFlag)
select 'ERP',
TableName,
ColumnName,
ROW_Number() OVER(Order By Substring(Convert(nvarchar(36),SysRowID),5,1)),
TableName,
ColumnName,
(case
when ColumnName ='SysRevID' then 'timestamp'
when ColumnName = 'SysRowID' then 'uniqueidentifier'
when Format like '%x%' then 'nvarchar'
else '?'
end),
ColumnDesc,
1,
1
From Ice.ColumnAttribute
where TableName = 'whsebinattr'
--Set Like fields
UPDATE Ice.ZdataField
SET LikeDataFieldSystemCode = 'ERP', Required = 1, LikeDataFieldTableID = CASE FieldName
WHEN 'Company' THEN 'Company'
WHEN 'WarehouseCode' THEN 'Warehse'
WHEN 'BinNum' THEN 'WhseBin'
ELSE ''
END,
LikeDataFieldName = Case FieldName
WHEN 'Company' THEN 'Company'
WHEN 'WarehouseCode' THEN 'WarehouseCode'
WHEN 'BinNum' THEN 'BinNum'
ELSE ''
END
Where Ice.ZdataField.DataTableID = 'whsebinattr' and FieldName IN ('Company', 'WarehouseCode', 'BinNum')
--Show Results
select * from Ice.ZDataField
where DataTableID = 'WhseBinattr'
order by Seq
END;