There seems to be phantom records for job attachments in our database. Is this an Epicor bug where there were attachments at some point but they got removed yet they remain in the database?
This seems to happen on a few different jobs.
SELECT xfr.*, xfa.*
FROM [Ice].XFileAttch AS xfa WITH (NOLOCK)
INNER JOIN [Ice].XFileRef AS xfr WITH (NOLOCK)
ON (xfa.Company = xfr.Company AND
xfa.XFileRefNum = xfr.XFileRefNum)
WHERE xfa.RelatedToFile IN('JobHead', 'JobAsmbl', 'JobMtl')
AND xfa.Company = 'SD'
AND xfa.Key1 = '367622'
ORDER BY xfa.Key1, xfa.Key2
Does anyone know how they are created?
Where does job entry pull its attachments from if it’s not these two tables?
Is there an Epicor clean-up script to fix these?
Looking at a job with an attachment I see the following table being referenced: AttachHed
Could that table be aliased by something in Epicor? I can’t find it in ERP, ICE, or IM table section. It simply doesn’t exist.
Does anyone know where this table resides and what its true name could be?
I think I figured it out. This appears to be buggy behavior from Epicor.
If you delete an assembly the attachments might not be removed from the XFileAttach, XFileRef tables, they remain in the database, essentially orphaned. Epicor devs could probably update their BO on delete method to take care of that, but maybe they don’t think it’s important.
To get around that, when looking for legitimate job attachments use this query to make sure the assembly still exists:
SELECT xfr.*, xfa.*
FROM [Ice].XFileAttch AS xfa WITH (NOLOCK)
INNER JOIN [Ice].XFileRef AS xfr WITH (NOLOCK)
ON (xfa.Company = xfr.Company AND
xfa.XFileRefNum = xfr.XFileRefNum)
WHERE xfa.Company = 'SD'
AND xfa.Key1 = '367622' -- xfa.Key1 is jobnum
AND (
(xfa.RelatedToFile = 'JobHead')
OR
-- If there is a JobAsmbl/JobMtl file record make sure the assembly still exists
(xfa.RelatedToFile IN ('JobAsmbl', 'JobMtl')
AND
EXISTS(
SELECT ja.Company
FROM [Erp].JobAsmbl AS ja WITH (NOLOCK)
WHERE ja.Company = xfa.Company
AND ja.JobNum = xfa.Key1
AND ja.AssemblySeq = xfa.Key2)
)
)
ORDER BY xfa.Key1, xfa.Key2
Where in the E10 application do these orphaned attachment records cause issues?
Or do they only appear when you go looking for them?
Any BAQ or Report would ignore them as their keys don’t completely relate to the tables they are Related To. No report or BAQ should show any of those records since there is no matching JobAsmbl record.
Maybe inside the application they don’t show up.
Auto printing attachments at the click of a button doesn’t exist in Epicor. If I were to write something external to Epicor to query these attachment tables to print the attachments out automatically I could save our users countless hours.
When querying the database for attachments for a given job it gave me a bunch of these orphan records so that’s bad for trying to auto print stuff that doesn’t exist.
Really it’s just poor record keeping by the business object and/or poor database setup to not remove them automatically if the foreign key is gone.
I recommend Epicor either set the business object up to delete those attachment records or setup proper SQL ON DELETE CASCADE rules on those tables.
But that takes dev time and I can see why they thought nobody will ever know the shortcut they took with that.
I agree that it’s poor record keeping, but had you made the joins between the field for the AsmSeq and an existing JobAsm(?) record, they’d have never shown.
did you see this information on jobs that are created by MRP we are currently seeing this type of behavior when we use an access database to pull the jobs drawings from epicor. I will need to test in 10.2.400 but im seeing this in 10.2.300.9
I have an open ticket with Epicor but I am unable to duplicate this every time. Can you provide me some steps and or if you have a Epicor ticket created that I can reference as well with more information to add to my ticket.
This is the support case number we had: CS0001326162
They offered to write a fix program for ones that were already orphaned, but not fix the actual problem.
This was my resolution comment in the case:
Don’t worry about a fix program then. It’s cumbersome & time consuming to reproduce this problem, just know that it’s a potential problem with Epicor’s record keeping. I got around it by also linking on job assemblies to make sure they exist on the job. Maybe just let the devs know that the BO or that the table’s foreign key restraints might not be working correctly.
It’s not trivial to do attachments correctly and it would take some dev time to get it right. Time I don’t think they want to spare.
thanks for the information. we have done the same thing. Currently we are linking to the partrev to get the correct drawings. I will close this ticket as well.
