How to correct Dynamics GP Errors Lab Exercises

Matt Mason mmason@manersolutions.com

Fixing GP Errors Lab 

Exercise 1 

 

Running Check Links 

Screen:  (From Toolbar).  Microsoft Dynamics GP – Maintenance – Check links. 

Choose Purchasing Series.  Click All button.   

Click OK.  BE SURE TO PRINT ERROR LOG TO SCREEN. 

 

 

 

Minimize Dynamics GP for later use. 

Fixing GP Errors Lab 

Exercise 2 

 

Running Reconciles 

Purpose: Matches summary records to the total of transaction details in specific places in GP. 

Screen:  Sales – Utilities – Reconcile 

 

Choose Current Customer Information.  Click Process.  Print Output to screen. 

 

 

Fixing GP Errors Lab 

Exercise 3 

 

Backing up GP company 

This is done by accessing SQL Management Studio 

Upon launching, you’ll need to access the SQL server by using ‘sa’ login. 

Password is access 

Click Connect button. 

 

 

 

Goal:  to back up the TWO database (Fabrikam, Inc.) 

On left side, click the “+” next to Databases folder in the Object Explorer panel. 

Right click on the TWO database.  Then click Tasks.  Then click Back Up 

 

 

 

You will see the following screen: 

 

Click Remove button to get rid of latest backup. 

Click Add 

Click Elipsis button (the one with 3 dots). 

  

Click the “+” next to TWO folder.  Then, click ON the TWO folder. 

In File Name field, type TWO.bak.  Then click OK. 

Click OK 2 more times. 

You’re backing up.  When you’re done, you’ll see the following message: 

 

Click OK. 

Minimize SQL Management Studio for later use. 

 

 

Fixing GP Errors Lab 

Exercise 4 

 

Clearing Stuck Batches 

Bring up minimized SQL Management Studio 

Click New Query on top left of window 

 

 

Change database from master to TWO 

 

 

In the big white space, type in the following: 

SELECT * FROM SY00500 

   

Click the Execute button above the white space. 

Results of this Query will appear at the bottom of the screen 

 

 

 

Search BCHSTTUS column for batches with status other that 0.  There should be 2 batches:  PETTY AND 

UPTOWN TRUST 

Change Query Statement to: 

SELECT BCHSTTUS, * FROM SY00500 WHERE BACHNUMB = ‘PETTY’ 

Click Execute button. 

You will see only one record now. 

   

 

 

Change Query Statement to: 

UPDATE SY00500  

SET BCHSTTUS = 0 WHERE BACHNUMB = ‘PETTY’ 

Click Execute button. 

You should see one row affected. 

 

 

Type in the query statement again 

SELECT BCHSTTUS, * FROM SY00500 WHERE BACHNUMB = ‘PETTY’ 

You should see that BCHSTTUS = 0, which means the batch is available 

 

 

 

Fixing GP Errors Lab 

Exercise 5 

 

Deleting and Rebuilding the PM Key file 

Step 1‐Delete:  Do this in SQL Management Studio 

Click New Query button 

 

 

Type the following Query Statement: 

SELECT  * FROM PM00400 

Click the Execute button. 

Examine results at bottom on window.  There should be a lot of records.  There should be Control 

Numbers and Vendor ID’s. 

 

 

 

CHANGE (don’t type new statement) EXISTING STATEMENT TO: 

DELETE PM00400 

Click the Execute button. 

You should see lots of rows affected. 

 

 

Step 1‐Delete:  Do this in Dynamics GP 

Bring up Dynamics GP. 

Run check links just like in Exercise 1, EXCEPT ONLY CHOOSE TWO FILES: 

  Payables History Logical Files   Payables Transaction Logical File  Screen:  (From Toolbar).  Microsoft Dynamics GP – Maintenance – Check links. 

Choose Purchasing Series.   

Choose Payables History Logical Files. Click Insert button.   Choose Payables Transaction Logical File. Click Insert button.   

 Click OK.  BE SURE TO PRINT ERROR LOG TO SCREEN. 

  You’ll see a record added for each line of Key file.  Don’t bother printing, just close.  

 

 

A Short Primer on Dynamics GP Tables

Each GP table name is 7 or 8 characters. The first 2 or 3 characters are letters; the last 5 are numbers. An example is: UPR30300 Microsoft SQL Every character (almost) means something. The letters denote which module they are a part of. Names and Modules

Table Name Module AF Advanced Financial (Dinosaur module) BM Bill of Materials CM Bank reconciliation (formerly Cash Management) GL General Ledger IV Inventory IVC Invoicing MC Multicurrency PM Payables Management – better known as Accounts Payable POP Purchase Order Processing RM Receivables Management - better known as Accounts Receivable SOP Sales Order Processing SY System Manager TX Taxes UPR United States Payroll (as opposed to Canadian Payroll) Others 3rd Party programs have their own file names The numbers denote what kind of table it is. The most important number is the first one…that tells what series it’s in. Numbers and Table Types

Number Type of Table 00’s Master (card) files

10000’s Work (unposted) files 20000’s Open files 30000’s History files 40000’s Setup files Other Don’t worry about them

So, the table UPR30100 is a payroll history table. If you want to know EXACTLY what kind of table it is, you can find this out in the Resource Table Descriptions inside Dynamics. Screen: Tools => Resource Descriptions => Tables

Click the next to the Table:

Product: Dynamics Series: Choose module View by: By Table Physical Name

In this case, you find that the UPR30100 is the Payroll Check History Table.

But it goes even farther…to find out what each field is in the table, double-click on the line. You will be able to see the Physical Field Name (very important for SQL Management Studio), the type of each field, and what the key fields are.

But there’s EVEN MORE…double-click on a particular field and you will see more information on that field, such as field size, and formatting. And finally…a list of common tables that you’ll find a lot of fields useful in SQL Reports.

Physical Name Display Name GL00100 Account Master (Chart of Accounts) GL00105 Account Index GL20000 GL Year-to-Date Transactions GL30000 GL Account Transaction History (prior years’ transactions) PM00200 Vendor Master (Vendor Maintenance) PM20000 A/P Open Transactions (open invoices) PM30200 A/P Paid Transactions (paid invoices and checks) RM00101 Customer Master (Customer Maintenance) RM20101 A/R Open Transactions (open invoices) RM30101 A/R Paid Transactions (paid invoices and checks) SOP30200 Sales Order Processing Transactions – Header file SOP30300 Sales Order Processing Transactions – Line file (Inventory items) UPR00100 Employee Master (Employee Maintenance) UPR00400 Pay Code Master (What everyone gets paid) UPR00500 Deduction Master UPR00600 Benefit Master UPR30100 Check History UPR30300 Transaction History IV00101 Inventory Item Master (Item Maintenance) IV30200 Inventory Transaction History – Header file IV30300 Inventory Transaction History – Line file IV30301 IVC30101 IVC30102

Inventory Transaction History – Line file Invoicing Transaction History – Header file Invoicing Transaction History – Line file