Dynamics GP Analysis Cubes Error: The column "INTERID" cannot be processed because more than one code page (1252 and 1256) are specified for it

 

I am sure if were in the non-English countries, you might need to use code pages other than CP1252 to support your characters, and 1256 would your right choice for example if you been in Arab speaking countries, but this makes a problem when it comes to Analysis Cubes installation.

Once you install the Analysis Cubes and finish all wizard issues you feel like things are becoming easy and will proceed to start your job getting your cubes inititalized and then you will be start getting below errors:

Results to CompanyMaster Task OLE DB Destination [22]    
Description: The column "INTERID" cannot be processed because more than one code page (1252 and 1256) are specified for it. 
End Error 

Error: 2015-03-23 10:18:58.26    
Code: 0xC02020F4    
Source: Copy Data from Results to CompanyMaster Task OLE DB Destination [22]    
Description: The column "CMPNYNAM" cannot be processed because more than one code page (1252 and 1256) are specified for it. 
End Error 

Warning: 2015-03-23 10:18:58.26    
Code: 0x800470C8    
Source: Copy Data from Results to CompanyMaster Task OLE DB Destination [22]    
Description: The external columns for component "OLE DB Destination" (22) are out of synchronization with the data source columns. The external column "CompanyID" needs to be updated.  The external column "CompanyName" needs to be updated. 
End Warning 

Error: 2015-03-23 10:18:58.26    
Code: 0xC004706B    
Source: Copy Data from Results to CompanyMaster Task SSIS.Pipeline    
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN". 
End Error 

Progress: 2015-03-23 10:18:58.26    
Source: Copy Data from Results to CompanyMaster Task     
Validating: 50% complete 
End Progress 

Error: 2015-03-23 10:18:58.26    
Code: 0xC004700C    
Source: Copy Data from Results to CompanyMaster Task SSIS.Pipeline    
Description: One or more component failed validation. 
End Error 

Error: 2015-03-23 10:18:58.26    
Code: 0xC0024107    
Source: Copy Data from Results to CompanyMaster Task     
Description: There were errors during task validation. 
End Error 

Error: 2015-03-23 10:18:58.26    
Code: 0xC00220E4    
Source: Run Company     
Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.  . 
End Error 

Warning: 2015-03-23 10:18:58.26    
Code: 0x80019002    
Source: DynamicsGP_TWO_to_DynamicsGPWarehouse_Package_Master     
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 
The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1). 
Started:  10:18:57 AM 
Finished: 10:18:58 AM 
Elapsed:  0.842 seconds. 
The package execution failed. 
The step failed.

Obviously it is all about the first error, different code pages are exist and therefore your data cannot be copied from the production database to the data warehouse, going further with investigations I been able to get around this error by unifying the code page in my packages, and this is by simply allowing a flag in cubes SSIS packages to use the default code page “AlwaysUseDefaultCodePage” to be “True” and your issues will no longer be shown:

Now the challenge here you need to extract the packages from the MSDB store, to your Visual Studio and perform this change, save your package and re-import this back to your MSDB, this will need to be done to the following packages:

DynamicsGP_DynamicsGPWarehouse_FiscalPeriods.dtsx
DynamicsGP_DynamicsGPWarehouse_GLBudgets.dtsx
DynamicsGP_DynamicsGPWarehouse_GLMDAGroups.dtsx
DynamicsGP_DynamicsGPWarehouse_GLMDATransactions.dtsx
DynamicsGP_DynamicsGPWarehouse_GLTransactions.dtsx
DynamicsGP_DynamicsGPWarehouse_ItemCurrentQuantity.dtsx
DynamicsGP_DynamicsGPWarehouse_ItemMaster.dtsx
DynamicsGP_DynamicsGPWarehouse_PendingPurchaseOrders.dtsx
DynamicsGP_DynamicsGPWarehouse_PendingSalesOrders.dtsx
DynamicsGP_DynamicsGPWarehouse_PurchaseOrderDetail.dtsx
DynamicsGP_DynamicsGPWarehouse_SalesDetail.dtsx
DynamicsGP_DynamicsGPWarehouse_SalesPerson.dtsx
DynamicsGP_DynamicsGPWarehouse_SalesTerritory.dtsx
DynamicsGP_DynamicsGPWarehouse_TotalExpense.dtsx
DynamicsGP_DynamicsGPWarehouse_TotalRevenue.dtsx
DynamicsGP_DynamicsGPWarehouse_AgingDetails.dtsx
DynamicsGP_DynamicsGPWarehouse_AgingPeriodAmounts.dtsx
DynamicsGP_DynamicsGPWarehouse_AgingPeriods.dtsx
DynamicsGP_DynamicsGPWarehouse_CheckbookRegister.dtsx
DynamicsGP_DynamicsGPWarehouse_CompanyMaster.dtsx

Luckily, I found a script that makes this easier, it allows you to extract all your SSIS packages from MSDB to file system instead of pulling these one by one, it will save the packages to a folder on your “C:\” drive, and all what you need to do then is copying these to your visual studio and start changing the flag, below the script:

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename')
    -- if you need to really make this generic
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;"C:\Packages\' + P.PackageName +'.dtsx"' AS cmd
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the
-- native Data Collector packages
-- WHERE
--     F.FolderPath <> '\Data Collector'

Once you run this on your SQL Server the result will be set of commands like the below, run these commands using CMD and go to find these in your C:\Packages folder:

dtutil /sourceserver SERVERNAME /SQL "\DynamicsGPWarehouse\Package" /copy file;"C:\Packages\Package.dtsx"

Execute these over CMD and find your files on C:\ drive, select all and paste the files inside your Visual Studio and start your modifications, once done import back updated packages to SQL Integration Services one by one and rerun your job.

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

No Print for Purchase Requisitions!!

When you see the window print button you expect that the information of the window you are on is being printed on a report writer report, and when one of your customers inform you that no action is being taken upon pressing the report you just feel like the user is not aware whats going on and a privileges  issue should take place!

Well for Purchase Requisition Entry form this is not the case! the window print is not linked to any of the available reports, clicking the button will do nothing and you will feel a little bit confused about what to be done!

The button indeed does not have a report assigned, but you can design your report on SSRS and link it to this button! Just click the Small Arrow near the print button and hit “Assign Reports”, then select the report you need to link, see the below for clarification:

---

Regards,

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

Management Reporter CU10 is available

For customers with complicated reports and huge sizes, you need to consider the upgrade to this release of Management Reporter, noticeable improvements has been made to fasten up the report generation process, along with many more features found below:

http://blogs.msdn.com/b/dynamics_financial_reporting/archive/2014/09/30/management-reporter-2012-cu-10-now-available.aspx

---

Regards,

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

Management Reporter 2012 Process Service Not being Started - The time zone ID 'Jerusalem Standard Time' was not found on the local computer.

Few days back I got a very wired case from one of my customers, the reports they are generating are not being processed and locked in the “Queued” state, which normally caused due to the fact that Management Reporter Services are not running, moving to the services I have noticed that the “Management Reporter 2012 Process Service” is not running and unable to be started.

I moved to the Event Viewer trying to find the cause of the issue and found the following wired error:

Service cannot be started. System.TimeZoneNotFoundException: The time zone ID 'Jerusalem Standard Time' was not found on the local computer.
   at System.TimeZoneInfo.GetTimeZone(String id)
   at System.TimeZoneInfo.FindSystemTimeZoneById(String id)   at Microsoft.Dynamics.Common.Scheduler.GenericScheduler.ExtensionMethods.ToScheduler(Trigger taskTrigger)
   at Microsoft.Dynamics.Common.Scheduler.GenericScheduler.Scheduler.SchedulerTask.CreateFromDataTask(Task task, Guid schedulerId)
   at Microsoft.Dynamics.Common.Scheduler.GenericScheduler.Scheduler.LoadTasks()
   at Microsoft.Dynamics.Common.Scheduler.GenericScheduler.Scheduler.OnHeartbeat()
   at Microsoft.Dynamics.Common.Scheduler.GenericScheduler.Scheduler.StartScheduler()
   at Microsoft.Dynamics.Performance.Reporting.Scheduler.SchedulerService.OnStart()
   at Microsoft.Dynamics.Performance.Common.ServiceComponent.StateChangeHandler(RequestedStateChange requestedState)
   at Microsoft.Dynamics.Performance.Common.ServiceComponent.<Load>b__0(RequestedStateChange s)
...

Tried to search everywhere looking what could cause this with no luck, that was indeed a very wired error that does not have any explanation.

I tried to search the web on the location where the windows saves the times zones to see what’s going on and noticed that it is stored in the windows registry under the following path:

HKLM\Software\Microsoft\Windows NT\CurrentVersion\Time Zones\

Tried to find “Jerusalem Standard Time” in the list with no luck, but noticed that there is a new time zone called “Israel  Standard Time” that in-fact replaced the “Jerusalem Standard Time”.

I renamed the folder back to “Jerusalem Standard Time” and the service started properly, it looks like folks at Microsoft has changed this for political reasons and forgot to apply the change correctly!

---

Regards,

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

Management Reporter CU9 does not support windows XP

One of my customers recently upgraded his environment to SQL 2012 and GP 2013 from windows 2003 and GP 9.0, upon installing Management Reporter with CU 9.0 that supports SQL 2012 I have realized that it cumulative update no longer supports windows XP, therefore I had to rollback my installation and install CU8 then re-imported my reports.

Published to let you know before planning any upgrades!

---

Regards,

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

Maps Reporting using SSRS

One of the challenges that I been trying to achieve is having my reports graphically designed to show data on “Maps” and that always been the toughest part customers might request.

Few days back I been researching with my colleagues on how the “Sales By States” KPI works in Dynamics GP that dynamically pulls the sales of each state on the US map and discovered the “Map” feature in SSRS!

We have been able to download the “.shp” file for Jordan map from one of the online free GIS providers and integrated this will SSRS, and then created the following report in SSRS in few minutes, saved this report in SSRS and been able to print this report from GP business analyzer:

This article is just to show that this is doable for users to give it a try.

Happy reporting!

---

Regards,

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

Analytical Accounting Budgets not Pulled on Management Reporter while filters is enabled.

I got a very interesting case few months back, one of my customers implemented the Management Reporter and tried to implement their existing reports, it went well but for the reports that uses the Analytical Accounting Budgets that has filters over specific dimensions, budget amounts were not pulled at all.

At that time I tried very hard to resolve the issue with no luck, and therefore I had to install the FRx and work out the reports there till getting this resolved by Microsoft.

During the last days I have noticed many articles by Microsoft that they has identified the case and fixed this issue with the product updates, therefore this morning I decided to install Management Reporter 2012 with the final update to see if the issue was resolved, I created a dummy report and tested the case with no luck! Same issue is still there while I couldn’t replicate the same issue on a clean database.

After many researches I have seen a question answered in the community and located here, it is the same issue I am currently investigating, and the provided cause of issue is the gap in serials for “Analytical Accounting Transaction Dimension IDs”, simply the data inside AAG00400 table should look like the below:

What if I been having one of the dimensions coded as “15” instead of “7” as the case I had? The proposed solution was to create dummy dimensions to cover the gap between 9 and 15 which is doable and safe solution.

The problem is what if I had one of the dimensions coded as “700”! I am sure that there is no was to create 684 dummy dimensions to cover the gap where I had to reindex the whole database.

The operation I followed is very risky and requires a real expert in the SQL, below steps I used to fix my issue:

1. I searched for the tables that I need to affect, simply these are all the tables that has “aaTrxDimID” column, went to the SQL and wrote the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Column_Name = 'aaTrxDimID' AND COLUMN_DEFAULT IS NOT NULL

2. I didn’t take a backup of the database but you must take a full backup!

3. I took a backup for the tables that I need to update by running the following query:

SELECT * INTO BAK_AAG00401 FROM AAG00401

SELECT * INTO BAK_AAG00901 FROM AAG00901

SELECT * INTO BAK_AAG01001 FROM AAG01001

SELECT * INTO BAK_AAG01002 FROM AAG01002

SELECT * INTO BAK_AAG02000 FROM AAG02000

SELECT * INTO BAK_AAG02001 FROM AAG02001

SELECT * INTO BAK_AAG40003BAK FROM AAG40003BAK

SELECT * INTO BAK_AAG30003BAK FROM AAG30003BAK

SELECT * INTO BAK_AAG04001 FROM AAG04001

SELECT * INTO BAK_AAG20003 FROM AAG20003

SELECT * INTO BAK_AAG10003 FROM AAG10003

SELECT * INTO BAK_AAG00202 FROM AAG00202

SELECT * INTO BAK_AAG30003 FROM AAG30003

SELECT * INTO BAK_AAG00312 FROM AAG00312

SELECT * INTO BAK_AAG00400 FROM AAG00400

SELECT * INTO BAK_AAG00316 FROM AAG00316

SELECT * INTO BAK_AAG00801 FROM AAG00801

SELECT * INTO BAK_AAG40003 FROM AAG40003

SELECT * INTO BAK_AAG00402 FROM AAG00402

SELECT * INTO BAK_AAG00403 FROM AAG00403

SELECT * INTO BAK_AAG00404 FROM AAG00404

SELECT * INTO BAK_AAG00405 FROM AAG00405

SELECT * INTO BAK_AAG00406 FROM AAG00406

SELECT * INTO BAK_AAG00407 FROM AAG00407

4. I have identified what are the existing IDs and what it should be and ran the following set of commands for each record:

UPDATE AAG00401 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00901 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG01001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG01002 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG02000 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG02001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG40003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG30003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG04001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG20003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG10003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00202 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG30003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00312 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00400 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00316 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00801 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG40003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00402 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00403 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00404 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00405 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00406 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

UPDATE AAG00407 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

Viola! The budgets are now generating data and everything looks fine for the time being, I will keep you posted with any other issues that might occur due to this operation.

---

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

Printing Barcodes for Fixed Assets

One of my customers has requested to prints barcodes for his assets, and therefore I have created this simple methodology, take a look into the steps below:

Go to the Asset General Information form (Cards >> Fixed Assets >> General), and note the Additional menu on the form:

The system will prompt you to select the printer:

Barcode will be printed!

To get this please go and download the following ZIP file:

http://di.jo/files/Asset Label Printing.zip

It has two files:

• “IDAutomationHC39M.ttf”: place this file inside “C:\Windows\Fonts”
• “Asset Label Printing.dll”: Place this file inside your Dynamics GP Folder >> AddIns

NOTE: we have added a little DI.JO inside the barcode, just e-mail me to send you a package without this, it was added to count the number of users!

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

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

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

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