Wednesday, April 1, 2015

Clearing Out Old Uninterfaced Fixed Assets GL Activity

It's not unusual for Fixed Assets to get implemented after the initial project for Dynamics GP.  But lately, I have had a few different clients who went live with Fixed Assets but never actually went live.  What do I mean? They started using the module, but for whatever reason (e.g., lack of confidence in the GL accounts used, issues with how items were calculating, not understanding the process) they did not use the GL interface to pass the journal entries from Fixed Assets to the General Ledger.






Of course, if there are issues with the accounts being used, or the calculations, or understanding the process, those need to be addressed. But once they are, what do you do with all of that uninterfaced activity (considering it might be using incorrect accounts)?  Now, if you are lucky-- their won't be much of it and you can just run the GL interface (Routines-Fixed Assets-GL Posting) and delete the resulting batch.  But as luck would have it, I have had a couple instances where there was SO MUCH ACTIVITY that it was taking hours and hours and hours to for the interface to run.  So at the worst, it was locking up the machine and at the best, it was annoying to have to deal with.


So what to do?  Well, let's just update those records in the database so that they think they were previously interfaced.  This approach is surprisingly easy because there is just one table involved, FA00902.






If you do a select on that table, you will see that it contains all of the GL activity records and it has columns for GL information.


  • INTERFACEGL stores a 1 if the record is to be interfaced to the GL
  • GLINTTRXDATE, GLINTDATESTAMP both store 1/1/1900 until the record has been interfaced to the GL, and then these dates are updated
  • GLINTBTCHNUM stores the batch name (FATRX000...) created in the GL
So, with these fields in mind, the following script would update and set the records as interfaced.  Now, keep in mind, you might want further restrictions in your WHERE clause on the FAYEAR or FAPERIOD which are also in the table.




--Confirm records to be updated
SELECT * FROM FA00902 WHERE INTERFACEGL=1 AND GLINTBTCHNUM=' '




--Mark all records as interfaced to GL
UPDATE FA00902 SET GLINTRXDATE='whatever date you want', GLINTDATESTAMP='whatever date you want', GLINTBTCHNUM='CLEAROUT' WHERE INTERFACEGL=1 and GLINTBTCHNUM=' '


As always, please make sure that you have a backup and use the script above to first validate what will be updated before actually doing the update. Happy updating!


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.







No comments: