Dynamics GP Users: the creation of login failed for unknown reason. Contact your SQL Server Administrator for assistance.

 

One of my community fellows has reported a challenging case this evening, he been trying to add a user to Dynamics GP and unable to succeed with this operation, and the obvious cause for this message was that the user could be already added to your database and not added to your GP nor your SQL level logins but wasn’t the case, the message is showing on any name you selects and while running under the “sa” user.

When you create the user at the SQL level it creates with no issues, you only have a problem while creating the user from Dynamics GP, therefore I tried a fresh copy of Dynamics GP and tried to create users with no luck, been sure that the “sa” is a system administrator on the SQL level and the “sa” is the owner of the DYNAMICS and other companies databases.

Digging deeply into this I ran SQL profiler trace to see what’s going on and realized that everything is working as desired, I tried to create the user using the CREATE LOGIN command and it  creates the user perfectly! But noticed upon creating the user by using the command that the returned message wasn’t “Command Completed Successfully” it was “Mail Queued”, where I looked further into this and realized that the customer was having a DDL Trigger on the server level that tracks the changes on the Logins and sends an email to the Administrators informing them about this change following the best practices! And this was the real cause behind the issue, it seems that GP wasn’t receiving the expected result from adding this user and been generating unknown error, disabling the trigger got this issue to be fixed!

Hope that this helps.

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Requisitions Management Purchase – Item Cost Selection

One of my customers reported that when they transfer the Purchase Requisition to Purchase Order, the cost they specified in the requisition for their items is not being transferred to the purchase order.

Checking the Requisition setup I didn’t find an option to keep the cost of requisition on Purchase Order, only the below options are available:

For this particular customer, they are not using the “last vendor originating cost”, so I have created a Trigger that updates the field for each update occurs on requisition screen, below the trigger code if anybody is interested.

CREATE TRIGGER [dbo].[DI_UPDATEITEMCOST]
ON [dbo].[POP10210]
AFTER INSERT, UPDATE
AS

DECLARE @ITEMNMBR VARCHAR(500)
DECLARE @UNITCOST numeric(18,5)
DECLARE CUR CURSOR FOR SELECT ITEMNMBR, UNITCOST FROM INSERTED

OPEN CUR
FETCH NEXT FROM CUR INTO @ITEMNMBR, @UNITCOST

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE IV00103 SET Last_Originating_Cost = @UNITCOST WHERE ITEMNMBR = @ITEMNMBR AND @UNITCOST <> 0

FETCH NEXT FROM CUR INTO @ITEMNMBR, @UNITCOST
END
CLOSE CUR
DEALLOCATE CUR
GO

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Update Currency Index field across all database

You might need to change the currency index for one of your currencies, I needed this during a consolidation project for multiple DYNAMICS databases and needed to change the currency index field for all company tables, take a look into the below script, I have used the “Information_Schema” to get all columns that are called “CURRNIDX” and updated these using SQL Cursor, considering that I taking a backup for each table before doing the operation:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CURRNIDX' AND COLUMN_DEFAULT IS NOT NULL
OPEN CURR
FETCH NEXT FROM CURR INTO @TABLENAME
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @Statement = 'SELECT * INTO ' + @TABLENAME + '_BAK FROM ' + @TABLENAME 
EXEC (@Statement)

SET @Statement = 'UPDATE ' + @TABLENAME + ' SET CURRNIDX = [NEW CURRENCY ID] WHERE CURRNIDX = [OLD CURRENCY ID]'
EXEC (@Statement)

FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Update Company ID field across all company database

A couple of weeks back I needed to consolidate two DYNAMICS databases and move companies from the first DYNAMICS to the second, this for sure requires Company IDs to be changed as it might be taken by another companies on the main DYNAMICS, to fix this once for all I have wrote the following script:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CMPANYID' AND COLUMN_DEFAULT IS NOT NULL
OPEN CURR
FETCH NEXT FROM CURR INTO @TABLENAME
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @Statement = 'SELECT * INTO ' + @TABLENAME + '_BAK FROM ' + @TABLENAME 
EXEC (@Statement)
SET @Statement = 'UPDATE ' + @TABLENAME + ' SET CMPANYID = [NEW COMPANY ID] WHERE CMPANYID = [OLD COMPANY ID]'
EXEC (@Statement)
FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR

 

Hope that this helps.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Unable to start execution of step 1 (reason: line(1): Syntax error)

This morning I worked with one of the customers on configuring the database backups, and stuck with an issue that after finalizing the Maintenance Plan the job been generating an error, getting into the job history I have seen the below dumb message:

Unable to start execution of step 1 (reason: line(1): Syntax error)

Luckily I found the below articles about this subject and it helped:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc52e0d9-af14-46b9-9904-29b9afa798cd/syntax-error-executing-maintenance-plan-job?forum=sqldatabaseengine

http://www.sqlserver-expert.com/2011/08/unable-to-start-execution-of-step-1.html

I found few difficulties while looking into the “package” mentioned in the resolution and therefore I though I would share a screenshot, adding a slash “\” to the beginning of the text worked like a charm:

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Update SQL Server Collation for a Clustered Installation

I been in a situation with one of my clients where I been implementing and Active/Active clustering setup over SQL Server 2008 R2 as my customer is still using GP 10.0, after finalizing and testing everything I noticed that the code page I must use is 1256 while I have used 1250!!!

Thanks god for having a utilities to update the collation without having to destroy the cluster! I have followed the steps below and it worked like a charm:

1. Backup your objects! As this process will drop logins, linked servers, jobs, and anything related to the instant itself plus will de-attach your database.

2. Make sure that your current server is online and carrying your SQL Instant.

3. Make sure that SQL Services is offline.

4. Run the following command:

"E:\SQL\setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS="DAOUD-PC\DAOUD"

E:\SQL\setup.exe will need to be replaced with the locations of the SQL Server 2008 R2 setup.exe path.

MSSQLSERVER represents the default instance, if you have a named instance this must be changed to hold the instance name.

P@ssW0rd: is the new SA user password, it must be strong password or otherwise the repair will fail.

Arabic_CI_AI: is the new collation name.

DAOUD-PC\DAOUD need to be replaced with the local administrator of the server or PC you are installing the SQL on.

Paste above command into CMD and wait for 3-4 minutes, you will have your SQL Collation changed.

Warning: this operation will recreate your master database, were all existing setting will be reset, if you had databases attached it will be de-attached and will be found in the database folder.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Duplicated GL Transactions between Work and Open tables

One of my customers got a case that Journals were not deleting from GL10000 upon posting, this caused the transactions to duplicate between open and work tables and been annoying end users.

To fix this miss, I wrote a script that looks into GL10000 and GL10001 and compares the totals between both tables for each journal and generate the result, you can use this script to company these and make sure that all journals were successfully posted and none posted partially.

Below the script code:

Historical Stock Sttus Summary

1. SELECT UNPOSTED.JRNENTRY, UNPOSTED.AMOUNT, POSTED.JRNENTRY, POSTED.AMOUNT FROM
2.     (SELECT JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
3.     FROM GL20000 GROUP BY JRNENTRY) AS POSTED
4. INNER JOIN
5.     (SELECT GL10000.JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
6.     FROM GL10000 LEFT OUTER JOIN GL10001 ON GL10000.JRNENTRY = GL10001.JRNENTRY
7.     GROUP BY GL10000.JRNENTRY) AS UNPOSTED
8. ON POSTED.JRNENTRY = UNPOSTED.JRNENTRY
9. WHERE UNPOSTED.AMOUNT IS NULL

Hope that this helps.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Historical Stock Status Script

We been suffering from Historical Stock Status report issues when it comes to customers with big inventory transactions, I am providing a script that recalculate the historical stock status for your items based on the IV10200 and IV10201 tables, it will allow you to estimate your inventories using a historical date:

Historical Stock Sttus Summary

1. DECLARE @ASOFDATE DATETIME
2. SET @ASOFDATE = '2016-12-31'
3.  
4. SELECT
5. ITEMNMBR AS [Item Number],
6. TRXLOCTN AS [Location],
7. SUM(Quantity) AS Quantity,
8. SUM([Extended Cost]) AS [Total Cost]
9.  
10. FROM
11.  
12. (SELECT ITEMNMBR,
13.  
14. (dbo.IV10200.QTYRECVD -
15. ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
16.         WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
17.         AND ITEMNMBR = IV10200.ITEMNMBR
18.         AND DOCDATE <= @ASOFDATE), 0))
19. AS [Quantity],
20.         UNITCOST *
21.             (dbo.IV10200.QTYRECVD -
22.             ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
23.             WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
24.             AND ITEMNMBR = IV10200.ITEMNMBR
25.             AND DOCDATE <= @ASOFDATE), 0))
26. AS [Extended Cost],             
27. TRXLOCTN
28. FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
29. GROUP BY ITEMNMBR, TRXLOCTN

Hope that his helps!

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Installing SQL Failover Clustering Without SAN Storage – Windows 2008 R2

It always been hard to test out the failover clustering as it requires SAN Storage device to be connected and requires many servers for testing.

This morning I have seen an interesting article in the community the Microsoft has released “Microsoft iScsi Software Target” free of charge, by using the utility you can create a storage and share it across multiple servers, “fooling” the windows to think that a SAN Storage is available.

Following the above, I been able to install failover clustering for testing over one single machine without any other equipments! I used Hyper-V to create 3 Windows 2008 R2 Servers and 1 Virtual Hard Disk Drive and worked out a fully functioning test environment, below what you need if you like to test this one your machine:

1. Over a test server, install Windows 2008 R2 and activate the Hyper-V.

2.  Create 3 virtual machines, one for SQL Server Node1, and the second for the second failover node, and the third one to act as storage server.

3. Create 1 virtual hard disk to represent your SAN Storage.

When you gather these, click on the following tech-net article that details all what you need:

http://blogs.technet.com/b/mghazai/archive/2009/12/12/hyper-v-guest-clustering-step-by-step-guide.aspx

Hope that this helps.

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Auto Assign Newly Added Analytical Accounting Code to All Users

I got a request from one of my customers, they had a need to auto assign newly created AA codes to all users and that how the idea started, I saw the scripts created by Sivakumar earlier to auto assign all codes to all users and used his cross join to create the below trigger.

The trigger is simply works on AAG00401 table upon record insert, it assigns the newly created codes to all GP users automatically, just run below script over your company database and enjoy.

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

 

-- =============================================
-- Author     :    Mohammad R. Daoud
-- Create date: 09:56 AM ‎07/‎12/‎2012
-- Description:    This trigger as created to auto assign 
-- newly created analytical accounting codes to all users.
-- =============================================
CREATE TRIGGER DI_AutoAssignSecurity
   ON  AAG00401
   FOR INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @aaTrxDimID INT
DECLARE @aaTrxDimCodeID INT

SELECT TOP 1 @aaTrxDimID = aaTrxDimID, @aaTrxDimCodeID = aaTrxDimCodeID FROM INSERTED
DELETE FROM AAG02000 WHERE aaTrxDimID = @aaTrxDimID AND AAG02000.aaTrxDimCodeID = @aaTrxDimCodeID
INSERT INTO AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AAG00401 A CROSS JOIN DYNAMICS..SY01400 B WHERE A.aaTrxDimID = @aaTrxDimID AND A.aaTrxDimCodeID = @aaTrxDimCodeID) 
END
GO

COPY Advanced Financials Reports Across Companies

 

Normally you will need to create your balance sheet and paste it over all your companies, script below will do the task for you:

 

Download Link:

http://www.di.jo/files/COPY Advanced Financials Reports Between Companies.zip

 

Script:

--REPLACE Source with your source Company ID

--REPLACE Destination with your destination Company ID

INSERT INTO [Destination].DBO.AF40100 ([RPRTNAME],[REPORTID],[RPRTTYPE],[CLCFRPRT],[LSTMODIF],[NOTEINDX])  SELECT [RPRTNAME],[REPORTID],[RPRTTYPE],[CLCFRPRT],[LSTMODIF],[NOTEINDX] FROM [Source].DBO.AF40100

INSERT INTO [Destination].DBO.AF40101 ([REPORTID],[MNHDRCNT],[MNFTRCNT],[SHDRCNT],[SFTRCNT],[ROWCNT1],[COLCNT],[SHDRPCNT],[SFTRPCNT],[MNHDRFLG],[MNFTRFLG],[SHDRFLAG],[SFTRFLAG],[MNHDRSIZ],[MNFTRSIZ],[SHDRSIZE_1],[SHDRSIZE_2],[SHDRSIZE_3],[SHDRSIZE_4],[SHDRSIZE_5],[SFTRSIZE_1],[SFTRSIZE_2],[SFTRSIZE_3],[SFTRSIZE_4],[SFTRSIZE_5],[SHDROPT_1],[SHDROPT_2],[SHDROPT_3],[SHDROPT_4],[SHDROPT_5],[SHDRPRT_1],[SHDRPRT_2],[SHDRPRT_3],[SHDRPRT_4],[SHDRPRT_5],[SFTROPT_1],[SFTROPT_2],[SFTROPT_3],[SFTROPT_4],[SFTROPT_5],[SFTRPRT_1],[SFTRPRT_2],[SFTRPRT_3],[SFTRPRT_4],[SFTRPRT_5],[COLHDCNT],[COLDHSIZ_1],[COLDHSIZ_2],[COLDHSIZ_3],[COLDHSIZ_4],[COLDHSIZ_5],[COLDHSIZ_6],[RTOTLSIZ],[COLTOSIZ],[COLOFSIZ],[LFTMARGN],[RTMARGIN],[TOPMARGN],[BOTMARGN]) SELECT [REPORTID],[MNHDRCNT],[MNFTRCNT],[SHDRCNT],[SFTRCNT],[ROWCNT1],[COLCNT],[SHDRPCNT],[SFTRPCNT],[MNHDRFLG],[MNFTRFLG],[SHDRFLAG],[SFTRFLAG],[MNHDRSIZ],[MNFTRSIZ],[SHDRSIZE_1],[SHDRSIZE_2],[SHDRSIZE_3],[SHDRSIZE_4],[SHDRSIZE_5],[SFTRSIZE_1],[SFTRSIZE_2],[SFTRSIZE_3],[SFTRSIZE_4],[SFTRSIZE_5],[SHDROPT_1],[SHDROPT_2],[SHDROPT_3],[SHDROPT_4],[SHDROPT_5],[SHDRPRT_1],[SHDRPRT_2],[SHDRPRT_3],[SHDRPRT_4],[SHDRPRT_5],[SFTROPT_1],[SFTROPT_2],[SFTROPT_3],[SFTROPT_4],[SFTROPT_5],[SFTRPRT_1],[SFTRPRT_2],[SFTRPRT_3],[SFTRPRT_4],[SFTRPRT_5],[COLHDCNT],[COLDHSIZ_1],[COLDHSIZ_2],[COLDHSIZ_3],[COLDHSIZ_4],[COLDHSIZ_5],[COLDHSIZ_6],[RTOTLSIZ],[COLTOSIZ],[COLOFSIZ],[LFTMARGN],[RTMARGIN],[TOPMARGN],[BOTMARGN] FROM [Source].DBO.AF40101

INSERT INTO [Destination].DBO.AF40102 ([REPORTID],[HDRFTRTY],[FLDNUM],[FLDPOSX1],[FLDPOSY1],[FLDPOSX2],[FLDPOSY2],[FLDTYPE],[FLDFRMAT],[SBHSBFIN],[FLDOPT],[FLDOPT2],[FLDALIGN],[FLDFTFML],[FLDFTSIZ],[FLDSTYLE_1],[FLDSTYLE_2],[FLDSTYLE_3],[FLDSTYLE_4],[FLDSTYLE_5],[FLDSTYLE_6],[FLDVALUE],[FLDVALU2],[FLDPRNAM]  ) SELECT [REPORTID],[HDRFTRTY],[FLDNUM],[FLDPOSX1],[FLDPOSY1],[FLDPOSX2],[FLDPOSY2],[FLDTYPE],[FLDFRMAT],[SBHSBFIN],[FLDOPT],[FLDOPT2],[FLDALIGN],[FLDFTFML],[FLDFTSIZ],[FLDSTYLE_1],[FLDSTYLE_2],[FLDSTYLE_3],[FLDSTYLE_4],[FLDSTYLE_5],[FLDSTYLE_6],[FLDVALUE],[FLDVALU2],[FLDPRNAM]   FROM [Source].DBO.AF40102

INSERT INTO [Destination].DBO.AF40103 ([REPORTID],[COLNUM],[CLTKNCNT],[COLTYPE],[COLSIZE],[COLOMCNT],[COLOFMRK_1],[COLOFMRK_2],[COLOFMRK_3],[COLOFMRK_4],[HIDEFLAG],[TEXTVALU],[STPERIOD],[ENDPEROD],[AMNTFROM],[HISTYEAR],[BUDID],[PRTSIGN],[PRTCOMMA],[PRTPCENT],[PRTTEXT],[ROUNDOPT],[HEADALIN],[HDFTFMLY],[HDFTSIZE],[HEDSTYLE_1],[HEDSTYLE_2],[HEDSTYLE_3],[HEDSTYLE_4],[HEDSTYLE_5],[HEDSTYLE_6],[HEADTYPE_1],[HEADTYPE_2],[HEADTYPE_3],[HEADTYPE_4],[HEADTYPE_5],[HEADTYPE_6],[HEDFRMAT_1],[HEDFRMAT_2],[HEDFRMAT_3],[HEDFRMAT_4],[HEDFRMAT_5],[HEDFRMAT_6],[HEADOPT_1],[HEADOPT_2],[HEADOPT_3],[HEADOPT_4],[HEADOPT_5],[HEADOPT_6],[HEADOPT2_1],[HEADOPT2_2],[HEADOPT2_3],[HEADOPT2_4],[HEADOPT2_5],[HEADOPT2_6],[COLHDNG_1],[COLHDNG_2],[COLHDNG_3],[COLHDNG_4],[COLHDNG_5],[COLHDNG_6],[COLHDNG2_1],[COLHDNG2_2],[COLHDNG2_3],[COLHDNG2_4],[COLHDNG2_5],[COLHDNG2_6],[ALGNOFST],[COLEXPER],[NOTEINDX],[SEGFROM_1],[SEGFROM_2],[SEGFROM_3],[SEGFROM_4],[SEGFROM_5],[SEGFROM_6],[SEGFROM_7],[SEGFROM_8],[SEGFROM_9],[SEGFROM_10],[SEGTO_1],[SEGTO_2],[SEGTO_3],[SEGTO_4],[SEGTO_5],[SEGTO_6],[SEGTO_7],[SEGTO_8],[SEGTO_9],[SEGTO_10]) SELECT [REPORTID],[COLNUM],[CLTKNCNT],[COLTYPE],[COLSIZE],[COLOMCNT],[COLOFMRK_1],[COLOFMRK_2],[COLOFMRK_3],[COLOFMRK_4],[HIDEFLAG],[TEXTVALU],[STPERIOD],[ENDPEROD],[AMNTFROM],[HISTYEAR],[BUDID],[PRTSIGN],[PRTCOMMA],[PRTPCENT],[PRTTEXT],[ROUNDOPT],[HEADALIN],[HDFTFMLY],[HDFTSIZE],[HEDSTYLE_1],[HEDSTYLE_2],[HEDSTYLE_3],[HEDSTYLE_4],[HEDSTYLE_5],[HEDSTYLE_6],[HEADTYPE_1],[HEADTYPE_2],[HEADTYPE_3],[HEADTYPE_4],[HEADTYPE_5],[HEADTYPE_6],[HEDFRMAT_1],[HEDFRMAT_2],[HEDFRMAT_3],[HEDFRMAT_4],[HEDFRMAT_5],[HEDFRMAT_6],[HEADOPT_1],[HEADOPT_2],[HEADOPT_3],[HEADOPT_4],[HEADOPT_5],[HEADOPT_6],[HEADOPT2_1],[HEADOPT2_2],[HEADOPT2_3],[HEADOPT2_4],[HEADOPT2_5],[HEADOPT2_6],[COLHDNG_1],[COLHDNG_2],[COLHDNG_3],[COLHDNG_4],[COLHDNG_5],[COLHDNG_6],[COLHDNG2_1],[COLHDNG2_2],[COLHDNG2_3],[COLHDNG2_4],[COLHDNG2_5],[COLHDNG2_6],[ALGNOFST],[COLEXPER],[NOTEINDX],[SEGFROM_1],[SEGFROM_2],[SEGFROM_3],[SEGFROM_4],[SEGFROM_5],[SEGFROM_6],[SEGFROM_7],[SEGFROM_8],[SEGFROM_9],[SEGFROM_10],[SEGTO_1],[SEGTO_2],[SEGTO_3],[SEGTO_4],[SEGTO_5],[SEGTO_6],[SEGTO_7],[SEGTO_8],[SEGTO_9],[SEGTO_10] FROM [Source].DBO.AF40103

INSERT INTO [Destination].DBO.AF40104 ([REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]) SELECT [REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10] FROM [Source].DBO.AF40104

INSERT INTO [Destination].DBO.AF40105 ([REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]     ) SELECT [REPORTID],[CLCOLNUM],[TKNODNUM],[TKNTYPE],[TKNVALUE],[TKNDLVAL],[TKNUNACT_1],[TKNUNACT_2],[TKNUNACT_3],[TKNUNACT_4],[TKNUNACT_5],[TKNUNACT_6],[TKNUNACT_7],[TKNUNACT_8],[TKNUNACT_9],[TKNUNACT_10]FROM [Source].DBO.AF40105

INSERT INTO [Destination].DBO.AF40106 ([REPORTID],[ROWNUMBR],[TOTKNCNT],[ROWTYPE],[ROWSIZE],[ROLUPFLG],[ROWDESC],[SUBSUDID],[TYPCLBAL],[CATNUMBR],[PRTSIGN],[PRTHEDER],[CENTHEDR],[ROWFTFAM],[ROWFTSIZ],[ROWSTYLE_1],[ROWSTYLE_2],[ROWSTYLE_3],[ROWSTYLE_4],[ROWSTYLE_5],[ROWSTYLE_6],[ROFMRKIN_1],[ROFMRKIN_2],[ROFMRKIN_3],[ROFMRKIN_4],[ROFMRKIN_5],[ROFMRKIN_6],[ROFMRKIN_7],[ROFMRKIN_8],[ROFMRKIN_9],[ROFMRKIN_10],[ROFMRKIN_11],[ROFMRKIN_12],[ROFMRKIN_13],[ROFMRKIN_14],[ROFMRKIN_15],[ROFMRKIN_16],[ROFMRKIN_17],[ROFMRKIN_18],[ROFMRKIN_19],[ROFMRKIN_20],[ROFMRKIN_21],[ROFMRKIN_22],[ROFMRKIN_23],[ROFMRKIN_24],[ROFMRKIN_25],[ROFMRKIN_26],[ROFMRKIN_27],[ROFMRKIN_28],[ROFMRKIN_29],[ROFMRKIN_30],[ROFMRKIN_31],[ROFMRKIN_32],[ROFMRKIN_33],[ROFMRKIN_34],[ROFMRKIN_35],[ROFMRKIN_36],[ROFMRKIN_37],[ROFMRKIN_38],[ROFMRKIN_39],[ROFMRKIN_40],[CFLOSCTN],[RWEXPERR],[NOTEINDX],[STTACCT_1],[STTACCT_2],[STTACCT_3],[STTACCT_4],[STTACCT_5],[STTACCT_6],[STTACCT_7],[STTACCT_8],[STTACCT_9],[STTACCT_10],[ENDACCT_1],[ENDACCT_2],[ENDACCT_3],[ENDACCT_4],[ENDACCT_5],[ENDACCT_6],[ENDACCT_7],[ENDACCT_8],[ENDACCT_9],[ENDACCT_10]) SELECT [REPORTID],[ROWNUMBR],[TOTKNCNT],[ROWTYPE],[ROWSIZE],[ROLUPFLG],[ROWDESC],[SUBSUDID],[TYPCLBAL],[CATNUMBR],[PRTSIGN],[PRTHEDER],[CENTHEDR],[ROWFTFAM],[ROWFTSIZ],[ROWSTYLE_1],[ROWSTYLE_2],[ROWSTYLE_3],[ROWSTYLE_4],[ROWSTYLE_5],[ROWSTYLE_6],[ROFMRKIN_1],[ROFMRKIN_2],[ROFMRKIN_3],[ROFMRKIN_4],[ROFMRKIN_5],[ROFMRKIN_6],[ROFMRKIN_7],[ROFMRKIN_8],[ROFMRKIN_9],[ROFMRKIN_10],[ROFMRKIN_11],[ROFMRKIN_12],[ROFMRKIN_13],[ROFMRKIN_14],[ROFMRKIN_15],[ROFMRKIN_16],[ROFMRKIN_17],[ROFMRKIN_18],[ROFMRKIN_19],[ROFMRKIN_20],[ROFMRKIN_21],[ROFMRKIN_22],[ROFMRKIN_23],[ROFMRKIN_24],[ROFMRKIN_25],[ROFMRKIN_26],[ROFMRKIN_27],[ROFMRKIN_28],[ROFMRKIN_29],[ROFMRKIN_30],[ROFMRKIN_31],[ROFMRKIN_32],[ROFMRKIN_33],[ROFMRKIN_34],[ROFMRKIN_35],[ROFMRKIN_36],[ROFMRKIN_37],[ROFMRKIN_38],[ROFMRKIN_39],[ROFMRKIN_40],[CFLOSCTN],[RWEXPERR],[NOTEINDX],[STTACCT_1],[STTACCT_2],[STTACCT_3],[STTACCT_4],[STTACCT_5],[STTACCT_6],[STTACCT_7],[STTACCT_8],[STTACCT_9],[STTACCT_10],[ENDACCT_1],[ENDACCT_2],[ENDACCT_3],[ENDACCT_4],[ENDACCT_5],[ENDACCT_6],[ENDACCT_7],[ENDACCT_8],[ENDACCT_9],[ENDACCT_10] FROM [Source].DBO.AF40106

INSERT INTO [Destination].DBO.AF40107 ([REPORTID] ,[TOTRWNUM],[TKNODNUM],[STROWNUM],[ENDRWNUM]) SELECT [REPORTID] ,[TOTRWNUM],[TKNODNUM],[STROWNUM],[ENDRWNUM] FROM [Source].DBO.AF40107

INSERT INTO [Destination].DBO.AF40108 ([REPORTID],[TOTRWNUM],[MBRWNUM]) SELECT [REPORTID],[TOTRWNUM],[MBRWNUM] FROM [Source].DBO.AF40108

INSERT INTO [Destination].DBO.AF40109 ([FLDPRNAM],[FLDPCTUR]) SELECT [FLDPRNAM],[FLDPCTUR] FROM [Source].DBO.AF40109

--INSERT INTO [Destination].DBO.AF40110 ([USERNAME],[SHGRDFLG],[SHCGRFLG],[SHTBARFL],[SCDEFAFL],[SHRWARFL],[SHOFMKFL],[SNPTGRFL],[SHMARFLG],[SHPGBDFL],[SHRLRSFL] ) SELECT [USERNAME],[SHGRDFLG],[SHCGRFLG],[SHTBARFL],[SCDEFAFL],[SHRWARFL],[SHOFMKFL],[SNPTGRFL],[SHMARFLG],[SHPGBDFL],[SHRLRSFL]  FROM [Source].DBO.AF40110

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

Microsoft Dynamics GP Excel Paste is now Online

 

Production version of Microsoft Dynamics GP Excel Paste is now released and the development team has updated the website to integrate PayPal!

Download Fabrikam copy now or purchase your company version now from the below link:

http://www.dynamicsinnovations.com/GPExcelPaste.aspx

Below is the pricing table for the tool:

Core Financial Modules (GL, CM, DTA)	350$
Accounts Payables and Receivables (AR, AP)	300$
Sales Order Processing (SOP)	400$
Purchase Order Processing (POP, POR, POE)	400$
Inventory Control (IV, IVC)	300$
Payroll (UPR)	300$
Full Package – All Modules	1,300 $

Enjoy!

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

Mismatched Inventory Quantities

 

Sometime you face difficulties in your inventories where your quantities does not match between applications forms, for example the on hand quantity does not match the historical stock status report or the item stock inquiry, and this is really annoying as the inventory reconciliation utility does not fix this and the resolution for this is a nightmare.

Reason behind such an issue might be a packets loss during the transaction posting where the system inserted the transactions in some tables and did not insert into the other, for instance a normal inventory transaction posting affect IV00102 table to adjust quantity, insert records into IV30300 for history and affect IV10200 and IV10201, all these tables must have the same quantity in normal cases, but in some cases you might have differences.

To fix this issue you will need to follow how Dynamics GP works, as a start we know that the reconciliation utility for the inventory items reconciles the quantity of IV00102 based on the difference between Quantity Received and Quantity Sold of IV10200 table, therefore after performing “Reconcile” for your inventories you can be sure that IV00102 is matched to IV10200.

Now the challenge is how to reconcile IV30300 to IV10200, first of all you will need to identify differences, I have created the below script that generate differences in quantity for all your items per each site between IV10200 and IV30300:

SELECT ITEMNMBR                                       AS ITEMNUMBER, 
       LOCNCODE                                       AS LOCATIONCODE, 
       Sum(TRXQTY * QTYBSUOM)                         AS QTYIV30300, 
       (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS QTYIV10200, 
       Sum(TRXQTY * QTYBSUOM) - (SELECT Sum(QTYRECVD - QTYSOLD) FROM   IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS DIFF

FROM   (
--Correct IV30300 To Include Transfers
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST, 
                      TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE <> 3)
UNION ALL
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY * - 1 AS Expr1, UNITCOST, EXTDCOST, 
                      TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE = 3)
UNION ALL
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST, 
                      TRNSTLOC AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE = 3)
) AS  IV30300MODIFIED
WHERE  IV30300MODIFIED.ITEMNMBR IN 

--Select Inventory Items with Type "Sales Invetory" Only
(SELECT ITEMNMBR FROM IV00101 WHERE (ITEMTYPE = 1) )

GROUP  BY ITEMNMBR, 
          LOCNCODE 

HAVING
Sum(TRXQTY * QTYBSUOM) <> (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE)

For the time being you had a list of variances, you will need to identify what figures are really matches your physical, if it was the IV30300, then you will need to create an adjustment using Inventory Transaction Entry to correct IV10200 to match IV30300 and then delete the record from the IV30300, by this the IV10200 will be affected and IV30300 will not. Otherwise if the IV10200 is correct and the issue is in IV30300 then you will need to create a database adjustment in IV30300 to correct the difference and match your IV30300 to the IV10200.

I know that this is not an easy process to be done but I can assure you that above steps can save you several weeks of researches specially if your inventories are loaded with items.

Hope that this helps.

---

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

Mass Item/Vendor Link

 

I got a request from one of my customers to have the ability to link multiple items with a vendor without the need to add them one by one and I have created this using the .Net toolkit, the screen will look like the below:

The user can filter items from the list on the left panel, click on insert and process! The system will link the selected items with the selected vendor!

I am willing to publish this tool soon, will update the article with the download link once doing some fine tuning on the utility.

---

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

Field Level Security is not working!?!?

 

Few months back, one of my clients reported an issue with the field level security module in Dynamics GP that it only works for “Sa”, and this morning I noticed a question in the community reminding me to post about this issue! Below are the circumstances:

1. Only the sa user can see the module on GP even by giving the power user to other users.

2. The restrictions made on the field level security module are applied only to sa user.

Solution clearly shows that this is not an issue with the application and it is an issue with database privileges, simply go to “C:\Program Files\Microsoft Dynamics\GP2010\SQL\Util” and locate a script called “Grant.SQL”, the script will grant the DYNGRP role in SQL the access on all objects in the database and will resolve your issue, below the script if needed:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
    where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
    where type = 'P'  

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor

---

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

How to change SQL Collation for SQL Server 2008 R2

 

Have you ever been in a situation where you wanted to change the SQL Collation to match SQL Database Collation?? Last year I have posted a script that resolves this for SQL Server 2005, but the same syntax is not applicable for SQL 2008 R2, while investigating this I found the correct syntax that need to be used as below:

"E:\SQL\setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS="DAOUD-PC\DAOUD"

E:\SQL\setup.exe will need to be replaced with the locations of the SQL Server 2008 R2 setup.exe path.

MSSQLSERVER represents the default instance, if you have a named instance this must be changed to hold the instance name.

P@ssW0rd: is the new SA user password, it must be strong password or otherwise the repair will fail.

Arabic_CI_AI: is the new collation name.

DAOUD-PC\DAOUD need to be replaced with the local administrator of the server or PC you are installing the SQL on.

Paste above command into CMD and wait for 3-4 minutes, you will have your SQL Collation changed.

Warning: this operation will recreate your master database, were all existing setting will be reset, if you had databases attached it will be de-attached and will be found in the database folder.

---

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

Vendor Statement with Analytical Accounting Information

 

one of my clients is a constructions firm whose not interested to activate the project accounting and wanted to distribute their project management over analytical accounting dimensions.

Therefore we have implemented Dynamics GP and created dimension for projects to distribute all expenses accounts of analytical accounting codes.

One of their requirements been to have a vendor statement per project which is reasonable for such a line of business, therefore I have created the below view that extracts the information including from AP, GL and AA and wanted to share the idea to avoid reinventing the weel for people who works with the same situation, below is the script I used:

DECLARE @FROMDATE    DATETIME
DECLARE @TODATE        DATETIME
DECLARE @FROMVENDOR VARCHAR(MAX)
DECLARE @TOVENDOR    VARCHAR(MAX)

SET @FROMDATE    = '1900-01-01'
SET @TODATE        = '2017-04-17'
SET @FROMVENDOR = 'ACETRAVE0001'
SET @TOVENDOR    = 'BLOOMING0001'

SELECT 
'Beginning Balance'        AS TRXSOURCE, 
@FROMDATE                AS DOCDATE, 
'Beginning Balance'        AS DOCNUMBER, 
VENDORID, 
SUM(CREDIT)                AS CREDIT, 
SUM(DEBIT)                AS DEBIT, 
VENDNAME, 
'Beginning Balance'        AS VCHRNMBR, 
'Beginning Balance'        AS TRXDESC, 
0                        AS JRNENTRY, 
0                        AS aaGLHdrID, 
aaTrxDimCode, 
aaTrxDimCodeDescr,
'' AS LOCNCODE
FROM
(
SELECT DISTINCT 
VENDORTRANSACTION.TRXSOURCE, 
VENDORTRANSACTION.DOCDATE, 
VENDORTRANSACTION.DOCNUMBER, 
VENDORTRANSACTION.VENDORID, 
AAG3000240002.CRDTAMNT AS CREDIT, 
AAG3000240002.DEBITAMT AS DEBIT, 
VENDORTRANSACTION.VENDNAME, 
VENDORTRANSACTION.VCHRNMBR, 
VENDORTRANSACTION.TRXDESC, 
GL2000030000.JRNENTRY, 
dbo.AAG30000.aaGLHdrID, 
dbo.AAG00401.aaTrxDimCode, 
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE

FROM                 
        (SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
        UNION 
        SELECT AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
        ) AS AAG3000240002         
INNER JOIN
        (SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
        UNION 
        SELECT AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003
        ) AS AAG3000340003 
        ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID AND 
        AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID LEFT OUTER JOIN
        dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID         
RIGHT OUTER JOIN
        (SELECT aaGLHdrID, aaGLDistID FROM AAG30001
        UNION
        SELECT aaGLHdrID, aaGLDistID FROM AAG40001
        ) AS AAG3000140001 
        ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID 
RIGHT OUTER JOIN
        dbo.AAG30000 ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID 
RIGHT OUTER JOIN
(
SELECT     
    CASE 
        WHEN dbo.PM20000.DOCTYPE = 1 THEN 'Invoice' 
        WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges' 
        WHEN dbo.PM20000.DOCTYPE = 3 THEN 'Mis Charges' 
        WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return' 
        WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo' 
        WHEN dbo.PM20000.DOCTYPE = 6 THEN 'Payment' 
        END 
AS TRXSOURCE, 
'Open' AS Status, 

    CASE 
        WHEN dbo.PM20000.TRXDSCRN = '' THEN '-' 
        ELSE dbo.PM20000.TRXDSCRN 
        END 
AS TRXDESC, 
dbo.PM20000.DOCDATE, 
dbo.PM20000.VCHRNMBR, 
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,
(SELECT     VENDNAME FROM         dbo.PM00200 WHERE     (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME, 
dbo.PM20000.CURNCYID, 
dbo.PM20000.DOCNUMBR AS DOCNUMBER, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        END, 0) 
AS ORCREDIT, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
        END, 0) 
AS ORDEBIT
FROM         dbo.PM20000 
LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE     (dbo.PM20000.VOIDED <> 1)

UNION ALL

SELECT CASE 
        WHEN dbo.PM30200.DOCTYPE = 1 THEN 'Invoice' 
        WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges' 
        WHEN dbo.PM30200.DOCTYPE = 3 THEN 'Mis Charges' 
        WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return' 
        WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo' 
        WHEN dbo.PM30200.DOCTYPE = 6 THEN 'Payment' 
        END 
AS TRXSOURCE, 
'History' AS Status, 

CASE 
    WHEN dbo.PM30200.TRXDSCRN = '' THEN '-' 
    ELSE dbo.PM30200.TRXDSCRN 
    END 
AS TRXDESC, 
DOCDATE, 
VCHRNMBR, 
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,
(SELECT     VENDNAME FROM         dbo.PM00200 AS PM00200_1 WHERE     (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME, 
CURNCYID, 
DOCNUMBR, 
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 ORCREDIT, 
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 ORDEBIT
FROM         dbo.PM30200
WHERE     (VOIDED <> 1)) AS VENDORTRANSACTION 
LEFT OUTER JOIN dbo.POP30310 
INNER JOIN dbo.POP30300 
ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM 
ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR 
LEFT OUTER JOIN
(SELECT JRNENTRY, ORCTRNUM FROM GL20000 
UNION 
SELECT JRNENTRY, ORCTRNUM FROM GL30000) 
AS GL2000030000  
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON 
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY

WHERE     (VENDORTRANSACTION.VENDORID >= @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID <= @TOVENDOR) AND(AAG3000340003.aaTrxDimID = 3) AND VENDORTRANSACTION.DOCDATE <@FROMDATE
) AS SOURCE
GROUP BY 
VENDORID, 
VENDNAME, 
aaTrxDimCode, 
aaTrxDimCodeDescr

UNION ALL

SELECT DISTINCT 
VENDORTRANSACTION.TRXSOURCE, 
VENDORTRANSACTION.DOCDATE, 
VENDORTRANSACTION.DOCNUMBER, 
VENDORTRANSACTION.VENDORID, 
AAG3000240002.CRDTAMNT AS CREDIT, 
AAG3000240002.DEBITAMT AS DEBIT, 
VENDORTRANSACTION.VENDNAME, 
VENDORTRANSACTION.VCHRNMBR, 
VENDORTRANSACTION.TRXDESC, 
GL2000030000.JRNENTRY, 
dbo.AAG30000.aaGLHdrID, 
dbo.AAG00401.aaTrxDimCode, 
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE
FROM         
(SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
        UNION 
SELECT AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
) AS AAG3000240002 

INNER JOIN
(SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
        UNION 
SELECT AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003) AS AAG3000340003 
ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID 
    AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID 
    AND AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID 
LEFT OUTER JOIN
dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID 
    AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID 
RIGHT OUTER JOIN
(SELECT aaGLHdrID, aaGLDistID FROM AAG30001
UNION
SELECT aaGLHdrID, aaGLDistID FROM AAG40001
) AS AAG3000140001 
ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID 
RIGHT OUTER JOIN dbo.AAG30000 
ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID 
RIGHT OUTER JOIN
(SELECT     CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM20000.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM20000.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'Open' AS Status, 
CASE WHEN dbo.PM20000.TRXDSCRN = '' THEN '-' ELSE dbo.PM20000.TRXDSCRN END AS TRXDESC, dbo.PM20000.DOCDATE, 
dbo.PM20000.VCHRNMBR, 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,
(SELECT     VENDNAME
FROM         dbo.PM00200
WHERE     (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME, dbo.PM20000.CURNCYID, dbo.PM20000.DOCNUMBR AS DOCNUMBER, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) END, 0) AS OrCredit, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) END, 0) AS OrDebit
FROM         dbo.PM20000 LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE     (dbo.PM20000.VOIDED <> 1)
UNION ALL
SELECT     CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM30200.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM30200.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'History' AS Status, 
CASE WHEN dbo.PM30200.TRXDSCRN = '' THEN '-' ELSE dbo.PM30200.TRXDSCRN END AS TRXDESC, DOCDATE, VCHRNMBR, 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,
(SELECT     VENDNAME
FROM         dbo.PM00200 AS PM00200_1
WHERE     (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME, CURNCYID, DOCNUMBR AS DOCNUMBER, 
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 OrCredit, 
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 OrDebit
FROM         dbo.PM30200
WHERE     (VOIDED <> 1)) AS VENDORTRANSACTION LEFT OUTER JOIN
dbo.POP30310 INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR LEFT OUTER JOIN
(SELECT JRNENTRY, ORCTRNUM FROM GL20000 
UNION 
SELECT JRNENTRY, ORCTRNUM FROM GL30000) 
AS GL2000030000 
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON 
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY
WHERE     (VENDORTRANSACTION.VENDORID > = @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID < = @TOVENDOR) AND (AAG3000340003.aaTrxDimID = 3) AND 
VENDORTRANSACTION.DOCDATE > = @FROMDATE AND VENDORTRANSACTION.DOCDATE <= @TODATE
ORDER BY DOCDATE

Hope that this helps.

---

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