Microsoft Dynamics GP 10.0 AR Trial Balance – Data Repair recommendation to SQL programmer

For Great Plains there is very popular data support routine, where you as GP developer or programmer have to fix failing batch posting or RM data transfer to history.  In Microsoft Great Plains you can try several techniques, first would be Check Links, which may potentially fix or at least direct you on where the problem would be.  Check Links is powerful technology, however it is in hands of GP Dexterity developers, who tried to think through various scenarios of data to become inconsistent, but you can expect the life to give you additional data inconsistency surprises.  Let us give you orientation on SQL query design to update, fix or reconstruct failed data:

1. GP RM Open Tables:  RM20101 – this is open transactions, plus this one RM20201 – open apply to transactions table.  Most of the RM posted transactions are repaired in these two tables, assuming that you are not moving Open transactions to the history on the weekly or monthly basis.  If you, however move transactions to the history, the next paragraph orients you on the historical tables
2. GP RM Historical Tables: RM30101 – receivables management transactions history and RM30201 – apply to receivables transactions history file.  Please be aware from the beginning that RM historical data fixing is a way more complex than the one for open tables, due to the fact, that you can not void historical RM transactions in GP workstation interface.  All you really can is to remove historical transaction via SQL scripting delete statement or update applied amount via update statement
3. Check Links.  For those who are not familiar with this routine, please in GP workstation go to Microsoft Dynamics GP->Maintenance->Check Links, select Sales series and include Receivables Open Transaction Files, plus for the history Receivables Transaction History Files
4. Avoiding Data Damaging.  Before you do your surgery on the above mentioned tables, we recommend you to make table backup – use the following script as an examples: select * into RM20201_Backup from RM20201.  The rollback script includes these statements: alter table RM20201_Backup drop column dex_row_id and then you can delete specific records in RM20201 and insert them from backup table.  Again SQL update statement is very powerful and you can easily destroy your ERP records with one click of the mouse