Dynamics GP - Customer Internet Information Table

Customer Internet Information Table - SY01200


To join to the Customer Master - RM00101




SELECT    * from RM00101 LEFT OUTER JOIN
                      dbo.SY01200 ON dbo.RM00101.CUSTNMBR = dbo.SY01200.Master_ID AND dbo.RM00101.ADRSCODE = dbo.SY01200.ADRSCODE


You can do a straight sql insert or table import into this table.

Windows 8 - Cannot boot

Issues:
Windows 8 is not booting up
Right after the dell logo, the screen goes grey or black and moves no further
All attempts to switch the bios or boot from the dvd are unsuccessful


Resolution:

• Create a boot USB drive
• Get a 4 gb-ish usb drive
• Format it as Fat32
• Download the Windows 8 ISO from microsoft or wherever you bought it
• Extract the ISO to the USB Drive
• Configure the machine to boot from the usb drive
• Turn on the machine
• Keep pressing F12
• Go to Setup
• Go to boot
• Disable secure boot
• disable legacy option
• Set boot list to UEFI
• Stick the USB drive into the machine
• Reboot the machine
• It should detect Windows 8 on the usb drive, and boot up
• From here you can choose to repair and refresh or reinstall windows 8

Connect SQL to Filemaker via Linked Server

Use 32 bit SQL

Step1 - Download/Install the Filemaker ODBC driver for your version of Filemaker
http://www.filemaker.com/support/downloads/?csr=support_downloads_new

Step2 - Configure the Filemaker Databases for sharing according to chapter 4 in the documentation
http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf

• Enable Sharing
• Enable Multi-User
• Enable Remote Access
• Add a "maximum number of characters" in the text field validation option settings in the Filemaker database. Set it to 255.

Step3 - Create ODBC connection to Filemaker using Filemaker ODBC driver

• Admin tools>ODBC>System DSN>New> Create a connection, call it "FM"

Step4 - Create Linked server in SQL

• Provider is Microsoft OLE DB  Provider for ODBC
• Product name is Filemaker
• Data Source is Your ODBC name "FM"
• Under security, enter the Filemaker Admin username and password

Although you can now connect to the database via linked server, none of the tables can be browsed or scripted normally.

You must use

select * from openquery(FM, 'select * from "table name with spaces"')

to select any data.

Filemaker uses double quotes " " instead of [ ] to enclose table names

Dynamics GP - SQL Views - Purchase Requisition, PO, Receipt

SELECT
                      DYNAMICS.dbo.ReqMgmtLines.CompanyID, DYNAMICS.dbo.SY01500.CMPNYNAM, DYNAMICS.dbo.ReqMgmtLines.DocumentID AS ReqID,
                      DYNAMICS.dbo.ReqMgmtDocument.CreatedDate AS ReqCreatedDate, DYNAMICS.dbo.ReqMgmtDocument.FinalApprovalDate AS ReqApprovedDate,
                      DYNAMICS.dbo.ReqMgmtLines.PurchaseOrderDocument AS PONumber, dbo.POP10100.DOCDATE AS PODate, dbo.POP30310.POPRCTNM AS RctNumber
FROM         DYNAMICS.dbo.ReqMgmtLines INNER JOIN
                      DYNAMICS.dbo.SY01500 ON DYNAMICS.dbo.ReqMgmtLines.CompanyID = DYNAMICS.dbo.SY01500.CMPANYID LEFT OUTER JOIN
                      DYNAMICS.dbo.ReqMgmtDocument ON DYNAMICS.dbo.ReqMgmtLines.DocumentID = DYNAMICS.dbo.ReqMgmtDocument.DocumentID LEFT OUTER JOIN
                      dbo.POP10100 ON DYNAMICS.dbo.ReqMgmtLines.PurchaseOrderDocument = dbo.POP10100.PONUMBER LEFT OUTER JOIN
                      dbo.POP30310 ON DYNAMICS.dbo.ReqMgmtLines.PurchaseOrderDocument = dbo.POP30310.PONUMBER

Dynamics GP - Business Portal Purchase Requisition Tables

Business Portal Tables are in the DYNAMICS database

ReqMgmtDocument - Requisition Header
ReqMgmtLines - Contains line item details AND the GP PO number it was transferred to

Dynamics GP - AP in foreign currency - Applying non-functional currency payment to non-functional currency invoice

Scenario:
The currencies used are just for example, and could be any currency.
Functional Currency: US
Currency1: UK
Currency2: EURO

Customer's invoice is done in UK. Customer pays in EURO. We want to apply the EURO payment to the UK invoice.

The problem is that when you select the EURO payment in the apply sales documents window, invoices that are of a different currency are not displayed in the apply-to window.


Solution: (Only works in GP 2013. Older versions will not display invoices in currencies different from the payment currency)
Use Euro Relationships
This will allow you to define relationships between non-functional currencies

1. All users must be out of the system
2. Grant company access to the respective currencies
3. Define Euro Relationships
1. Tools>Setup>System>Euro Relationships
2. Setup the rates for your currency relationships
4. Enable Euro Relationships
1. Tools>Utilities>Company>Enable Euro
2. You will be prompted to 
1. Post or remove all batches with trx containing the affected currencies
2. Post or remove all Bank Deposits for Checkbooks in affected currencies
3. Post or remove all Bank Deposits without receipts
4. Reconcile all Bank Statements
3. After you complete the above tasks, tick the currency to enable, and click process
4. From this point on, you will be able to create invoices in EURO, Payments in UK, and apply them in the apply screen (you still have to post them first)
1. When you create the invoices, a new Exchange rate Entry Zoom with triangulation will open to allow you to define a rate for the currency relationships
2. Any EURO invoices you created BEFORE enabling Euro Relationships will show an amount remaining of $0.00 and you will not be able to apply UK payments to them (even though the relationships are enabled)
3. The changes will only allow NEW INVOICES AND NEW PAYMENTS to be applied to each other for non-functional to non-functional applications

eOne Smartconnect - Scheduled servers do not show up in the list

Run services.msc
Look for the eOne Smartconnect Service
Start it
Once it starts the first time, the server name will be recorded in the list of schedule servers

Dynamics GP - Users cannot see any GL accounts. SA can still see accounts.

The account security box is ticked under the company setup.

Account security allows you to setup specific access to specific accounts for users. If this is enabled iwth nothing setup, no users will see any accounts (except sa)

How to Remote control Android tablet from PC

Teamviewer Quick Support - www.teamviewer.com

Dynamics GP 2013 Upgrade Error - The conversion process encountered an error and the temporary table did not get removed

During the upgrade process, dynamics GP shows errors or warnings for a few tables
In the details of the error, GP Utilities suggests "Remove the temporary table at the database level"
If you delete the tables, GP Utilities throws errors when it tries to upgrade the company again.

• I have seen this happen when you make backup copies of tables that happen to be the same name as the temporary tables.
• Delete any extra backup tables you may have  that pertain to the tables listed as giving trouble to update, then run the update again

I experienced issues with these tables (there was no data in any of them)

• CM00103 Checkbook EFT format header
• SVC00998 SVC_Contract_Line
• SVC00601 SVC_Master_Default

Solution:
If the tables are empty, and they just refuse to upgrade, you can copy the table structure from another database that has successfully upgraded, or from a clean install of the new version.

• Use SQL management studio to make a "drop and create" script for the table in your upgraded database.
• Change the name of the database after "Use" to your old db
• Run the create script to create the new version of the table in your old db
• Re-run dynamics. There should be no issues, or the upgrade should complete with no errors.

Also, see this technique
http://community.dynamics.com/gp/f/32/t/78980

1) Ran the failed table script to verify what failed:
SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber AND a.PRODID = b.PRODID
JOIN
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15
2) Then ran:
delete DYNAMICS..DU000030 WHERE Status <> 0 and Status <>15
3) Then ran:
delete dynamics..duLCK
4) Opened the Dex.ini file and set "Synchronize=TRUE" and saved changes
5) Re-ran GP Utilities and the companies upgraded successfully.

Dynamics GP Analysis Cubes - How to Enable Budgets

1. Download the manual for the Analysis Cubes setup here
2. After you have deployed your cube, navigate to your Data Warehouse database in SQL Management Studio (default name is GPDataWarehouse or somehting)
3. Find the dbo.GLBudgetsetup table
1. Right click, edit 
2. This contains a list of your GP budgets
3. Find the correct budget you want to enable
4. Update the BudgetForecast column to say "Budget"
5. Update the UseForReporting column to say "Yes"
6. Run your synchronization routine to refresh the Data Warehouse (the SQL server agent job)
7. Run the cube population routine to refresh the Cube (the SQL server agent job)

Compare Two Database Schemas

This is a pretty good free tool i've found for comparing databases to determine if any tables didn't upgrade properly


http://opendbiff.codeplex.com/

Dynamics GP 2013 Upgrade Error - Product 0 does not support upgrading from version

Each service packed version of 2010 requires a specific service packed version of 2013 to upgrade to
Check the list here
https://mbs.microsoft.com/customersource/northamerica/GP/support/hot-topics/HOT_TOPIC_MDGP2013Upgrade

This error occurs if you are on the wrong service pack level to allow for upgrading to the next version.
Confirm your upgrade path by checking customersource.
This error can also happen if you are testing an upgrade on a server with a different name from the original server.


To confirm this, run the following SQL scripts

• This will show you the versions of each company.
• Ignore company -1, that's Fabrikam
• SELECT * FROM dynamics.dbo.du000020 where prodid = 0

• To find out the server name run this
• SELECT @@servername
• If it is showing your original production server name, and not your test server name, you will have to update it
• Run the following to set the name to the current test server
• exec sp_dropserver 'oldserver'
• exec sp_addserver 'newserver', 'local'
• Restart SQL server, and check the servername again to confirm it is correct

It is also possible you have "ghost" companies in your Dynamics that do not exist.
To clean these out, run the Clear Companies script on Partnersource
http://support.microsoft.com/kb/855361

If moving from GP 10, upgrade to version 10.00.1868
This link is for 10.00.1868
https://mbs.microsoft.com/partnersource/downloads/taxupdates/usgpye10.htm?printpage=false


Error: Number of results columns does not match table definition
This can also occur if not all of GP's modules were correctly updated from the last upgrade. For example, if a company used AA in GP 10, then stopped using it, but upgraded to GP2010 without installing the AA module for GP2010, the AA tables would never get upgraded, resulting in piece of the database to be at a lower version than the rest of the database.

Generally, this error means you have missed out an upgrade, or a service pack somewhere.
You need to restore your DYNAMICS and company back to a version that was properly upgraded.
(It is not possible to downgrade)
Ensure all of the correct modules are selected.
Run the most recent service pack for that version, then upgrade to the next level, keeping an eye out for errors and resolving them immediately before moving on to any other service packs.

Dynamics GP - Upgrade from GP10 to GP2013

Before Upgrade

• Run full check links, reconcile
• Export all customizations to a package file
• Backup client, dictionaries, databases

Complete List can be found here

http://community.dynamics.com/gp/b/dynamicsgp/archive/2013/04/18/microsoft-dynamics-gp-2013-is-upgraded-what-39-s-next.aspx#.UXU32LV-q6I

Dynamics GP 2013/2015/2016 - How to Install the Web Client - Web Components, Web Services

https://community.dynamics.com/gp/b/dynamicsgpthoughts/archive/2016/12/07/dynamics-gp-2015r2-web-client-installation-steps

• Pre-Requisites
• Remote Credentials to the machine where the WebClient server will be installed
• Remote Credentials to the SQL server and GP servers
• Access to the GP install media
• Access to the OS install media
• Domain Administrator access
• SA access to SQL
• An administrative service account to be used for any windows services required
• A WebclientAdmin AD group
• A WebClientUsers AD group
• On the Server
• Enable Web Client in Add\Remove Programs>GP Modules
• Login to GP and setup a default printer under named printers
• Create WebClientUser group, assign all admin accounts
• Create WebClientAdmin group, assign all admin accounts
• Refer to the documentation for a list of Windows Features to add as prerequisites
• A lot of them are not ticked by default
• http://www.microsoft.com/en-us/download/details.aspx?id=34672
• Windows Server 2008 R2
• Select the role services to install. The following items must be marked:
• In Common HTTP Features:
• Static Content
• Default Document
• In Application Development:
• ASP.NET
• In Security:
• Windows Authentication
• Windows Server 2008 - Does not recognize ASP.net 4.5
• Download and install dot net 4.5
• Open a Command Prompt with Administrative permissions and type the following and then hit Return:
• cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
• Then type the following and hit Return:
• aspnet_regiis –i
• If this does not work, manually browse ot the location, create a shortcut to aspnet_regiis and add the -i flag then run it manually
• Windows Server 2012
• Open the Server Manager.
• Click Manage >> Add Roles and Features.
• In the Add Roles and Features Wizard, click Next.
• Choose Role-based or feature-based installation, and then click Next.
• Select your server from the server pool, and then click Next.
• Mark Web Server (IIS) and then click Next.
• Select the features to install. Be sure that you mark ASP.NET 4.5. 
• In the WCF Services group under .NET Framework 4.5 Features, be sure that you have marked HTTP Activation.
• Click Next.
• The screen for the Web Server Role (IIS) is displayed. Click Next.
• Select the role services to install for the web server. The following items must be marked:
• In Common HTTP Features:
• Static Content
• Default Document
• In Security:
• Windows Authentication
• In Application Development:
• ASP.NET 4.5
• Click Install
• After the installation is complete, click Close.
• Install GP 2013/2015/2016
• Install Web Client Runtime in the list of GP modules during install to enable the Web Client functionality
• Launch Dynamics, Run GP 2013, log in normally
• Create Self Signed Certificate (Use the external public address, not the internal private address)
• http://www.azurecurve.co.uk/2012/09/how-to-install-the-microsoft-dynamics-gp-2013-beta-web-client-installing-a-self-signed-certificate-in-iis/
• DO NOT USE THE DEFAULT SITE
• Export Certificate and store in install folder
• Run>MMC>add snap-in>Certificates>Local Computer
• Personal>Certificates>Select Certificate>Right Click>All Tasks>Export
• Include Private Key>PFX>Enter Password
• 
  
• Setup Website on IIS
• Create name as GP
• Select a content directory
• Open IIS manager, right click on sites, add site
• Select Binding Type: https
• Leave host name blank
• Enter a specific user to connect as
• Create binding, Assign certificate to website
• Ensure you are using an unused port
• Click on Website>SSL Settings>Tick Require SSL
• Run the Web Client Installer from install menu
• Run C:\Program Files\Microsoft Dynamics\GP Web Components\GPweb\DynamicsGPTrustedApp.msi
• Navigate to site
• https://yourserver:443/GP
• https://mypc.mydomain.com/GP/Account/LogOn
• Management console
• https://yourserver:443/WebManagementConsole/
• It will prompt to install silverlight, install it
• Even if it doesn't, you HAVE to install this, go get it
• Install all gp customizations
• Point to shared dictionary

• Related Errors
• An Unexpected error occurred
• Restart all the GP Web Services
• Disable the enhanced Security for IE, or else you will get anti-forgery cookie errors in the event viewer that prevent you from logging in to the web client
• You can confirm this by logging in with Firefox with no issues
• The Page you requested could not be found
• Install silverlight
• Certificate Errors
• Go back and recreate the certificate, then run the GP Web Client install to repair the installation and use the new certificate
• Click on the certificate error and install the certificate to the Trusted Certification Authorities folder
• Asynccallbackexception
• Install the certificate on the client machine you are connecting from
• If you get any errors connecting, review the following
• Open all ports for internally and externally for the web Client, session Service and Runtime Service (48650,48651,48652)
• Ensure you installed all the Windows Features outlined in the doc
• Ensure you installed the Web Client module in the GP install (Not required on client if no GP installed)
• Ensure you have access to download and install silverlight
• If you get an error saying the certificate is for a different site, make sure you're using the full address of the computer yourserver.yourdomain.com
• Unexpected error : check to ensure the gp session central service has started correctly, reboot
• An error occurred while initializing communication with the server.
• Run C:\Program Files\Microsoft Dynamics\GP Web Components\GPweb\DynamicsGPTrustedApp.msi
• If you are trying to uninstall a component, and you are getting error messages, try this tool
• https://support.microsoft.com/en-us/help/17588/fix-problems-that-block-programs-from-being-installed-or-removed
• Delete all GP Web folders in C:\Program Files\Microsoft Dynamics folder
• Ensure you are running as a domain admin, and connected to the domain

• Upgrading to GP 2015 R2 Web Client
• I was unable to get the upgrade to work correctly
• Instead, I had to uninstall the old web client
• This gave trouble to uninstall, i eventually used this tool to get rid of the old Web Client install
• https://support.microsoft.com/en-us/mats/program_install_and_uninstall
• Delete the databases manually
• Delete the Website manually
• Reinstall the new Web Client

SSRS - Data is showing up as Skype Phone Numbers in SSRS Reports

This is Skype’s Internet Explorer “Click to Call” plugin that is incorrectly interpreting the report data as a telephone number.

The “Click to Call” plugin is an addon that comes enabled by default with the newer versions of Internet Explorer.

To disable it, you will have to

• Open Internet Explorer
• Click on the Gear in the top right hand, or go to tools>options
• Go to Manage Add-ons
• Scroll down the list and find the Skype – Click to Call
• Click Disable

·        You will have to do this on all affected workstations

·        This should prevent skype from trying to interpret numbers In the browser

http://www.ehow.com/how_8317962_disable-skype-ie.html

Dynamics GP - AP Historical Aged Trial Balance - Stored Procedure

--This code replicates the AP Historical Aged Trial Balance report in GP. 
--You'll need to set a date to go back to in the @ASOF variable. 
--This is a modification of original code that was created by Steve Gray of 4penny.net(?) 
--AP Aging 
--we have four cases to handle: 
--open invoices 
--open payments and credits 
--closed invoices 
--closed payments and credits 
--we take the doc amount and subtract the  
--apply amounts from the appropriate apply table 
--We need to set a cutoff date 
--usage EXEC AP_HATB '2012-11-02 00:00:00.000' 
--If no date is provided, it uses the current date by default 

CREATE PROCEDURE [dbo].[Ap_hatb] 
-- Add the parameters for the stored procedure here 
(@asof DATETIME=NULL) 
AS 
  BEGIN 
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements. 
      SET nocount ON; 

      IF @asof IS NULL 
        SET @asof = Getdate() 

      -- Insert statements for procedure here 
      SELECT pmTrans.vendorid, 
             vendMaster.vendname, 
             pmTrans.vchrnmbr, 
             pmTrans.docdate, 
             CASE pmTrans.doctype 
               WHEN 1 THEN 'Invoice' 
               WHEN 2 THEN 'Finance Charge' 
               WHEN 3 THEN 'Misc Charge' 
             END             AS docType, 
             pmTrans.docamnt AS docamnt, 
             pmTrans.docnumbr, 
             apply.appldamt, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -30, @asof) AND @asof THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt0to30, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -60, @asof) AND 
                                     Dateadd(d, -31, @asof) 
             THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt31to60, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -90, @asof) AND 
                                     Dateadd(d, -61, @asof) 
             THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt61to90, 
             CASE 
               WHEN pstgdate < Dateadd(d, -90, @asof) THEN pmTrans.docamnt - 
               Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amtOver91, 
             bachnumb 
      FROM   pm20000 pmTrans 
             LEFT JOIN pm00200 vendMaster 
                    ON vendMaster.vendorid = pmTrans.vendorid 
             LEFT JOIN (SELECT aptvchnm, 
                               aptodcty, 
                               Sum(appldamt) AS appldamt 
                        FROM   pm20100 
                        WHERE  docdate <= @asOf 
                        GROUP  BY aptvchnm, 
                                  aptodcty) apply 
                    ON pmTrans.vchrnmbr = apply.aptvchnm 
                       AND pmTrans.doctype = apply.aptodcty 
      WHERE  pstgdate <= @asof 
             AND pmTrans.docamnt - Isnull(apply.appldamt, 0) <> 0 
             AND pmTrans.doctype <= 3 
             AND voided = 0 
      UNION 
      SELECT pmTrans.vendorid, 
             vendMaster.vendname, 
             pmTrans.vchrnmbr, 
             pmTrans.docdate, 
             CASE pmTrans.doctype 
               WHEN 4 THEN 'Return' 
               WHEN 5 THEN 'Credit' 
               WHEN 6 THEN 'Payment' 
               ELSE CONVERT(VARCHAR(2), pmTrans.doctype) 
             END                                          AS docType, 
             -pmTrans.docamnt                             AS docamnt, 
             pmTrans.docnumbr, 
             apply.appldamt, 
             -pmTrans.docamnt + Isnull(apply.appldamt, 0) AS amt0to30, 
             0                                            AS amt31to60, 
             0                                            AS amt61to90, 
             0                                            AS amtOver91, 
             bachnumb 
      FROM   pm20000 pmTrans 
             LEFT JOIN pm00200 vendMaster 
                    ON vendMaster.vendorid = pmTrans.vendorid 
             LEFT JOIN (SELECT vchrnmbr, 
                               doctype, 
                               Sum(appldamt) AS appldamt 
                        FROM   pm20100 
                        WHERE  docdate <= @asOf 
                        GROUP  BY vchrnmbr, 
                                  doctype) apply 
                    ON pmTrans.vchrnmbr = apply.vchrnmbr 
                       AND pmTrans.doctype = apply.doctype 
      WHERE  pstgdate <= @asof 
             AND pmTrans.docamnt - Isnull(apply.appldamt, 0) <> 0 
             AND pmTrans.doctype >= 4 
             AND voided = 0 
      UNION 
      SELECT pmTrans.vendorid, 
             vendMaster.vendname, 
             pmTrans.vchrnmbr, 
             pmTrans.docdate, 
             CASE pmTrans.doctype 
               WHEN 1 THEN 'Invoice' 
               WHEN 2 THEN 'Finance Charge' 
               WHEN 3 THEN 'Misc Charge' 
             END             AS docType, 
             pmTrans.docamnt AS docamnt, 
             pmTrans.docnumbr, 
             apply.appldamt, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -30, @asof) AND @asof THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt0to30, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -60, @asof) AND 
                                     Dateadd(d, -31, @asof) 
             THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt31to60, 
             CASE 
               WHEN pstgdate BETWEEN Dateadd(d, -90, @asof) AND 
                                     Dateadd(d, -61, @asof) 
             THEN 
               pmTrans.docamnt - Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amt61to90, 
             CASE 
               WHEN pstgdate < Dateadd(d, -90, @asof) THEN pmTrans.docamnt - 
               Isnull(apply.appldamt, 0) 
               ELSE 0 
             END             AS amtOver91, 
             bachnumb 
      FROM   pm30200 pmTrans 
             LEFT JOIN pm00200 vendMaster 
                    ON vendMaster.vendorid = pmTrans.vendorid 
             LEFT JOIN (SELECT aptvchnm, 
                               aptodcty, 
                               Sum(appldamt) AS appldamt 
                        FROM   pm30300 
                        WHERE  glpostdt <= @asOf 
                        GROUP  BY aptvchnm, 
                                  aptodcty) apply 
                    ON apply.aptvchnm = pmTrans.vchrnmbr 
                       AND pmTrans.doctype = apply.aptodcty 
      WHERE  pstgdate <= @asof 
             AND pmTrans.docamnt - Isnull(apply.appldamt, 0) <> 0 
             AND pmTrans.doctype <= 3 
             AND voided = 0 
      UNION 
      SELECT pmTrans.vendorid, 
             vendMaster.vendname, 
             pmTrans.vchrnmbr, 
             pmTrans.docdate, 
             CASE pmTrans.doctype 
               WHEN 4 THEN 'Return' 
               WHEN 5 THEN 'Credit' 
               WHEN 6 THEN 'Payment' 
               ELSE CONVERT(VARCHAR(2), pmTrans.doctype) 
             END                                          AS docType, 
             -pmTrans.docamnt                             AS docamnt, 
             pmTrans.docnumbr, 
             apply.appldamt, 
             -pmTrans.docamnt + Isnull(apply.appldamt, 0) AS amt0to30, 
             0                                            AS amt31to60, 
             0                                            AS amt61to90, 
             0                                            AS amtOver91, 
             bachnumb 
      FROM   pm30200 pmTrans 
             LEFT JOIN pm00200 vendMaster 
                    ON vendMaster.vendorid = pmTrans.vendorid 
             LEFT JOIN (SELECT vchrnmbr, 
                               doctype, 
                               Sum(appldamt) AS appldamt 
                        FROM   pm30300 
                        WHERE  glpostdt <= @asOf 
                        GROUP  BY vchrnmbr, 
                                  doctype) apply 
                    ON pmTrans.vchrnmbr = apply.vchrnmbr 
                       AND pmTrans.doctype = apply.doctype 
      WHERE  pstgdate <= @asof 
             AND pmTrans.docamnt - Isnull(apply.appldamt, 0) <> 0 
             AND pmTrans.doctype >= 4 
             AND voided = 0 
      ORDER  BY bachnumb 
  END