Ice.XFileAttch and Ice.XFileAttch_UD

Epicor ERP 10
1

Hi,

Can anyone else confirm if they have a column called ForeignSysRowID in both of these tables?

2

I don’t have an extended table for XFileAttch as i dont need to store extra data, but essentially the ForeignSysRowID in the _UD points to the main table SysRowID, the ForeignSysRowID in the XFileAttch table points to the SysRowID of the table record with the associated file attachement.

Pasted image749×320 26.2 KB

1 Like
3

That poses a problem then, because when you add an extended UD table, the primary key for any _UD table is the ForeignSysRowID column. So, when these upgrade scripts are creating SQL Views for the extended UD tables, there is an issue with two columns being named the same thing.

I modified the stored procedure (Ice.SPCreateTriggerForUDCreateView) as a work around.

    -- IF STATEMENT NEEDED TO FIX BUG
    IF @FullUDTable = 'XFileAttch_UD'
    BEGIN
        SET @str = 'CREATE VIEW [' + @TableName + '] AS' + @NewLineChar
                    + '   SELECT a.*, b.Date01, b.ForeignSysRowID AS UDForeignSysRowID, b.UD_SysRevID FROM ' + @FullTable + ' a' + @NewLineChar    
                    + '   LEFT OUTER JOIN ' + @FullUDTable + ' b ' + @NewLineChar
                    + '   ON a.SysRowID = b.ForeignSysRowID'
    END
    ELSE
    BEGIN
        SET @str = 'CREATE VIEW [' + @TableName + '] AS' + @NewLineChar
                    + '   SELECT a.*, b.* FROM ' + @FullTable + ' a' + @NewLineChar    
                    + '   LEFT OUTER JOIN ' + @FullUDTable + ' b ' + @NewLineChar
                    + '   ON a.SysRowID = b.ForeignSysRowID'
    END
4

Not on 9.05.702A.

Brad

5

I put in a ticket with Epicor, this is a bug. See response below. I’m not sure how it is invasive, if the issue is with just creating a view, and the code I posted does work around this issue.