Management Report Setup Important

After CU 11 you need to:
Go to Microsoft Dynamics GP > Tools > Setup > Company > Company.
Click on “Options” to open the “Company Setup Options” window There is a separate section for management reporter integration with Dynamics GP when the data mart provider is used. Make sure these are checked for that the data mart imports the company.

Note that you need to enable Change Tracking for all DBs and the Dynamics DB as well. 

Resetting the QuickBooks FIM Integration for Exact MAX

1) First, I would remove the integrated applications in QB (Edit--Preferences--Integrated applications--Company tab). Highlight the two and remove.
2) Next, I would delete the ExactRM user in QB
3) Then remove the QBFC 11 from Control Panel--Uninstall Program
4) Rename the ExactMAXQB.xml.
5) Make sure all users are out of MAX and QB.
6) Go to Finance--Financial Integration--Options--Integration Setup. Click on config editor button
7) Make a print screen of the current integration setup and then remove it.
8) Add a new integration. Make sure you select the correct version of QB.
9) Follow the wizard to complete the integration setup.

Clearing up Posting in Exact MAX

1. To clear up the Invoice Edit list - Set INVCE_27 to “Y” or …

a. Stop shipping

b. Have them post all sales orders that they want to actually go through

c. Turn off the integration with Great Plains

d. Post all remaining orders

e. Turn on the integration with Great Plains

f. Resume shipping

2. To remove all unposted PO receipts - Done

a. Using SQL, change the value of the MARK_55 field on all PO Receipt records with a transaction date prior to (date) and a value of ‘N’ to ‘P’

3. To close Completed Shop orders

a. Close all the shop orders

b. Stop all Max transactions

c. Run the From/To report selecting their standard options

d. Using SQL, go into the order master and change all shop orders with a status of ‘4’ to ‘5’

e. Rerun the From/To report selecting their standard options again

f. Allow Max transactions to continue

Upgrades form SQL 2005 to Later Versions for Exact MAX

When upgrading MAX databases from SQL 2005 be sure to change the legacy setting of TORN_PAGE to CHECKSUM in the PAGE_VERIFY setting of your MAX database. 

Use of Parameterization on SQL Database for Exact MAX

 

In some cases setting the database Parameterization setting may increase performance of MRP Explosion and other sizable batch processes. Testing this in your test environment is definitely recommended. Change this setting under options in your SQL database and benchmark the change in processing time.

Multi-Column Report for Serial Numbers

I recently had to create a report that needed adjacent columns of serial numbers much like columns in a Word document. I pored over several advices but in the end discretely defining the number of serial numbers per column worked out best.
I used the code below to get a row number and list them and then created separate data sets in the sub-report which was inserted in the body of the main report.

SQL Code for Data Sets
select s1.Num as R1, s1.SERIAL_71 as S1
from
(
SELECT top 100 percent SERIAL_71, ordnum_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num <= 10
select s1.Num as R2, s1.SERIAL_71 as S2
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num >= 11 and s1.Num <= 20
select s1.Num as R3, s1.SERIAL_71 as S3
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @OrdNum)
order by SERIAL_71
) AS S1
where s1.Num >= 21 and s1.Num <= 30

Linking the Order Master to the Sales Master Table in ExactMAX for AutoGenerated Master Scheduled Orders

 

SELECT Order_Master.ORDNUM_10, CM.NAME_23, CM.COMNT1_23, CM.COMNT2_23, CM.UDFREF_23
FROM
Order_Master
INNER JOIN
SO_Master SM
on LEFT(CUSORD_10,8) = SM.ORDNUM_27
inner join Customer_Master CM on SM.CUSTID_27 = CM.CUSTID_23
WHERE – Add anything here that you need to select the proper record set.