SafePay Transactions Upload window does not populate in Microsoft Dynamics GP

SafePay allows the customer to generate a list of checks and voids (from Payables Management and Payroll) that they can upload to the bank.  When a check is presented to the bank to be cashed, the bank will compare the amount and information on the check to the uploaded SafePay file, as a security and fraud prevention measure.

In Microsoft Dynamics GP, the user can generate the upload file to send to the bank, if they have purchased SafePay.  However, we have received several support incidents where the SafePay Transactions Upload window is blank and does not populate in Microsoft Dynamics GP.  If it’s the user’s first time using SafePay, we often find they are not registered for the Bank Reconciliation module.   However, if the user has been using SafePay for some time, we usually find that the check has already been uploaded and it will not upload again. The check can only be uploaded once to the bank.

Here is a running list of potential reasons why the SafePay Transactions Upload window is completely blank:

  • The checkbook ID that the check was generated in is not linked to the Bank Upload ID in SafePay.
  • The check date does not fall within the Last Upload Date and Upload Cutoff Date range specified in the SafePay-Transactions Upload window.
  • The checks have already been uploaded. Review the SafePay Transactions History window for the check number.
  • The Customer is not registered for the Bank Reconciliation module. Note: The CM20200 table will be empty if they are not registered for Bank Reconciliation.
  • The default ‘DD’ prefix was removed from the Next Earnings Statement Number (ie. Direct Deposit number) in Payroll, so the system treats all checks with the same beginning digit as direct deposits and does not include them in the SafePay upload.
  • A third party product may be interfering.  Disable any 3rd party products and test again. 

For more information or steps on how to review the above causes, please refer to Knowledge Base article # 2708664

Thank you,

Cheryl Waswick | Technical Support Engineer | Microsoft Dynamics GP Support -Financials

Visit link:
SafePay Transactions Upload window does not populate in Microsoft Dynamics GP

May 29, 2012 · Cheryl W. MSFT · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 467

Error Exporting to Excel from Business Portal 5.1 for Microsoft Dynamics GP 2010 in Internet Explorer 9

If you’re using Business Portal 5.1 for Microsoft Dynamics GP 2010 along with Internet Explorer 9 and attempt to export the results of a query to Excel you may have received the following “Results not copied to Excel” error:

 

This generally happens when the result set you are exporting is large (~1000 records or greater).

When exporting a Business Portal query to Excel we utilize a java script file in your Business Portal installation folder.  We have worked with our development team to come up with a fix for this issue.

This workaround involves modifying the DataCenter.js file that is found in the C:Program FilesMicrosoft DynamicsBusinessPortalPortalDatacenter directory on your Business Portal server.  The steps are as follows:

1)      First, make a backup of DataCenter.js

2)      Then, open DataCenter.js in Notepad

3)      Search this file for “//Size it”

4)      Edit the file as follows to add the alert section:

Before Edit:

                }

                                }

                                //Size it

                                if(excel.MajorVersion < 10)

                                                excel.ActiveSheet.UsedRange.AutoFitColumns();

                                                excel.ActiveSheet.UsedRange.AutoFitRows();

                               

 

After Edit:

 

           }

                                  alert(“Click to export”);

                                 }

                                 //Size it

                                if(excel.MajorVersion < 10)

                                                excel.ActiveSheet.UsedRange.AutoFitColumns();

                                                excel.ActiveSheet.UsedRange.AutoFitRows();

                               

 

NOTE: You can change the text in the alert() line to whatever you’d like.

5) Save your changes to this file

6) Reload Business Portal and export a query to Excel.  You should now receive the alert prompt, which will give the site the short amount of extra time it needs to successfully export to Excel.

NOTE: You may find that you need to run IISRESET before this change will take effect.

Implementing this quick fix should allow you to successfully export your Business Portal query data to Excel after you have upgraded to IE9.

Enjoy!

Lucas

Continue Reading:
Error Exporting to Excel from Business Portal 5.1 for Microsoft Dynamics GP 2010 in Internet Explorer 9

May 24, 2012 · Lucas Miller · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 381

Concatenating strings in SQL Server

This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter.

A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers.  So if there is an item with serial numbers, instead of showing a list with one serial number per line under the item, which would look a little unwieldy with long list of serial numbers:

we want to show all the serial numbers on one line, separated by a comma. Below is the SQL code that will do this in SQL 2005 and 2008:

SELECT
p.SOPNUMBE SOP_Number, p.SOPTYPE SOP_Type, p.ITEMNMBR Item, p.LNITMSEQ Line_Item_Sequence,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Numbers
FROM
 (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
  (SELECT coalesce(rtrim(SERLTNUM) + ', ','')
   FROM SOP10201 p2
   WHERE p1.SOPNUMBE = p2.SOPNUMBE and p1.SOPTYPE = p2.SOPTYPE
      and p1.ITEMNMBR = p2.ITEMNMBR and p1.LNITMSEQ = p2.LNITMSEQ
   ORDER BY SOPNUMBE
   FOR XML PATH('')) serial_numbers
  FROM SOP10201 p1
 GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, LNITMSEQ) p

Using this code, the line item and serial numbers shown above can look like this:

This code can also be changed slightly for other uses, for example, to concatenate a list of invoices paid by a check in payables. Here is the code to do that using my Payables Apply Detail view:

SELECT
p.Vendor_ID, p.Payment_Voucher_Number, p.Payment_Date, p.Payment_Type,
p.Payment_Document_Number, LEFT(p.apply_docs, len(p.apply_docs)-1) Applied_To_Docs
FROM
 (SELECT p1.Vendor_ID, p1.Payment_Voucher_Number, p1.Payment_Date, p1.Payment_Type,
  p1.Payment_Document_Number,
  (SELECT coalesce(rtrim(Apply_To_Doc_Number) + ', ','')
   FROM view_Payables_Apply_detail p2
   WHERE p1.Vendor_ID = p2.Vendor_ID
     and p1.Payment_Voucher_Number = p2.Payment_Voucher_Number
   ORDER BY Payment_Voucher_Number
   FOR XML PATH('')) apply_docs
  FROM view_Payables_Apply_detail p1
  GROUP BY Vendor_ID, Payment_Voucher_Number, Payment_Date, Payment_Type,
   Payment_Document_Number) p

For more tips like this, take a look at my SQL Server Coding Tips page. To receive new tips as soon as they are published, sign up for the GP Reports Viewer newsletter.

Filed under: GP Reports code, GP Reports Viewer, SQL coding, SQL Server Tagged: featured, GP Reports code, GP Reports Viewer, SQL code

See more here:
Concatenating strings in SQL Server

May 21, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 536

Microsoft Dynamics GP and the Concur Import

Concur Standard, Professional, and Premier are Web-based solutions that automate each step of the expense reporting process. Concur is integrated with the Microsoft Dynamics GP financial management solution through the use of Concur Expense Integrator.

A common question that we receive in regards to Microsoft Dynamics GP and the Concur Import is the following:  Will you help me with the Concur setup window as I cannot get the import to work correctly?

Oftentimes, we find that the customer is using Concur Standard.  Concur Standard is a little bit different than the Professional and Premier offerings, as this gives you the ability to create your own file extract definition.  The Standard Edition only has 25 possible columns, but we need to end up with a 77 column comma separated import file, which the Concur Expense Integrator tool for Microsoft Dynamics GP is expecting.  To do this, you will end up having a lot of blank fields for columns that are not used in the source file.  Another requirement is that the file extension must end with .cgl (Company Check) or .igl (Individual Bill/Company Pay). If you are attempting to set this up, you will want to utilize the APGL File Layout (in Appendix A of the Concur Expense.pdf) which is the file definition that you will need to match with the extract definition that was created.  This is the file format that the Concur Expense Integrator Tool is designed to consume.

If you are having trouble mapping the remaining segments of your accounts you need to know where these are in your source file, if you look at the setup window for mapping the segments there are 6 possibilities (Employee Info 1, Employee Info 2, Employee Info 3, Custom 1, Custom 2, and Cost Center) which translates to columns 17,18,19, 64, 65, and 22 respectively. 

I hope that this helps you to successfully integrate your Concur Expense data with Microsoft Dynamics GP.

Enjoy!
Sarah

Visit site:
Microsoft Dynamics GP and the Concur Import

May 17, 2012 · Sarah Purdy · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 559

Mismatched Inventory Quantities

 

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

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

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

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

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

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

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

GROUP BY ITEMNMBR,
LOCNCODE

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

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

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

Hope that this helps.


Regards,

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

More:
Mismatched Inventory Quantities

May 12, 2012 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 360

Connection to the MAPI server not available in Dynamics GP

I posted about Dynamics GP and 64 bit support last year. As per the technology requirements for Dynamics GP – for the e-Email feature –

E-Mail Functionality Feature

Item

Requirements

Microsoft Office

Microsoft Office 2007 32-bit
Microsoft Office 2010 32-bit

 

Documentation

Click HERE to review the SystemSetup.pdf manual for Microsoft Dynamics GP 2010.  Refer to Chapter 16 for information regarding e-mail setup.

E-Mail

Extended MAPI compliant e-mail application

 

As you can see 32 bit Office is required. So if you are getting the “connection to the mapi server not available” error, and Outlook is working fine, you need to check your office version. If you have the 64 bit version install, you need to uninstall it, and re-install 32 bit Office. 

See more here:
Connection to the MAPI server not available in Dynamics GP

May 11, 2012 · Jivtesh Singh · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 671

Workflow documents suddenly showing as ‘Not Submitted’ rather than ‘Pending Approval’ in Microsoft Dynamics GP 2010

When a user submits a document into the workflow system and approval is required, the document will have a status of ‘Pending Approval’.  A user can view the workflow status from the window in Microsoft Dynamics GP 2010.  For example, in the Purchase Order Entry window below, you can see at the top of the window, the purchase order is ‘Pending Approval by…’.

Have you run into the situation where Workflow for Microsoft Dynamics GP 2010 is working perfectly and then all of a sudden the workflow documents show as ‘Not Submitted’ rather than ‘Pending Approval’ when a user submits a document into the workflow system? In addition, no error is presented to the user upon submission.  For example, in the Purchase Order Entry window below, you can see at the top of the window, the purchase order is ‘Not Submitted’ even though the document has been submitted for approval.

You will also begin to see several errors logged about every 20 minutes in the Dynamics Event Viewer on the machine where Web Services is installed.  The Source of the error is Microsoft.Dynamics.Security.AdminService and the error details are below:

Event Error 1:

Exception type:
System.IO.FileNotFoundException

Exception message:
The system cannot find the file specified. (Exception from HRESULT: 0x80070002)

SecurityContext:null
Action:OnCacheUpdateTimerEvent

Input parameters:

Stack Trace:

at Microsoft.Interop.Security.AzRoles.AzAuthorizationStoreClass.UpdateCache(Object varReserved)
at Microsoft.Dynamics.Security.AzManHelper.UpdateCache()
at Microsoft.Dynamics.Security.AzManHelper.OnCacheUpdateTimerEvent(Object source, ElapsedEventArgs e)

Event Error 2:

Exception type:
System.Runtime.InteropServices.COMException 

Exception message:
The handle is invalid. (Exception
from HRESULT: 0x80070006 (E_HANDLE)) 

SecurityContext:
ApplicationKey:null
ScopeKey:null

User:XXXXXXXXXXXXX

Action:Get

Input parameters:


xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
25cc1a21-2cc4-4b13-a1c8-eea186fb688a

Stack Trace:  
at
Microsoft.Interop.Security.AzRoles.AzAuthorizationStoreClass.OpenApplication(String
bstrApplicationName, Object varReserved)  
at Microsoft.Dynamics.Security.AzManApplicationServiceImplementation.RetrieveForGetByKey(SecurityContext
context, Key key)   at
Microsoft.Dynamics.Security.SecurityService.Get(SecurityContext context, Key
key)

Another symptom appears in Business Portal as well.  If you click to view any external list in Business Portal 5.1, you may also see the following error:

If you launch the Dynamics Security Console on the machine where Web Services is installed, you will see the following error and will not be able to see the security store.

 

Based on the error above, you will more than likely go to Services to see if the Microsoft Dynamics GP Service Host service is running.  This is the service that runs Web Services for Microsoft Dynamics GP 2010.  In Services, you will see the Microsoft Dynamics GP Service Host service is still running, therefore, this may cause you to question the above error in the Dynamics Security Console.

As we were troubleshooting the above issues, we asked what had changed in the environment since everything was working fine.  The answer we received in most cases was that the Microsoft SQL Server was rebooted or the Microsoft SQL Server instance had been stopped and started.  This started us down the path of investigating why a restart of the Microsoft SQL Server would potentially cause an issue.  In all cases the Web Services security store was held in a database in Microsoft SQL Server.  There is a timer that runs every 20 minutes on the server where Web Services is installed.  The purpose of the timer is to update the security store with any changes.  Because the connection to the Microsoft SQL Server was disconnected, this timer cannot make the connection to the Microsoft SQL Server even though the Microsoft SQL Server instance is back up and running.  This connection is running through the Windows Authorization Manager (AZMAN) pieces on the Windows operating system as well. 

This issue then causes all the issues we talked above:

1. Workflow documents now indicate ‘Not Submitted’ rather than ‘Pending Approval’.
2. The external lists in Business Portal 5.1 will not work.
3. The Web Services Dynamics Security Console will produce an error.
4. An event will be logged every 20 minutes in the Event Viewer with the exception message “The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE))”

The resolution for all issues listed above is to stop and start the Microsoft Dynamics GP Service Host service on the server where Web Services is installed.

More to come on this issue!

Enjoy!

Follow this link:
Workflow documents suddenly showing as ‘Not Submitted’ rather than ‘Pending Approval’ in Microsoft Dynamics GP 2010

May 10, 2012 · Kelly Youells · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 354