Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Saturday, November 28, 2015

Analytical Purchase Requisition Entry - Dimension Code Lookup is not working (Temporary Customized Solution)


In the previous post, Analytical Purchase Requisition Entry - Dimension Code Lookup is not working, this issue was thoroughly illustrated along with a link to vote on MSConnect in order to get this resolved by the next release of Dynamics GP. 

Although, there must be a current; yet a temporary, solution in order to remove such glitch from the Analytical Accounting entry as part of the requisition cycle and ensure that the process is running smooth without any issues.

The code simply represents an additional form which retrieves the dimension codes for the specific dimension within the AA purchase Requisition Entry window.

Customization Deployment

The customization can be downloaded from this link, which includes a dll file. Copy the file into Microsoft Dynamics GP Addins folder, which usually can be found on (C:\Program Files (x86)\Microsoft Dynamics\GP2015\AddIns). 

Just in case the dll file is being blocked, right click on the file and make sure to check "unblock" before running Dynamics GP. 
unblock dll file
Note ! 
The customization can only be used with Dynamics GP 2015 R2, on which the error is reported. Future versions will definitely get this resolved and therefore, you will have to remove the dll file before upgrading. 

------------------------------------------------------------------------------------------------------------------
The customization is totally developed by Mr. Ibrahim Omran; Development Manager at Dynamics Innovations; a Golden Microsoft Partner in Amman, Jordan.
------------------------------------------------------------------------------------------------------------------

Open Microsoft Dynamics GP.
Go to Purchase Requisition Entry window on Transactions >  Purchasing > Purchase Requisition

Purchase Requisition Entry Window
Make sure to fill in an account which is linked to Analytical Accounting and require AA details to be filled up. Proceed by clicking on the "AA" button in order to open the Analytical Accounting Purchase Requisition Entry window. 
 
AA Requisition Entry

Place the cursor on the Dimension Code filed and click the lookup button (or the well-known shortcut Ctrl+l) to open up the customized lookup form, which will retrieve the associated dimension code details as shown below:
Customized Dimension Code Lookup Form


Best Regards,
Mahmoud M. AlSaadi

Monday, November 23, 2015

Vendor EFT Bank Details - SQL Script

A client of us requested a consolidated report for all the vendor EFT Bank details, I was quite sure that I have come across an SQL Script on that matter, It was Ian's script | SQL view on Vendor EFT from Azurecurve. While it is just perfect, I still wanted further definitive details including most of the fields in the screen.  


The Vendor EFT Bank details can be found on:
  • Cards > Purchasing > Vendor > Address Button > EFT Bank ButtonOr
  • Cards > Purchasing > Address > EFT BanK

Vendor EFT Bank Maintenance Window 

The script can be downloaded from >> Download Link

/*--------------------------------------------------------------------------------
Creation Date: 23, November, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to retrieve vendor EFT Bank details. 

Revision History:
Revision No.            Revision Date    Description
1                       23/11/2015      Original Version
-------------------------------------------------------------------------------- */

-- Tables Included:
-- SY06000 EFT Bank Setup Information
  
SELECT  SERIES Series ,
        ADRSCODE AS 'Address Code' ,
        VENDORID AS 'Vendor ID' ,
        CASE FRGNBANK
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No'
          ELSE ''
        END AS 'Additional Information' ,
        CASE INACTIVE
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No'
          ELSE ''
        END AS Inactive ,
        BANKNAME AS 'Bank Name' ,
        EFTBankAcct AS 'EFT Bank Account' ,
        EFTBankBranch AS 'EFT Bank Branch' ,
        IntlBankAcctNum AS IBAN ,
        CASE EFTAccountType
          WHEN 1 THEN 'Checking'
          WHEN 2 THEN 'Savings'
          WHEN 3 THEN 'General Ledger'
          WHEN 4 THEN 'Loan'
          ELSE ''
        END AS 'EFT Account Type' ,
        CASE EFTTransferMethod
          WHEN 1 THEN 'Not specified'
          WHEN 2 THEN 'Business Account'
          WHEN 3 THEN 'Corporate Account'
          WHEN 4 THEN 'Personal Account'
          WHEN 5 THEN 'Foreign Account'
          ELSE ''
        END AS 'EFT Transfer Method' ,
        CASE GIROPostType
          WHEN 1 THEN 'Pure'
          WHEN 2 THEN 'Impure'
          ELSE ''
        END AS 'GIRO Post Type' ,
        SWIFTADDR AS 'Swift Address' ,
        CustVendCountryCode AS 'Vendor Country Code' ,
        DeliveryCountryCode AS 'Delivery Country Code' ,
        BNKCTRCD AS 'Bank Country Code' ,
        CBANKCD AS 'Central Bank Code' ,
        ADDRESS1 AS AddressLine_1 ,
        ADDRESS2 AS AddressLine_2 ,
        ADDRESS3 AS AddressLine_3 ,
        ADDRESS4 AS AddressLine_4 ,
        RegCode1 AS RegulatoryCode_1 ,
        RegCode2 AS RegulatoryCode_2 ,
        EFTTransitRoutingNo ,
        CURNCYID AS CurrencyID ,
        EFTPrenoteDate AS 'EFT Pre Note Date' ,
        EFTTerminationDate AS 'EFT Termination Date'
FROM    SY06000
WHERE   SERIES =--- Purchasing Series

Best Regards,
Mahmoud M. AlSaadi

Analytical Purchase Requisition Entry - Dimension Code Lookup is not working

With the introduction of GP 2013 R2, a new feature came along to integrate Analytical Accounting with Purchase requisitions so that the requested can enter associated dimension code values. It was working just perfectly without any issues. 

Although, when testing the same functionality in GP 2015 R2, there is a tiny issue encountered with the lookup button on the Analytical Purchase Requisition Entry window, it seems the lookup for dimension codes is not working at all. 

Here is the original Scenario in GP 2013 (Perfect, without any issues)
  • Go to Transactions > Purchasing > Purchase Requisitions
  • Create a new requisition with items.
  • On the line item level, enter an account that is linked to Analytical Accounting
  • Click on the AA button in order to open the Analytical Purchase Requisition Entry
  • On the Dimension Code field, click on the "Look up" button to retrieve a list of available dimension codes
  • A window including all dimension codes pops up, from which you can choose.

Purchase Requisition Entry - GP 2013

Analytical Purchase Requisition Entry - GP 2013
Applying the same scenario on GP 2015 R2

While applying the same steps mentioned above, you are going to miss the lookup button 

Purchase Requisition Entry - GP 2015

Analytical Purchase Requisition Entry - GP 2015

Technical Perspective (SQL Profiler)

Apparently, when the loop up button (in GP 2013) is clicked, the trace shows appropriate code being run on the SQL server. On the other hand, clicking the same button in GP 2015 shows nothing on the trace.
Here is the code retrieve in GP 2013

SELECT TOP 25
        aaTrxDimID ,
        aaTrxDimCodeID ,
        aaTrxDimCode ,
        aaTrxDimCodeDescr ,
        aaTrxDimCodeDescr2 ,
        NOTEINDX ,
        INACTIVE ,
        DEX_ROW_TS ,
        DEX_ROW_ID
FROM    GP13R.dbo.AAG00401
WHERE   ( aaTrxDimID = 1
          AND INACTIVE = 0
        )
ORDER BY aaTrxDimID ASC ,
        aaTrxDimCode ASC
go
SELECT TOP 25
        aaTrxDimID ,
        aaTrxDimCodeID ,
        aaTrxDimCode ,
        aaTrxDimCodeDescr ,
        aaTrxDimCodeDescr2 ,
        NOTEINDX ,
        INACTIVE ,
        DEX_ROW_TS ,
        DEX_ROW_ID
FROM    GP13R.dbo.AAG00401
WHERE   ( aaTrxDimID = 1
          AND INACTIVE = 0
        )
        AND aaTrxDimID = 1
        AND aaTrxDimCodeID = 1
ORDER BY aaTrxDimID ASC ,
        aaTrxDimCodeID ASC
go



If you have encountered the same issue, you may share your vote on MSConnect to get this fixed by the support team, here is the MsConnect link. (ID: 2048691, Analytical Purchase Requisition Entry - Dimension Code Lookup)

Best Regards,
Mahmoud M. AlSaadi