Last year I tripped across an odd problem in the Epicor 10 BPM Builder. We were trying to create a BPM that would check what Bin Attributes were assigned to a Bin during a Transfer transaction so that we could default an appropriate bin, however when we tried to create a Query we could not find the WhseBinAttr listed in the table list. So I then checked the BAQ Builder and it was missing from there too! I checked the Data Dictionary and it was listed, why would it not be available in the BAQ Builder? While I opened a call with Epicor Support we worked around it by querying the database with C# Custom Code widget in the BPM. While Epicor Support was processing my call I did some digging around in the DB with SSMS and found the cause of the tables absence. There are two sets of 'ICE' schema tables: -Ice.TableAttribute -Ice.ColumnAttribute These tables contain attribute data for each table & column listed in the Data Dictionary, I believe these tables are also referenced by various Business Objects for data validation and such as well. However not all the tables & fields listed in these tables show up in the BAQ builder, plus the WhseBinAttr is included in these tables, so there had to be another data source for the BAQ & BPM Query Builder. After a bit more looking around I found the tables that seemed to be responsible for the BAQ & BPM Query Builder data: -Ice.ZDataTable -Ice.ZDataField The WhseBinAttr table & columns were NOT listed in these tables. So to confirm this, as best as I could I manually created a record in the Ice.ZDataTable table (in my test DB of course) based on data values of the Ice.TableAttribute record and took some guesses on the other fields. When I checked the BAQ Builder the table now showed up! I updated my Epicor Support call with this information and suggested that a SQL Data Fix (INSERT Query) could fix this issue. The response was weak, a SQL Data Fix would not be issued and SCR 119161 was created for the issue to be fixed in a future version. With our migration to another ERP system that could use the Bin Attribute data I needed to be able to get to that data with a BAQ so I attempted to construct my own SQL Data Fix with my limited understanding of SQL and lots of testing. The following SQL script was the result and seemed to work great in my Test & Development environments so I ran the SQL script on my Live DB and so far, no issues:
The sad thing is that this only took me about 4 hours to hack together, and I wouldn't say I'm at all an SQL pro. It really wouldn't have taken a seasoned Epicor SQL developer any longer. So if you have encountered this data bug in Epicor 10, this SQL script may be helpful to you in correcting the issue.
--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;