Mohammad R. Daoud: 2008

Wednesday, December 31, 2008

Copying Reports in Report Writer

1. Export the SOP Invoice package file using Customization Maintenance.
2. Open the package file using Notepad.
3. Perform a Find/Replace, replacing all the references to 'WORK' tables with 'HIST' tables. (i.e., replace SOP_HDR_WORK with SOP_HDR_HIST)
4. Change the report name in the file.
5. Save the package file.
6. Import the modified package file back into GP using Customization Maintenance.
7. Voila! Your report is modified.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/
http://mohdaoud.blogspot.com/

Sunday, December 28, 2008

An Error Occurred while using the BCP utility-- data was not correctly copied to the server....

Vaidy Mohan has turn our attention to a very known error these days on Vista operating system related to BCP Utility error:

Normally we used to go to users in control panel, remove "UAC feature" checkbox and wait to restart PC..................

Beside that this cannot be agreed by some of the users, I personally also hate to wait! Vaidy suggested another solution, which is only right clicking on GP shortcut and run it as Administrator :)

Thank you Vaidy for this solution.

Monday, December 22, 2008

Support Debugging Tool Build 10 released

By David Musgrave

Some of you might know that I have been busy working on the next build of the Support Debugging Tool for Microsoft Dynamics GP. If you did not know... well... you do now.

I have a wish list of features based on feedback from colleagues and the partner community as well as my own ideas for enhancements. I have been slowly working my way through this list and even though there is still more work to be done, I wanted to give you the benefit of the work completed already.

So, we have released Build 10 of the tool on PartnerSource. The download links for the pages are below.

Here is a summary of the features and enhancements added:

ScreenShot
ScreenShot is a tool that will capture screen shots of all open windows in the Microsoft Dynamics GP system and email them along with a detailed System Summary Report and optional copies of the Dynamics.set and Dex.ini files.
Security Profiler
The Security Profiler window has been enhanced to provide more information about security issues. It can now be set to automatically open when an error occurs so that you can immediately see the cause. You can print the contents of the window or even better, export to file or email a log of the information. This log can be emailed to your administrator, so they can see the issues and the cause on their system.
Resource Information
The Resource Information window has been enhanced to provide better search capabilities with options for Exact Match, Begins With and Contains searching as well as turning off case sensitivity. A Search Again button has been added so that you can keep searching for resources that match your criteria in all available dictionaries.
Security Information
The new Security Information window can be opened using a right mouse click context sensitive menu or the Security button from the Security Profiler and Resource Information windows. This window will display a full break down of the security status for a selected resource for a specified user and company combination. It can then be used as a launch pad to open the security windows to make changes.
For v8.0 & v9.0: It will show the security settings for the user and the user's class.
For v10.0: It will show the Security Tasks and Roles that the user is assigned to that grant access to the resource and customisation selected from the Alternate/Modified Forms and Reports ID. It will also show the Security Tasks and Roles available on the system that are linked to the selected resource.

Administrator Settings
This new "Advanced Mode" window is used to set default settings for the tool, including the administrator's email address, whether ScreenShot adds the Dynamics.set and Dex.ini files as attachments and whether the Security Profiler window should open automatically when there are errors or warnings.

There have also been a number of minor tweaks to the code to improve functionality and performance. Please download this latest version and get it installed on all your customer sites.

Downloads

Support Debugging Tool for Microsoft Great Plains 8.0

Support Debugging Tool for Microsoft Dynamics GP 9.0

Support Debugging Tool for Microsoft Dynamics GP 10.0

Have a look at Mariano Gomez's great post with his take on the latest features added to Build 10.

Please post your feedback as comments, I would love to know how the new features work for you.

Checklist of Troubleshooting a GP Problem

By Jivtesh Singh

Developer and entrepreneur Leon came up with a Troubleshooting Checklist while developing an application.

Check it out at http://www.secretgeek.net/simple_checklist.asp

Leon's a funny guy - here are some of his Gems

blame the guy who left last week
disable the customer experience improvement program
what would Jesus do?
reboot 3 times
temporarily allow Popup's
remove and then recreate all Bluetooth partnerships

There are 164 items, enough to Keep you busy, if nothing else !

Inspired, here is my list for

Troubleshooting a Common GP Problem

All the items don't apply to all cases, looking at the problem you normally know which ones to look at first. I'll probably update this - but these were all the things I could think of at the top of my mind.

Note the exact error Message
Search Knowledgebase with exact search on
Search Microsoft GP Groups
Search your system using Google Desktop or Microsoft Desktop
Does the problem occur only on one machine ?
Gather the following information from the machine that has a problem
1. Generate a DexSQL Log and review it
2. Get a copy of Dex.ini and Dynamics.set
3. Check Version of Software (GP, FRx, IM, Biz Portal, 3rd Party, SQL)
4. Check Windows version and service pack
5. What 3rd party products are being used ?
6. Check the ODBC
7. Is the user using named printers ?
8. Does the logged in user have restricted permissions ?
9. Does the user have required permissions in the required folders on their machine and on the network
10. Check Hard Disk Space
11. Are any Anti-virus software's running on the machine ?
12. Have any changes been made to this machine recently ?
13. Check Event Log
14. Create a new ODBC
Check the user's security, try the process with a user with full permissions
Talk to the IT guy to get this view
Try and replicate the problem on the test server for the company
Try and replicate the problem on your test server
Check queries fired in Profiler
Use the Support Debugging tool
Ask a consultant who has used Great Plains in the last millennium
Open a support ticket with Microsoft Support

And then, follow up with the user after a couple of days for small talk, and to make sure they are doing fine.

Victoria Yudin on Reporting on Extender Data

By Mariano Gomez, MVP, MCP

Fellow MVP Victoria Yudin, goes to great lengths to explain how to report on Extender data. Extender can be a valuable time saving customization tool, but data storage can be confusing even to the pros. Remember that Extender stores data in separate tables according to data types and the only way compile the data is by creating Extender Views.
Also don't miss out on her previous article about Extender and SmartList.
Victoria provides great detail on how to accomplish this with screenshots that will help you understand the process from beginning to end. More importantly, let her know what you think about her work by dropping her a comment.

How to determine if a report or a form has been modified??

By Mariano Gomez, MVP, MCP

How many times have you walked into a GP implementation done by a previous VAR and cannot establish what changes have been done on a report, if any? Or how many times have you come across forms and reports dictionaries with tons of objects and cannot tell by simply looking at these if they have been changed or not? Or have you made some changes to a report a few years aback and now cannot remember what these changes were? I get this question every once in a while and finally someone was keen enough to post it on the Dynamics GP public newsgroup. Solution Let me start by saying that ALL modified forms and reports should ALWAYS be backed up in the form of package files, and that ALL these package files should be stored in a source code control repository -- for example, Visual Source Safe -- and versioned if all possible, with notes on all changes done from version to version. However, this is not always possible, especially if the company happens to be a small company with limited technical and software development resources -- unless of course, the business happens to do software development :-) ). In order to establish what changes have been done to a form or a report, without having a source control repository, you can use old fashioned Microsoft Word... well, I will be using Microsoft Word 2007 for this example. In addition, we will use a slightly modified version of the SOP Blank Invoice report.

*Click on image to enlarge

1) Export the modified report to a package file. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance. Highlight the SOP Blank Invoice report and click on Export. Save the file as SOPBlankInvoice_Modified.package

*Click on image to enlarge

2) From a workstation not pointing to the REPORTS.DIC dictionary file containing the modified report or from a standalone copy of GP, say for example the one you carry on your laptop, print the SOP Blank Invoice report to screen. Go to Transactions > Sales > Sales Transactions and choose an invoice. Print to screen. Click on Modify to open the report with Report Writer. Once the report is shown in the Report Layout window, return to Dynamics GP.

*Report with no customizations. Click on image to enlarge

3) Repeat step 1 on your standalone environment, this time saving the file as SOPBlankInvoice_Original.package. Move the SOPBlankInvoice_Modified.package file to the same directory with the SOPBlankInvoice_Original.package file. This will both files easily accessible.

*Click on image to enlarge

4) Open Microsoft Word 2007.

Click on the Review menu item.

*Click on image to enlarge

5) Click on the Compare option. Open your SOPBlankInvoice_Original.package in the Original document column, open SOPBlankInvoice_Modified.Package in the Revised document. Changes can be labeled with markers to allow for easy identification. Word will run the comparison and highlight any changes between the two documents

*Click on image to enlarge

Remember: it's not about the tools, it's about how you use these tools to meet your needs. I hope this article provides a mechanism to quickly and accurately identify changes between original reports and customized reports and help you get a headstart when working at customers and customizations you have not created -- or may have and don't recall :-) .

Until next post!

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
CTO
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/
http://mohdaoud.blogspot.com/

Friday, December 12, 2008

Microsoft Dynamics GP SMS Services!

Dear All,

I finally released the SMS services version of Microsoft Dynamics GP! You can get notified about all new orders, invoices, inquiries, security breaks and any kind of business alerts in your system directly to your phone!

Tool was developed based on .Net technology with extremely dynamic process and configuration, basically it’s using predefined triggers using any method available in GP, you can use Business Alerts, Reminders, Smart-List and any newly added records for triggering.

The tool is available with competitive price that’s nothing comparing to the benefits gained.

Please don’t hesitate to contact me for any further information about this tool or its rates.

Keywords: Microsoft Dynamics GP SMS; SMS Services; SMS Engine; SMS And Dynamics.

MSN: Dawood_2@hotmail.com

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/
http://mohdaoud.blogspot.com/

Wednesday, December 10, 2008

All about SOP distributions

By Mariano Gomez

Microsoft MVP Victoria Yudin seems to be everywhere in these days. If you have ever wondered how GP calculates all these pesky SOP invoice accounting distributions, wonder no more! Victoria decodes in a very friendly and illustrative manner how GP decides the fate of the accounting distributions to be posted to General Ledger when an invoice is posted in the Sales Order Processing module.

If I may add one more thing about the SOP distribution types, this field is used to categorize the distributions for the purpose of verifying the distributions on a document and can be framed in one of these 23 types:

1 = SALES
2 = RECV
3 = CASH
4 = TAKEN
5 = AVAIL
6 = TRADE
7 = FREIGHT
8 = MISC
9 = TAXES
10 = MARK
11 = COMMEXP
12 = COMMPAY
13 = OTHER
14 = COGS
15 = INV
16 = RETURN
17 = IN USE
18 = IN SERVICE
19 = DAMAGED
20 = UNIT
21 = DEPOSITS
22 = ROUND
23 = REBATE

The corresponding numeric value is stored in the SOP_Distribution_WORK_HIST table (SOP10102).

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/
http://mohdaoud.blogspot.com/

Identifying Duplicate Transactions

By David Musgrave

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Because Microsoft Dynamics GP is designed to have separate tables for WORK, OPEN and HISTORY transactions, there are times where it is possible for a transaction record to exist in more than one table. This is usually the result of an error or interruption.

These duplicate records become an issue when it comes time to move a transaction from one table to another. For example: when it is posted, fully applied, or paid transaction removal is used. At this time, the duplicate will generate a duplicate key error and the process will be aborted.

Another time when duplicate records can cause problems is when using an Inquiry window which can show data from more than one of the WORK, OPEN and HISTORY tables at the same time. To achieve this, the Inquiry windows transfer data into a single temporary table and use this table for their display. If duplicate records exist, you will get an error when the data is being copied into the temporary table.

An error message that may be generated by several inquiry windows, when there are duplicates, refers to a createSQLTmpTable stored procedure. This stored procedure does not actually exist, but is in fact referring to pass through SQL script called from the Dexterity code. For example: The error message from the Payables Transaction Inquiry window is The stored procedure createSQLTmpTable returned the following results, DMBS: 2627, Microsoft Dynamics GP: 0. Error 2627 is a SQL Cannot insert duplicate key error.

To make it easier to find duplicates, I am providing the following SQL queries to look for duplicate headers in the core Distribution and Financial modules. While these will not find every possible duplicate in every table, they are a great basis for checking for duplicate transactions.

SQL Script to look for duplicate headers in SOP, IVC, POP, RM, PM, IV, & GL modules

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Useful SQL Scripts Series

Guys,

David started his new piece, he’s working on SQL Scripts package that's needed to complement GP environment, checkout his blog below:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx?CommentPosted=true#commentmessage

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

search all columns of all tables for a given search string and replace it with another string

Guys,

By: Narayana Vyas Kondreddi
My colleague and best friend Areej Neshewat just sent me the following stored procedure for knowledge sharing.
This procedure replaces any kind of strings in the database with other.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SearchAndReplace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SearchAndReplace]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Monday, December 8, 2008

Silent Installation for Dynamics GP

Guys,

If you are looking to install GP in silent mode without using packages, you may need to check command below, I found it as an answer to question in Microsoft Dynamics Forum:

msiexec /i "C:\path\bin\GreatPlains.msi" SQL_SERVER_NAME="server that is running Microsoft SQL Server" INSTALLDIR="installdirpath" SELECTED_COUNTRY="country" TRANSFORMS=:Inst0instance;c:\skipdotnetcheck.mst INSTANCE_NAME="instancename" MSINEWINSTANCE=1 /qb+

Along with that command from above, you can use the "ADDLOCAL" command to install certain modules from GP. I found a list of codes for each module and they seem to be valid (at least the several I have tried).

A4 - A4
AA - Analytical Accounting
BM - Bank Management
CPR - Canadian Payroll
CM - Collections Management
DDR - Direct Debit Refunds
PMEFT - EFT for Payables Management
RMEFT - EFT for Receivables Management
EREC - Electronic Bank Reconcile
EM - Encumbrance Management
EI - Enhanced Intrastat
EXTENDER - Extender
FAM - Fixed Asset management
GP - GP
GM - Grant Management
HRM - Human Resources
MFG - Manufacturing
MLCHECKS - Multilingual Checks
PDM - Payment Document Management
PA - Project Accounting
RED - Revenue/Expense Deferrals
SP - Safe Pay
SI - Scheduled Instalments
SLB - SmartList Builder

For instance, if you wanted to install Fixed Assets, Safe Pay, and Smartlist Builder you would put "ADDLOCAL=FAM,SP,SLB"

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Sunday, December 7, 2008

Programmatically Closing for Dynamics GP

By David Musgrave

If you wish to automatically close Dynamics GP from .Net Application or VBA, just follow steps below:

In your Project, go to references and add a reference to “Dynamics Continuum Integration Library” then use the code below:

Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP.MAC""; " &
vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form

BuiLtin command cmdQuitApplication; " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf

' Execute SanScript

CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

An available update may be required for your computer, but the update process couldn't verify information.

An available update may be required for your computer, but the update process couldn't verify information.

Sometimes when you setup client updates functionality in GP 10.0 and do not specify the correct information, the message above will appear upon login and the system will shutdown.

I found this question on the Dynamics Newsgroup and realized that it should be solved somewhere, the solution for this is only by going to Dynamics database, locate “SYUPDATE” table and delete the records saved there.

Hope this helps.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Useful SQL Scripts

By David Musgrave

After the success of the Microsoft Dynamics GP Application Level Security Series, I have decided to release a another series which contains some really useful SQL scripts I have collected over time.

The posts in the series (so far) include:

01-Dec: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
03-Dec: Releasing Stuck Batches and Transactions without exiting all Users
05-Dec: Identifying Duplicate Transactions

Note: Links will be available on or after the posting date.

I hope you find the information in this series useful. I plan to add more scripts as time goes on.
If you have any scripts you wish to donate to the series, please contact me using the Email link at the top of the blog page.

For some other cool scripts see this article on Mariano Gomez's blog.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Friday, December 5, 2008

How To Delete GP Company

I got allot of questions on how to delete company from GP, script below is the answer:

CAUTION: Do not delete the company from Enterprise Manager first (step 2). If this
is done, you will need to run SQL scripts to delete database tables individually. You
will need to delete any references to the company in several DYNAMICS tables. The
example below shows a list of some of the tables that might need to be removed.
Example:
Use DYNAMICS
Select * from DYNAMICS…SY01500
The result set shows you the CMPANYID and INTERID fields for your company. Run
the following script, replacing "-1" with CMPANYID and "TWO" with INTERID with
your company’s information:
Use DYNAMICS
go
delete MC60100 where CMPANYID = -1
delete MC60200 where CMPANYID = -1
delete SY00801 where CMPANYID = -1
delete SY01500 where CMPANYID = -1
delete SY01900 where CMPANYID = -1
delete SY02000 where CMPANYID = -1
delete SY02100 where CMPANYID = -1
delete SY05200 where CMPANYID = -1
delete SY40500 where CMPANYID = -1
delete SY60100 where CMPANYID = -1
delete UPR10300 where CMPANYID = -1
delete UPR10304 where CMPANYID = -1
delete W200002 where CMPANYID = -1
delete DU000020 where companyID = -1
delete DU000030 where companyID = -1
delete DB_Upgrade where db_name = 'TWO'

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Sunday, November 30, 2008

Number to Words in Report Writer

By David Musgrave

One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words. After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters. This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.

This example using the "Check with Stub on Top" report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation. The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.

The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table. When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.

NOTE: This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Example code for v8.0, v9.0 & v10.0 is found under the following link:
http://blogs.msdn.com/developingfordynamicsgp/attachment/8968837.ashx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Friday, November 21, 2008

Microsoft Dynamics GP Service Pack 3!

Link below will redirect you to the download page for GP SP3:

https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP10_ServicePack3.htm

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Tuesday, November 18, 2008

Filter Dynamics GP Lookups Using VBA

Guys,

A got allot of mails requesting to have Item Security functionality to distribute sites items and show only certain items for the warehouse keeper.

For those who are looking for such functionality, you need to follow the steps below:

1. Create a new table in the database that contains “User ID”, “Item Number” fields. This table will hold the items that will be mapped to each user.
2. Add “Items Lookup” form to VBA and include Item Number field.
3. Get the current connected user from Globals.
4. Loop the following code on all the items returned for the logged user:

Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
If ItemNumber.Value “Does not exist in our table” Then
RejectLine = True
End If
End Sub

Please let me know if you may need any help, this code is applicable for all lookup forms and details windows.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Tuesday, November 11, 2008

Filling Missing Transactions Numbers in GP

If the end user created any kind of transactions in GP that holds let’s say the ID 9 and another one that holds 10, then deleted 9; the next number will be 11 and 9 will be ignored.

For the clients that do not accept these gaps, and since almost all of GP transaction tables have setups for loading next ID number, I have created the following solution:

Create a trigger on your transactions table For INSERT and DELETE that perform the following actions:

UPDATE “Setup-Table” SET “Next-ID” =

(SELECT TOP 1 Covert(BIGINT, A.Number) + 1 As NextID From Table As A LEFT OUTER JOIN Table As B on Covert(BIGINT, A.Number) + 1 = Covert(BIGINT, B.Number) WHERE B.Number IS NULL)

WHERE SETUPKEY = XXX

Replace the “Number” with column name and Table with your table name.
Now each time a transaction added or deleted, your trigger will get the next available number and update the defined next number.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Dynamics GP, AX, SL, NAV into one ERP called Project Green?

Project Green is Dead!

Project Green started making headlines in late summer 2003, a few months before Microsoft made the formal announcement of what was supposed to become a horizontally integrated ERP suite. Project Green would do for ERP applications what bundling did for Office: put together disparate but related applications in a tidy, integrated package and eventually bring them to a single code base.

But Project Green seemed doomed right from its conception, with the horizontal-bundling task running into logistical and management problems. What looked good in concept proved harder to execute, and the channel may have been one of the major reasons.

http://www.eweek.com/c/a/Enterprise-Applications/Project-Green-is-Dead151for-the-Foreseeable-Future-at-Least/
http://fscavo.blogspot.com/2007/03/microsofts-project-green-is-dead.html
http://www.microsoft-watch.com/content/business_applications/rip_project_green_2003_2007.html

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

DEX.INI Commands Explanation

Interested to learn more about DEX.ini file and what does it effect? I found a post under (http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-dexini-settings.html) and found it interesting, below the parameters definition for DEX.ini File:

SuppressChangeDateDialog=TRUE - Turns off the Date Change dialogue that opens at midnight
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 file://server/folder/ole
NoPrintDialogs=TRUE - Suppress the print dialogue box
ShowResids=TRUE - Make resource ID's visible in Dexterity
SQLLogSQLStmt=TRUESQLLogODBCMessages=TRUESQLLogAllODBCMessages=TRUE - Turn on Dex SQL Logging (all 3 lines)
ShowAdvancedMacroMenu=TRUE - Turn on the Advanced Macro Menu
C:\DPS1\DEX.INI DPSInstance=1C:\DPS2\DEX.INI DPSInstance=2C:\DPS3\DEX.INI DPSInstance=3 - Run multiple process servers on a single machine. Specifics on CustomerSource here. (One line for each process server)
AutoInstallChunks=TRUE - Add CNK files without the "Add New Code?" dialogue
ExportOneLineBody=TRUE - eliminate line wrapping when exporting reports to comma or tab delimited files.
IMPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IM.EXEIMExecPath=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)
WindowMax=TRUE - Open the GP main window full screen
SampleDateMsg=FALSE - prevents the sample company dialog box from being displayed
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).

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

List of inventory items that have not sold between two dates

View below list the inventory items that have not sold between two dates:

SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
AND dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
WHERE (dbo.SOP30200.DOCDATE BETWEEN
CONVERT(DATETIME, '2006-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-10-30 00:00:00', 102))
))
AND (dbo.IV00102.QTYONHND > 0)

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Monday, November 10, 2008

Item Transactions Analysis

Interested in analyzing your items transaction? Check view below:

SELECT
dbo.SOP10100.DOCDATE,
dbo.SOP10200.LOCNCODE as TRXLOCTN,
CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN 'Return Unposted' When dbo.SOP10200.SOPTYPE = 3 Then 'Sales Unposted' end AS TrxType,
dbo.SOP10200.SOPNUMBE as DOCNUMBR,
dbo.SOP10200.ITEMNMBR,
dbo.SOP10200.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 3 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYOUT
FROM dbo.SOP10200 INNER JOIN
dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 ON dbo.SOP10200.SOPNUMBE = dbo.SOP10100.SOPNUMBE

UNION ALL

SELECT dbo.SOP30200.DOCDATE, dbo.SOP30300.LOCNCODE as TRXLOCTN, CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN 'Sales Posted' WHEN dbo.SOP30300.SOPTYPE = 4 THEN 'Return Posted' end AS TrxType, dbo.SOP30300.SOPNUMBE as DOCNUMBR, dbo.SOP30300.ITEMNMBR, dbo.SOP30300.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 4 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYOUT

FROM dbo.SOP30300 INNER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP30200 ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Adjustment Unposted' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV10001.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY > 0 THEN dbo.IV10001.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY < 0 THEN ABS(dbo.IV10001.TRXQTY) ELSE 0 END, 0) AS QTYOUT

FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 1)or (dbo.IV10001.IVDOCTYP = 2)
UNION ALL

SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Adjustment Posted' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) > 0 THEN dbo.IV30300.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) < 0 THEN ABS(dbo.IV30300.TRXQTY) ELSE 0 END, 0) AS QTYOut

FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 1) or (dbo.IV30300.DOCTYPE = 2)
UNION ALL

SELECT dbo.POP10300.receiptdate as DOCDATE , dbo.POP10310.LOCNCODE as TRXLOCTN,
'Unposted Purchasing' AS TrxType, dbo.POP10300.POPRCTNM as DOCNUMBR, dbo.POP10310.ITEMNMBR,
dbo.POP10310.ITEMDESC,
CASE WHEN dbo.POP10300.POPTYPE <> 2 and dbo.POP10310.UNITCOST <>0 THEN (ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP10300.POPTYPE = 2 and dbo.POP10310.UNITCOST <>0 THEN ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0)
ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP10310 ON dbo.IV00101.ITEMNMBR = dbo.POP10310.ITEMNMBR INNER JOIN
dbo.POP10300 ON dbo.POP10310.POPRCTNM = dbo.POP10300.POPRCTNM
UNION ALL

SELECT dbo.POP30300.receiptdate as DOCDATE, dbo.POP30310.LOCNCODE as TRXLOCTN, 'Posted Purchasing' AS TrxType, dbo.POP30300.POPRCTNM as DOCNUMBR, dbo.POP30310.ITEMNMBR,
dbo.POP30310.ITEMDESC,
CASE WHEN dbo.POP30300.POPTYPE <> 2 and dbo.POP30310.UNITCOST <>0 THEN (ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP30300.POPTYPE = 2 and dbo.POP30310.UNITCOST <>0 THEN ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0)ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP30310 ON dbo.IV00101.ITEMNMBR = dbo.POP30310.ITEMNMBR INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM

UNION ALL

SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRNSTLOC as TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR

WHERE (dbo.IV10001.IVDOCTYP = 3)

UNION ALL
SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRNSTLOC as TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10000.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 3)

UNION ALL

SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN , ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Thursday, November 6, 2008

Drop and recreate database Logins

Script below loop database users then drop and recreate them one by one:

DECLARE @UserName Varchar(500)
DECLARE UsersCurr Cursor For Select Name FROM sysusers WHERE (islogin = 1) AND (name <> 'dbo') AND (name <> 'guest')
Open UsersCurr
Fetch Next From UsersCurr Into @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_revokedbaccess @UserName
EXEC sp_droplogin @UserName
EXEC sp_addlogin @UserName, '123'
EXEC sp_grantdbaccess @UserName, @UserName
EXEC sp_addrolemember 'DYNGRP', @UserName
Fetch Next From UsersCurr Into @UserName
END
Close UsersCurr
DEALLOCATE UsersCurr

You can modify the above script to fit your needs.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Monday, November 3, 2008

Dynamically Get GP database connection information when using VBA

Dynamically Get GP database connection information when using VBA:

'Class Module: GP Dex_VBA_Link
'Author: Matt Connor
'Version: 2.01
'Updated: 06-Apr-2006

'Connection Properties
Public PasswordUnencrypted As Boolean
Public SQLPassword, SQLPasswordDecoded As String
Public UserID, SQLDataSourceName, RuntimeVersion As String
Public IntercompanyID, CompanyName As String
Public UserDate As Date

Private GP_App As Object
Private GP_Product As Object

Private Sub Get_GP_Info()
'Gets connection properties from dexterity and assigns to class properties
On Error GoTo ErrorHandler
Dim dex As String, dex_error_msg As String, MsgBoxTitle As String
MsgBoxTitle = "Get_GP_Info"
Set GP_App = CreateObject("Dynamics.Application")
GP_App.CurrentProductID = 0
GP_App.SetParamHandler Me
dex = ""
dex = dex & "local boolean Dummy, PasswordUnencrypted; "
dex = dex & "local string SQLPassword, SQLPasswordDecoded; "
dex = dex & "local string UserID, SQLDataSourceName, IntercompanyID, CompanyName, RuntimeVersion; "
dex = dex & "local date UserDate; "
dex = dex & " "
dex = dex & "set UserID to 'User ID' of globals; "
dex = dex & "set PasswordUnencrypted to 'Password Unencrypted' of globals; "
dex = dex & "set SQLPassword to 'SQLPassword' of globals; "
dex = dex & "set SQLPasswordDecoded to Utility_DecodeString(SQLPassword); "
dex = dex & "set IntercompanyID to 'Intercompany ID' of globals; "
dex = dex & "set CompanyName to 'Company Name' of globals; "
dex = dex & "set SQLDataSourceName to 'SQLDataSourceName' of globals; "
dex = dex & "set UserDate to 'User Date' of globals; "
dex = dex & "set RuntimeVersion to Runtime_GetVersionNum(); "
dex = dex & " "
dex = dex & "set Dummy to OLE_SetProperty(""UserID"", UserID); "
dex = dex & "set Dummy to OLE_SetProperty(""PasswordUnencrypted"", str(PasswordUnencrypted)); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLPassword"", SQLPassword); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLPasswordDecoded"", SQLPasswordDecoded); "
dex = dex & "set Dummy to OLE_SetProperty(""IntercompanyID"", IntercompanyID); "
dex = dex & "set Dummy to OLE_SetProperty(""CompanyName"", CompanyName); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLDataSourceName"", SQLDataSourceName); "
dex = dex & "set Dummy to OLE_SetProperty(""RuntimeVersion"", RuntimeVersion); "
dex = dex & "set Dummy to OLE_SetProperty(""UserDate"", str(UserDate)); "
dex_error_msg = ""
GP_App.ExecuteSanscript dex, dex_error_msg
If dex_error_msg <> "" Then
msg = "An unexpected error has occurred running Dexterity script:" & vbCrLf & vbCrLf & dex_error_msg
MsgBox msg, vbCritical, MsgBoxTitle
Exit Sub
End If
Set GP_App = Nothing
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Else
MsgBox "An unexpected error has occurred:" & vbCr & vbCr & Err.Source & vbCr & "Error " & Err.Number & vbCr & Err.Description, vbCritical, MsgBoxTitle
End Select
End Sub

Note: Applicable on versions earlier than GP 10.0.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

GL Query For Posted and Unposted GL Transactions

View Trial Balance for Posted and Unposted GL Transactions:

SELECT
'HISTORY' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL30000.JRNENTRY,
DBO.GL30000.ACTINDX,
DBO.GL30000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL30000
INNER JOIN DBO.DTA10100 ON DBO.GL30000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL30000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL30000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID

UNION ALL

SELECT
'POSTED' AS POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL10001.JRNENTRY,
DBO.GL10001.ACTINDX,
DBO.GL10000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORCRDAMT,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORDBTAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
GL10000.TRXDATE
FROM DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.GL10001 ON DBO.GL00105.ACTINDX = DBO.GL10001.ACTINDX
INNER JOIN DBO.DTA10100
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID AND DBO.DTA10100.DOCNUMBR = DBO.DTA10200.DOCNUMBR AND DBO.DTA10100.RMDTYPAL = DBO.DTA10200.RMDTYPAL ON DBO.GL10001.JRNENTRY = DBO.DTA10100.JRNENTRY AND DBO.GL10001.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL10001.SQNCLINE = DBO.DTA10100.SEQNUMBR
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID
INNER JOIN DBO.GL10000 ON DBO.GL10001.JRNENTRY = DBO.GL10000.JRNENTRY

/*WHERE DBO.GL10000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL00100.ACTINDX IN (5)"*/

UNION ALL

SELECT
'UNPOSTED' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL20000.JRNENTRY,
DBO.GL20000.ACTINDX,
DBO.GL20000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL20000
INNER JOIN DBO.DTA10100 ON DBO.GL20000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL20000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL20000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID

/* WHERE DBO.GL20000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL20000.ACTINDX IN (5) */

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Vendor Statement For Dynamics GP

Looking for a complete Vendor Statement that displays Posted and unposted transactions? Check the view below:

----------POP UNPOSTED----------------------------
SELECT
'RECIEVING UNPOSTED' AS TRXSOURCE,
dbo.POP10300.RECEIPTDATE AS DOCDATE,
dbo.POP10300.VNDDOCNM AS DOCNUMBR,
dbo.POP10300.VENDORID,
dbo.POP10300.SUBTOTAL-dbo.POP10300.TRDISAMT+dbo.POP10300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP10300
INNER JOIN dbo.PM00200 ON dbo.POP10300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
--------------------POP POSTED---------------------------------
SELECT
'RECIEVING POSTED' AS TRXSOURCE,
dbo.POP30300.RECEIPTDATE AS DOCDATE,
dbo.POP30300.POPRCTNM AS DOCNUMBR,
dbo.POP30300.VENDORID,
dbo.POP30300.SUBTOTAL-dbo.POP30300.TRDISAMT+dbo.POP30300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP30300
INNER JOIN dbo.PM00200 ON dbo.POP30300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
-------------------PAYMENT UNPOSTED--------------------
SELECT
'PAYMENT UNPOSTED' AS TRXSOURCE,
dbo.PM10400.DOCDATE ,
dbo.PM10400.PMNTNMBR AS DOCNUMBR,
dbo.PM10400.VENDORID,
0 AS CREDIT,
dbo.PM10400.DOCAMNT AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10400
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10400.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED-------------------------
SELECT
CASE

WHEN dbo.PM20000.DOCTYPE=1 THEN 'INVOICE POSTED'
WHEN dbo.PM20000.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=3 THEN 'MIS CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=4 THEN 'RETURN POSTED'
WHEN dbo.PM20000.DOCTYPE=5 THEN 'CREDIT MEMO POSTED'
WHEN dbo.PM20000.DOCTYPE=6 THEN 'PAYMENT POSTED'
END AS TRXSOURCE,
dbo.PM20000.DOCDATE,
dbo.PM20000.DOCNUMBR AS DOCNUMBR,
dbo.PM20000.VENDORID,
ISNULL(CASE
WHEN dbo.PM20000.DOCTYPE=1 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=2 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=3 THEN dbo.PM20000.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM20000.DOCTYPE=4 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=5 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=6 THEN dbo.PM20000.DOCAMNT
END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM20000 INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM20000.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED HISTORY-------------------------
SELECT
CASE
WHEN dbo.PM30200.DOCTYPE=1 THEN 'INVOICE POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=3 THEN 'MIS CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=4 THEN 'RETURN POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=5 THEN 'CREDIT MEMO POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=6 THEN 'PAYMENT POSTED HISTORY'
END AS TRXSOURCE,
dbo.PM30200.DOCDATE,
dbo.PM30200.DOCNUMBR AS DOCNUMBR,
dbo.PM30200.VENDORID,
ISNULL(CASE
WHEN dbo.PM30200.DOCTYPE=1 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=2 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=3 THEN dbo.PM30200.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM30200.DOCTYPE=4 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=5 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=6 THEN dbo.PM30200.DOCAMNT
END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM30200
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM30200.VENDORID
WHERE PM30200.VOIDED = 0
---------------------------------------------------------------
UNION ALL
----------PM UNPOSTED-----------------------------------------------
SELECT
CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN 'INVOICE UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=2 THEN 'FINANCE CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=3 THEN 'MIS CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=4 THEN 'RETURN UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=5 THEN 'CREDIT MEMO UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=6 THEN 'PAYMENT UNPOSTED'
END AS TRXSOURCE,
dbo.PM10000.DOCDATE,
dbo.PM10000.DOCNUMBR AS DOCNUMBR,
dbo.PM10000.VENDORID,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=2 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=3 THEN dbo.PM10000.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=4 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=5 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=6 THEN dbo.PM10000.DOCAMNT END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10000
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10000.VENDORID

Hope that this helps!

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
mohdaoud@gmail.com
mohdaoud.blogspot.com

Saturday, October 25, 2008

Microsoft Dynamics GP (Great Plains) Row Level Filtering (Row Level Security)

Guys,

David Musgrave the Microsoft Dynamics GP Specialist proofed that the GP could have Row Level Security customized by developing the tool for the sample company, the tool is great and contains all what we need to filter GP rows.

You can find this tool under the following link:
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/08/13/row-level-filtering-proof-of-concept.aspx?CommentPosted=true

But unfortunately the tool is still proof of concept, and not officially released, lets hope to have it one day as one of GP modules!

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Auto login for Microsoft Dynamics GP

I found the below idea in one of the forums and liked it!

1. Create new text file in “C:\Program Files\Microsoft Dynamics\GP” call it Login.mac
2. Paste the code below inside it and save.
3. Replace “sa” with your username.
4. Replace “123” with your password.
5. Replace “1” in “'(L) Company Names' item 1 # ''” with the company order of your companies selection list
6. Save the file.
7. Go to GP shortcut, right click and properties.
8. Change the target of the shortcut to include “Login.mac”

Old: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set
New: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set Login.mac

9. Open GP from this shortcut.

Logging file 'macro.log'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'User ID'
TypeTo field 'User ID' , 'sa'
MoveTo field Password
TypeTo field Password , '123'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # ''
MoveTo field 'OK Button'
ClickHit field 'OK Button'
CommandExec dictionary 'default' form 'Command_System' command CloseAllWindows
ActivateWindow dictionary 'default' form Toolbar window 'Main_Menu_1'

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Fix for Slow Correcting JE Lookups

I found the following post @ Mark's [MVP] blog and thought it will be helpful!

When you do a correcting Journal Entry in GP, the lookup to find the entry to correct can be brutally slow. it's so slow because it's looking up every journal entry you've done in, like, forever. It's so torturously slow that that the CIA is considering it for use at Guantanamo.

Well, today Jivtesh points us to a Microsoft Knowledge Base article that can help fix that. KB 925326 provides a small SQL script that adds a non-clustered index to the GL20000 and GL30000 tables.

That should help speed things up a bit. Of course, if you simply never made any mistakes, it would never be a problem.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

Copy Microsoft Dynamics GP Setup

To copy Microsoft Dynamics GP setup from company to another company DTS the following tables via SQL

Finance
GL00100 Chart of Accounts
Note Do not copy the GL00101
GL00102 Account Category Master
GL00103 Fixed Allocation Accounts
GL00104 Variable Allocation Accounts
GL00105 Account Index Master
CM00100 Checkbook Master
CM40100 Cash Management Setup
CM40101 Cash Management Transaction Type Setup
GL00200 Budget Master file
GL00201 Budget Master file
GL40000 General Ledger Setup
GL40100 Quick Journal Setup
GL40101 Quick Journal Setup
GL40200 Segment Description Master
SY04100 Bank Master
MC40000 Multicurrency Setup
MC40100 Multicurrency Setup
ASI*.* Advanced Lookup Files

Purchase
PM00100 PM Class Master
PM00101 Vendor Class Accounts
PM00200 Vendor Master
PM00203 Vendor Accounts
PM00300 Address Master
PM40100 PM Setup File
PM40101 PM Setup File
PM40102 PM Setup File
PM40103 PM Setup File
POP00101 Buyer Master
POP40100 Purchasing Setup File
POP40600 Purchasing Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Sales
IVC40100 Invoicing Setup
IVC40101 Invoicing Setup
RM00101 Customer Master
RM00102 Customer Master Address
RM00105 National Accounts Master
RM00201 Class Master
RM00301 Salesperson Master files
RM00303 Sales Territory Master
RM40101 RM Setup
RM40201 RM Period Setup
RM40401 RM Document Type Setup
SOP00100 Process Holds Master
SOP00200 Prospect Master
SOP40100 Sales Setup
SOP40200 Type ID setup
SOP40201 Default Process Holds Setup
SOP40300 Document Setup
SOP40400 User Defined Table Setup
SOP40500 Master Number Setup
SOP40600 Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Inventory
BM00101 Bill of Materials Header
BM00111 Bill of Materials Component
BM40100 Bill of Materials Setup
IV00101 Item Master
IV00102 Item Quantity Master
IV00103 Vendor Master
IV00104 Kit Master
IV00105 Item Currency Master
IV00106 Item Purchasing
IV00107 Item Price List Options
IV00108 Item Price List
IV00109 Item Serial Number Mask
IV40100 Inventory Control Setup
IV40201 U of M Setup
IV40202 U of M Setup
IV40400 Item Class Setup
IV40401 Item Class Currency Setup
IV40500 Item Lot Category Setup
IV40600 Item Category Setup
IV40700 Item Site Setup
IV40800 Price Level Setup Company Folder
IV40900 Price Group Master
IV41000 Stock Calendar
IV41001 Stock Calendar Exception Days
ASI*.* Advanced Lookup Files

Company
SY00300 Account Format Setup
SY01100 Posting Account Master
SY02200 Posting Journal Destinations
SY02300 Posting Setup
SY03000 Shipping Methods
SY03100 Credit Card Setup
SY03300 Payment Terms Setup
SY40100 Fiscal Period Setup
SY40101 Fiscal Period Setup
TX00101 Tax Schedule Header Master
TX00102 Tax Schedule Master
TX00201 Tax Master
STN*.* Named Printers Setup
ASI*.* Dynamics Explorer Files

U.S. Payroll
When you copy payroll setup information from one company to another company,
the following setup tables are used:

UPR40100 Payroll Unemployment Setup
UPR40101 Payroll Unemployment TSA
UPR40200 SetupUPR40300 Department Setup
UPR40301 Job Title Setup
UPR40500 Accounts Setup
UPR40501 Tax Expense/Withholding Setup
UPR40600 Pay Code Setup
UPR40700 Workers Comp Setup
UPR40800 Benefit Setup
UPR40801 Benefit Based On Setup
UPR40900 Deduction Setup
UPR40901 Deduction Based On Setup
UPR40902 Deduction Sequence Setup
UPR41100 State Code Setup
UPR41200 Class Setup
UPR41201 Class Detail Setup
UPR41400 Local Tax Setup
UPR41401 Local Tax Table Setup
UPR41500 Shift Code Setup
UPR41700 Supervisor Setup
UPR41800 Payroll Maximum Deduction Setup (only in Microsoft Dynamics GP 10.0)
UPR41801 Payroll State/Fed Setup (only in Microsoft Dynamics GP 10.0)
UPR41900 Payroll Earnings Setup (only in Microsoft Dynamics GP 10.0)
UPR41901 Payroll Earnings Paycode (only in Microsoft Dynamics GP 10.0)
UPR41902 Payroll Earnings Deductions (only in Microsoft Dynamics GP 10.0)

Note If you copy the UPR40500 file, the posting accounts will be identical
to those of the company that you are copying.

Payroll Extensions (deduction in arrears, payables integration to payroll,
overtime rate manager)
ORM_UPR_SETP_OT_DTL
ORM_UPR_SETP_OT_HDR
UPR40600_OT
APR_DIA40100
APR_DIA40200
APR_UPR40500
APR_UPR40900
APR_PIP40100

Advanced Payroll
APR40600
APR41100
APR41101
APR41501
APR41601
APR_APR70901
APR_APR70900
APR_UPR40500
APR_APR40101
APR_APR40100

Canadian Payroll
CPY10010 CDN Payroll Employer Master
CPY10020 CDN Payroll Department Master
CPY10030 CDN Payroll Employee Job Titles
CPY10050 CDN Payroll Employee Class
CPY10051 CDN Payroll Class Attached Pay codes File
CPY10060 CDN Payroll Pay code Master
CPY10061 CDN Payroll Pay code Attached Pay codes
CPY10062 CDN Payroll Income Attached Pay Codes
CPY10063 CDN Payroll Rate Table Codes
CPY10064 CDN Payroll Rate Tables
CPY10070 CDN Payroll WCB Master
CPY10075 CDN Payroll WCB Administration
CPY10080 CDN Payroll User Paid By
CPY10081 CDN Payroll User Drop Down Strings
CPY10082 CDN Payroll Reporting Codes
CPY10170 CDN Payroll Employee Unions
CPY10171 CDN Payroll UnionAttached Pay codes
CPY20200 CDN Payroll Job Master
CPY20201 CDN Payroll Phase Master
CPY20700 P_Security_Group_MSTR
CPY20705 P_Security_Group_Detail
CPY20710 P_Security_User_MSTR

If the following information is the same, you can also copy these files:
CPY20100 CDN Payroll Control Master
CPY20110 CDN Payroll CSB Setup Information
CPY20111 CDN Payroll CSB Pay codes

Human Resource
BE020230 HR_Benefit_SETP
BE021030 BEN2_FMLA_Line
BE031000 BEN_FMLA_INFO
HR2Ben21 HR_Benefit_Tiers_SETP
HR2Ben11 HR_Benefit_Fund
HR2Ben12 HR_Benefit_MDVE_Table
HR2Ben13 HR_Benefit_Life_Premiums
HR2Ben14 HR_Venefit_MDVE_Types
HR2Div02 HR_Division2
HR2Tra01 HR_Train_Course
HR2Tra03 HR_Train_Class
HRCom022 HR_Company2_extra
HRDep022 HR_Department2_Extra
HRDiv022 HR_Division2_Extra
HRPBen05 HRP_BEN_FMLA_Set12Month
HRPro022 HR_Property
HRPppc01 HRP_Position_Pay_Code
HRsax012 HR_Salary_Matrix
HRsax022 HR_Salary_Matrix_Table
HRsax042 HR_Salary_Matrix_Col
HRsax032 HR Salary Matrix rows
HRtra042 HR_Train_Class_Skills
HRtrpc02 HR_Train_Position_Course_Class
HRtrps01 HR_Train_Position_Course
RV010221 HR_Review_LINE_V2
RV020221 HR_Review_Setup_LINE_V2
RV030221 HR_Review_Words_Setup_LINE
SK010230 HR_Skills_Line
TAAC0130 TA_SETP_Accrual_Type
TAPY0130 TA_Payroll_Link
Note The TAPY0130 TA_Payroll_Link table was removed in Microsoft Dynamics GP
9.0 and in Microsoft Dynamics GP 10.0.
TAST0130 TA_Setup
TAST0230 TA_Attendance_reason
TAST0330 TA_Attendance_Types
TAST0532 TA_Pay_Period_accrual_LINE
TATM0130 TA_SETP_Types

Advanced Human Resource
APR_BLM41500
APR_BLM41501
APR_BLM41600
APR_BLM41601
APR_BLM41400
APR_BLM41401
APR_BLM41100
APR_BLM41101
APR_BLM41300
APR_BLM41301
APR_BLM41200
APR_BLM41201
APR_BLM42100
APR_BLM42101
APR_BLM42200
APR_BLM42201
APR_BLM43100
APR_BLM43200
APR_BLM43201
APR_BLM43300
APR_BLM43301
APR_APR40500
EHW40100
EHW40201
EHW40200
EHW40300
EHW40400
EHW40501
EHW40500
CLM40100
CLM40300
CLM40700
CLM40701
CLM40600
CLM40500
CLM40400
CLM40200

PTO Manager
PTO40100
PTO40101
PTO40200
PTO40201

Posted Orginally by MicrosoftSlave in Microsoft Dynamics Forum.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Why does Microsoft Dynamics GP encrypt passwords?

Why does Microsoft Dynamics GP encrypt passwords?

Check post below:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Convert Numbers To Arabic Words (Tafqeet)

Function to Convert Numbers To Arabic Words (Tafqeet):

You Can Call This Function By:

Select Tafkeet(9875)

Code Below:

CREATE FUNCTION [dbo].[DI_Tafkeet] (@TheNo numeric(18,3))
returns varchar(1000) as

begin
if @TheNo <= 0   return 'zero'

declare @TheNoAfterReplicate varchar(15)
set @TheNoAfterReplicate = right(replicate('0',15)+cast(floor(@TheNo) as varchar(15)),15)
declare @ComWithWord varchar(1000),@TheNoWithDecimal as varchar(400),@ThreeWords as int
set @ThreeWords=0
set @ComWithWord = ' فقط '
declare   @Tafket TABLE (num int, NoName varchar(100))
INSERT INTO @Tafket VALUES (0,'')
INSERT INTO @Tafket VALUES (1,'واحد')
INSERT INTO @Tafket VALUES (2,'اثنان')
INSERT INTO @Tafket VALUES (3,'ثلاثة')
INSERT INTO @Tafket VALUES (4,'اربعة')
INSERT INTO @Tafket VALUES (5,'خمسة')
INSERT INTO @Tafket VALUES (6,'ستة')
INSERT INTO @Tafket VALUES (7,'سبعة')
INSERT INTO @Tafket VALUES (8,'ثمانية')
INSERT INTO @Tafket VALUES (9,'تسعة')
INSERT INTO @Tafket VALUES (10,'عشرة')
INSERT INTO @Tafket VALUES (11,'احدى عشر')
INSERT INTO @Tafket VALUES (12,'اثنى عشر')
INSERT INTO @Tafket VALUES (13,'ثلاثة عشر')
INSERT INTO @Tafket VALUES (14,'اربعة عشر')
INSERT INTO @Tafket VALUES (15,'خمسة عشر')
INSERT INTO @Tafket VALUES (16,'ستة عشر')
INSERT INTO @Tafket VALUES (17,'سبعة عشر')
INSERT INTO @Tafket VALUES (18,'ثمانية عشر')
INSERT INTO @Tafket VALUES (19,'تسعة عشر')
INSERT INTO @Tafket VALUES (20,'عشرون')
INSERT INTO @Tafket VALUES (30,'ثلاثون')
INSERT INTO @Tafket VALUES (40,'اربعون')
INSERT INTO @Tafket VALUES (50,'خمسون')
INSERT INTO @Tafket VALUES (60,'ستون')
INSERT INTO @Tafket VALUES (70,'سبعون')
INSERT INTO @Tafket VALUES (80,'ثمانون')
INSERT INTO @Tafket VALUES (90,'تسعون')
INSERT INTO @Tafket VALUES (100,'مائة')
INSERT INTO @Tafket VALUES (200,'مائتان')
INSERT INTO @Tafket VALUES (300,'ثلاثمائة')
INSERT INTO @Tafket VALUES (400,'أربعمائة')
INSERT INTO @Tafket VALUES (500,'خمسمائة')
INSERT INTO @Tafket VALUES (600,'ستمائة')
INSERT INTO @Tafket VALUES (700,'سبعمائة')
INSERT INTO @Tafket VALUES (800,'ثمانمائة')
INSERT INTO @Tafket VALUES (900,'تسعمائة')
INSERT INTO @Tafket
SELECT FirstN.num+LasteN.num,LasteN.NoName+' و '+FirstN.NoName FROM
(SELECT * FROM @Tafket WHERE num >= 20 AND num <= 90) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 9) LasteN

INSERT INTO @Tafket
SELECT FirstN.num+LasteN.num,FirstN.NoName+' و '+LasteN.NoName FROM (SELECT * FROM @Tafket WHERE num >= 100 AND num <= 900) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 99) LasteN

if left(@TheNoAfterReplicate,3) > 0
set @ComWithWord = @ComWithWord + ISNULL((select NoName from @Tafket where num=left(@TheNoAfterReplicate,3)),'')+ ' ترليون'
if left(right(@TheNoAfterReplicate,12),3) > 0 and left(@TheNoAfterReplicate,3) > 0
set @ComWithWord=@ComWithWord+ ' و '
if left(right(@TheNoAfterReplicate,12),3) > 0
set @ComWithWord = @ComWithWord +ISNULL((select NoName from @Tafket where num=left(right(@TheNoAfterReplicate,12),3)),'') + ' بليون'
if left(right(@TheNoAfterReplicate,9),3) > 0

begin
set @ComWithWord=@ComWithWord + case when @TheNo>999000000 then ' و' else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,9),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when   @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,9),3)),'') + case when @ThreeWords=2 then ' مليونان' when   @ThreeWords between 3 and 10 then ' ملايين' else ' مليون' end
end

if left(right(@TheNoAfterReplicate,6),3) > 0
begin
set @ComWithWord=@ComWithWord + case when @TheNo>999000 then ' و' else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,6),3)),'')+ case when @ThreeWords=2 then ' الفان' when @ThreeWords between 3 and 10 then ' الاف' else ' الف' end
end

if right(@TheNoAfterReplicate,3) > 0
begin

if @TheNo>999
begin
set @ComWithWord=@ComWithWord + ' و'
end

if right(@TheNoAfterReplicate, 2) = '01' or right(@TheNoAfterReplicate, 2) = '02'
begin
--set @ComWithWord=@ComWithWord + case when @TheNo>1000 then ' و' else '' end
--set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ' ' + ISNULL((select noname from @Tafket where num=right(@TheNoAfterReplicate, 3)),'')
end

set @ThreeWords=right(@TheNoAfterReplicate,2)

if @ThreeWords=0
begin
--   set @ComWithWord=@ComWithWord + ' و'
   set @ComWithWord = @ComWithWord + ISNULL((select NoName from @Tafket where @ThreeWords=0 AND num=right(@TheNoAfterReplicate,3)),'')
end

end

set @ThreeWords=right(@TheNoAfterReplicate,2)
set @ComWithWord = @ComWithWord +   ISNULL((select NoName from @Tafket where @ThreeWords>2 AND num=right(@TheNoAfterReplicate,3)),'')
set @ComWithWord = @ComWithWord +' '+ case when @ThreeWords=2 then ' ديناران' when @ThreeWords between 3 and 10 then ' دنانير' else ' دينار' end
if right(rtrim(@ComWithWord),1)=',' set @ComWithWord = substring(@ComWithWord,1,len(@ComWithWord)-1)
if right(@TheNo,len(@TheNo)-charindex('.',@TheNo)) >0 and charindex('.',@TheNo)<>0
    begin
        set @ThreeWords=left(right(round(@TheNo,3),3),3)
        SELECT @TheNoWithDecimal= ' و' + ISNULL((SELECT NoName from @Tafket where num=left(right(round(@TheNo,3),3),3) AND @ThreeWords >3),'')
        set @TheNoWithDecimal = @TheNoWithDecimal+ case when @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات' else ' فلس' end
set @ComWithWord = @ComWithWord + ' و '+ CONVERT(varchar(max),@ThreeWords)+ case when @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات' else ' فلس' end --@TheNoWithDecimal
END
set @ComWithWord = @ComWithWord + ' لا غير '

return rtrim(@ComWithWord)
end

GO

UPDATE: Fixed some issues in covering numbers (101, 201, 301 …)

UPDATE: Fixed decimals not to be included as per many requests.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

How to search all columns of all tables in a database for a keyword?

BY http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

This procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Wednesday, December 31, 2008

Sunday, December 28, 2008

Monday, December 22, 2008

By Jivtesh Singh

Troubleshooting a Common GP Problem

By Mariano Gomez, MVP, MCP

Friday, December 12, 2008

Wednesday, December 10, 2008

Monday, December 8, 2008

Sunday, December 7, 2008

Friday, December 5, 2008

Sunday, November 30, 2008

Friday, November 21, 2008

Tuesday, November 18, 2008

Tuesday, November 11, 2008

Monday, November 10, 2008

Thursday, November 6, 2008

Monday, November 3, 2008

Saturday, October 25, 2008

Related Posts: