Get Allocated Inventory Items in Microsoft Great Plains

Below is the SQL query to get al the allocated inventory, copy and paste
the below code in sql browser on the GP company database. Modify accordingly
for the result.

PRINT 'This is the allocated quantity in the SOP10200 for Orders'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200
where ITEMNMBR = 'A' and ATYALLOC > 0 and SOPTYPE = 2

PRINT 'This is the allocated quantity in the SOP10200 for Invoices'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200
where ITEMNMBR = 'A' and ATYALLOC > 0 and SOPTYPE = 3

PRINT 'This is the allocated quantity in the IV10001 for a decrease Adjustment'
select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001
where ITEMNMBR = 'A' and TRXQTY < 0 PRINT 'This is the allocated quantity in the IV10001 for Transfers' select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001 where ITEMNMBR = 'A' and TRXQTY > 0 and IVDOCTYP = 3

PRINT 'This is the allocated quantity in the IVC10101 for saved Invoices'
select INVCNMBR,ITEMNMBR,QUANTITY from IVC10101
where ITEMNMBR = 'A' and DOCTYPE = 1

PRINT 'This is the allocated quantity in the BM10300 '
select TRX_ID,ITEMNMBR,ATYALLOC from BM10300
where Component_ID <> 0 and ITEMNMBR = 'A'
and ATYALLOC > 0

PRINT 'This is the allocated quantity in the POP10500 for POP Returns'
select POPRCTNM,QTYRESERVED from POP10500
where QTYRESERVED > 0 and Status = 0 and ITEMNMBR = 'A'

Dex.Ini Settings to Make Dynamics GP More Friendly

DEX.INI: The Microsoft Dynamics GP defaults files which contain setup and operating information. Each setting contains information such as where files are located and whether certain functions are performed. It is stored on each computer where the Microsoft Dynamics GP application files are installed. The information in the file affects only the computer where the file is located. This file is also essential for Microsoft Dynamics GP to run correctly, so we have to be very careful when editing it. But at the same time, it can be a good advantage to explore its possibilities.

Below are some settings which can be used for troubleshooting Microsoft Dynamics GP problems or making work in Dynamics GP more comfortable. Some of them are default settings, but some of them are optional.

• SQLLogSQLStmt=TRUE
  SQLLogODBCMessages=TRUE
  SQLLogAllODBCMessages=TRUE - Turn on all of these 3 settings and information is going to be appended to the end of the existing log DEXSQL.LOG file (or a new file is going to be created)
• Synchronize = TRUE - Forces a synchronization of the chart of accounts format
• OLEPath=\\server\folder\ole - Sets the path for linked and embedded files. This is often set up inconsistently in the initial setup. You'll need to include your path in place of the file:////server/folder/ole
• SampleDateMsg=FALSE - Prevents the sample company dialog box from being displayed, but the date is still set on 04/12/2017
• AutoInstallChunks=TRUE - Add CNK files without the "Add New Code?" dialogue after logging into Dynamics GP
• WindowMax=TRUE - Open the Dynamics GP main window full screen
• ScriptDebugger=TRUE - Turn Script Debugger Features on in Runtime Mode

There are also some other settings (which I haven’t used or checked), but maybe this list can help somebody else because I have found them to be very useful:

• SuppressChangeDateDialog=TRUE - Turns off the Date Change dialogue that opens at midnight
• NoPrintDialogs=TRUE - Suppress the print dialogue box
• ShowResids=TRUE - Make resource ID's visible in Dexterity
• ShowAdvancedMacroMenu=TRUE - Turn on the Advanced Macro Menu
• C:\DPS1\DEX.INI DPSInstance=1
  C:\DPS2\DEX.INI DPSInstance=2
  C:\DPS3\DEX.INI DPSInstance=3 - Run multiple process servers on a single machine. Specifics on CustomerSource here. (One line for each process server)
• ExportOneLineBody=TRUE - Eliminate line wrapping when exporting reports to comma or tab delimited files.
• IMPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IM.EXE IMExecPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IMRun.EXE - Set the path to Integration Manager so it will launch properly from the menu. (2 lines)
• SampleDateMMDDYYYY=00000000 - Prevents the sample company dialogue box from displaying and sets the date to the current date.
• SampleDateMMDDYYYY=MMDDYYYY - Prevents the sample company dialogue box from displaying and sets the sample company to the date defined. (5/1/08 would be 050102008).

Adding Extender fields to a Great Plains Report

Adding an Extender Window field to a Great Plains report is a simple process of adding a calculated field to the report. Before you add a field to a report, you should make a note of the Window ID, the key field/s for the window and the number of the field that you want to add.

To add an Extender Window field to a Great Plains report:
1) Select Tools >> Customize >> Report Writer.
2) Select Great Plains as the Product and click on the OK button.
3) Click on the Reports button to open the Report Writer window.
4) Select the report that you want to modify from the Original Reports list.
5) Click on the Insert button to move the report from the Original Reports list to the Modified Reports list.
6) Select the Report in the Modified Reports list.
7) Click on the Open button to open the Report Definition window.
8) Click on the Layout window to open the Report Layout window.
9) If the Toolbox window is not already open, select Layout >> Toolbox to open it.
10) Select Calculated Fields from the Resources list and click on the New button to open the Calculated Field Definition window.
11) Enter a Name for the calculated field.
12) Select the Result Type as String.
13) Click on the Functions tab and select the User-Defined option.
14) Select System from the Core list and rw_TableHeaderString from the Function list.
      Note
          The rw_TableHeaderString function has 5 parameters and returns string value:
          Product ID, Window ID, String Key Value, Numeric Key Value, and Field Number
15) Click on the Add button to add the function to the expression string.
16) Click on the Constants tab and select Integer as the Type.
17) Enter 3107 as the Constant value and click on the Add button to add it to the expression string. 3107 is the Product ID for Extender.
18) Select String as the Type.
19) Enter the Window ID for the Extender Window as the Constant value and click on the Add button to add it to the expression string.
20) If the key field for the window is a numeric field, clear the Constant value and click on the Add button to add a blank string to the calculation.
21) Click on the Fields button and select the Table that contains the Key value for the window. Select the Field that contains the Key value. Click on
      the Add button to
22) Click on the Constants tab and select Integer as the type.
23) If the key field for the window is a string field, clear the Value field and click on the Add button to add a zero to the calculation.
24) Enter the number of the field in to the Value field and click on the Add button.
25) Click on the OK button to save the calculated field.
26) Drag the calculated field on to the report.
27) Close the Report Layout window. Click on the Save button.
28) Click on the OK button to close the Report Definition window.
29) Select File >> Microsoft Business Solutions – Great Plains to return to Great Plains.
30) You will need to set security to the modified report before you will be able to view. Refer to the Great Plains manual or help file for more information on setting security to modified reports.

For more information about how to create an Extender calculated field, click the following article number 898983 to view the article 898983 in the Microsoft Knowledge Base: 898983 How to modify the SOP Blank Order Form report to include information from an Extender window for the Sales Transaction Entry window in Microsoft Dynamics GP and in Microsoft Great Plains

Listing of Microsoft Great Plains tables used by Microsoft FRx

FINANCE tables:
1. GL00100 Account Master
2. GL10110 Account Current Summary Master
3. GL10000 Transaction Work
4. GL10001 Transaction Amounts Work
5. GL00200 Budget Master
6. GL00201 Budget Summary Master
7. GL30000 Account Transaction History
8. GL40200 Segment Description Master
9. GL20000 Year-To-Date Transaction Open
10. GL30001 Account Summary History
11. DTA00100 Analysis Group Master
12. DTA00200 Analysis Code Master
13. MC40000 Multicurrency Setup
COMPANY tables:
1. SY40100 Period Setup
2. SY40101 Period Header
3. SY00300 Account Format Setup
SYSTEM tables:
1. SY00302 Account Definition
2. SY003001 Account Definition Header Dynamics
Tables that are directly read when generating FRx Financial reports are as follows:
1. DTA10200 Transaction Analysis Codes
2. GL10110 Account Current Summary Master
3. GL10111 Account Summary History
4. GL20000 Year-To-Date Transaction Open
5. GL30000 Account Transaction History
6. MC00201 Multicurrency Summary Master
7. MC30001 Mulitcurrency Account Summary Master