As with any software, there are bound to be minor issues that crop up from time to time. One such issue with Sage 500 ERP Data Import Manager is that it splits imported AR invoices into multiple batches. While this is by design, the maximum counts are hard-coded into the import stored procedure which can cause some headaches for users. However, fear not! We have found a solution to this problem and have successfully changed the setting for a few customers without any issues. It's such a simple change that we thought we would share it with the wider community to make everyone's lives a little bit easier.
In SQL, there is a stored procedure called spARapiPendingInvcIns. If you modify this stored procedure in SQL, you can search for the @MAX_ROWS_PER_RUN variable and find the section of code that dictates how many batches to make based on the total number of records. You can comment out the case statement and just select the @MAX_ROWS_PER_RUN to be 2500. Keep in mind this code was put in the system on purpose, so you may have performance issues depending on the number of invoices and line items you import at one time.
Change:
-- Set up @MAX_ROWS_PER_RUN based on the TotalRecs
SELECT @MAX_ROWS_PER_RUN =
CASE WHEN @oTotalRecs <= 250 THEN 25
WHEN @oTotalRecs <= 500 THEN 50
WHEN @oTotalRecs <= 2500 THEN 250
WHEN @oTotalRecs <= 10000 THEN 500
WHEN @oTotalRecs <= 50000 THEN 1000
ELSE 2500
END
To:
-- Set up @MAX_ROWS_PER_RUN based on the TotalRecs
SELECT @MAX_ROWS_PER_RUN = 2500
--CASE WHEN @oTotalRecs <= 250 THEN 25
--WHEN @oTotalRecs <= 500 THEN 50
--WHEN @oTotalRecs <= 2500 THEN 250
--WHEN @oTotalRecs <= 10000 THEN 500
--WHEN @oTotalRecs <= 50000 THEN 1000
--ELSE 2500
--END
