Dynamics GP Workflow Issues, Server Error in “/” Application

 

Yesterday I been through a wired issue with Dynamics GP Workflow, after activating Vendors Addition and Purchase Order workflows users were getting the below message when trying to open any workflow:

Server Error in '/' Application.

---

Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed.
Details: To enable the details of this specific error message to be viewable on the local server machine, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "RemoteOnly". To enable the details to be viewable on remote machines, please set "mode" to "Off".

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="RemoteOnly"/> </system.web> </configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="On" defaultRedirect="mycustompage.htm"/> </system.web> </configuration>

I doubled check the web.config and been sure that the web.config in the inetpub folder has the <customErrors mode =”off”>, after doing many researches I found another web.config located in the below path:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS

Which has the <customErrors mode =”on”>, switching off this returned the actual error behind the workflow as shown below:

Server Error in '/' Application.

---

The application encountered an unhandled system exception. Contact your system administrator for details.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: The application encountered an unhandled system exception. Contact your system administrator for details.
Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL: 1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %> or: 2) Add the following section to the configuration file of your application: <configuration> <system.web> <compilation debug="true"/> </system.web> </configuration> Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode. Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:

[SoapException: The application encountered an unhandled system exception. Contact your system administrator for details.] System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +413241 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +300 Microsoft.Dynamics.GP.Proxy.DynamicsGP.GetCurrencyList(CurrencyCriteria criteria, Context context) +98 Microsoft.Dynamics.GP.Formatter.GetCurrencyList(DynamicsGP service, Boolean useHttpUser) +505 Microsoft.Dynamics.GP.Formatter.CacheCurrencyFormatData(String webServiceURL, Boolean useHttpUser) +85 Microsoft.Dynamics.GP.Workflow.DocumentViewer..ctor() +159 ASP._layouts_dynamics_workflow_gp_vendorviewer_aspx..ctor() +18 __ASP.FastObjectFactory_app_web_dynamics_workflow_gp_vendorviewer_aspx_2a428413__lml_jl2.Create_ASP__layouts_dynamics_workflow_gp_vendorviewer_aspx() +31 System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +138 System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +50 System.Web.MaterializeHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +425 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +263

---

Version Information: Microsoft .NET Framework Version:2.0.50727.5448; ASP.NET Version:2.0.50727.5456

That was a new challenge, trying to figure our the resolution behind the new error, doing more researches returned that such an issue are web-services related issues as the workflow was unable to read information from Dynamics GP, you will need the administrator user you are using to run the services to the BDC Administrator role in web-services administration console.

The issue will be resolved!!!

---

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

Error Converting Request to Purchase Order–Business Portal 5.1

 

I have installed Business Portal 5.1 for one of my customers and been through the below error in converting Purchase Request into Purchase Order

The specified Protocol is invalid

Doing researches about this subject returned that the web.config of the business portal might be missing from this path “C:\Program Files\Microsoft Dynamics\Business Portal” and it was!

To resolve this I have connected to another client whose running the business portal with no issues and copied the web.config! It worked perfectly, below is the web.config content:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <system.web>

        <xhtmlConformance mode="Legacy" />

    </system.web>

    
    <appSettings>

    <add key="Protocol" value="1"/>
    <add key="TaxEngineServiceAssembly0" value="Microsoft.Business.Taxes.Services"/>
    <add key="TaxEngineServiceClass0" value="Microsoft.Business.Taxes.TaxEngine"/>

    <add key="TaxEngineDataServiceAssembly0" value="Microsoft.Dynamics"/>
    <add key="TaxEngineDataServiceClass0" value="Microsoft.Dynamics.Common.TaxEngineData"/>

    <add key="TaxEnginePreCalculateDocumentEventAssembly0" value="Microsoft.Dynamics"/>
    <add key="TaxEnginePreCalculateDocumentEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
    <add key="TaxEnginePreCalculateDocumentEventMethod0" value="DocumentPre"/>
    
    <add key="TaxEnginePreGetTaxGroupIDsEventAssembly0" value="Microsoft.Dynamics"/>
    <add key="TaxEnginePreGetTaxGroupIDsEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
    <add key="TaxEnginePreGetTaxGroupIDsEventMethod0" value="PreGetTaxGroupIDs"/>

    <add key="TaxEnginePreCalculateCodeEventAssembly0" value="Microsoft.Dynamics"/>
    <add key="TaxEnginePreCalculateCodeEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
    <add key="TaxEnginePreCalculateCodeEventMethod0" value="PreCalculateCode"/>

    </appSettings>

</configuration>

---

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

Microsoft Dynamics Salary Survey 2012

 

Nigel Frank International would like to invite you to complete our annual survey of global Microsoft Dynamics salaries. The survey will only take a couple of minutes to complete and your response and any personal details will be kept strictly confidential.

Complete the survey by the closing date and you will automatically be entered into our prize draw to win one of five amazing prizes:

1^st Prize = Apple iPad2 16gb with Wi-Fi + 3G

2^nd Prize = Microsoft Xbox 360 250gb + Kinect

3^rd Prize = Kindle Keyboard with Free 3G + Wi-Fi

4^th Prize = Microsoft LifeCam Studio Webcam

5^th Prize = Microsoft Arc Touch Mouse

You will also receive a FREE copy of the Salary Survey report once it has been compiled.

Please click on the following link to complete the Microsoft Dynamics Salary Survey 2012:

http://survey.nigelfrank.com/en/microsoft-dynamics-salary-survey-2012.html

The survey is available in 12 languages for your convenience.

---

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