Canadian Payroll Midyear Tax Update has released.

The Canadian Payroll 2012 Midyear tax update has released for Microsoft Dynamics GP 10 and 2010.

The changes in this update include Ontario and Nova Scotia.

Once the update is installed the Last Tax Update field in the Payroll Control Setup – Canada window (Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll – Canada >> Control) should be 06/15/2012.

Links to site:

GP 10
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagpye10.htm?printpage=false

GP 2010
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagptuye2010.htm?printpage=false

Thanks
Terry Heley

View original post here:
Canadian Payroll Midyear Tax Update has released.

June 5, 2012 · Terry Heley MSFT · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 224

Dynamics GP SOP line items with serial numbers and comments

Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). The serial/lot numbers for each line will be concatenated into one field. The results will also include the header and line item comments.

For more Dynamics GP SOP scripts please visit my SOP SQL Views page. Or take a look at my GP Reports page for additional reporting links. SOP table information can be found on this page.

CREATE VIEW view_SOP_Line_Items_Serial
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_SOP_Line_Items_Serial
-- Created on June 4, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please see http://victoriayudin.com/gp-reports/
-- All line items for posted and unposted SOP transactions with serial and/or lot numbers
-- Returns Functional amounts only
-- Component Items are not taken into consideration
-- Fields that can have different values on the document header and
-- line item are both returned prefaced by 'Header' or 'Line'
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT S.*,
coalesce(C.CMMTTEXT,'') Line_Item_Comment,
coalesce(SN.Serial_Lot_Numbers,'') Serial_Lot_Numbers,
coalesce(HC.CMMTTEXT,'') Header_Comment

FROM  --sop lines
(SELECT Doc_Status = 'Open',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP10100 H
INNER JOIN SOP10200 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE

UNION

SELECT Doc_Status = 'History',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP30200 H
INNER JOIN SOP30300 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE) S --sop lines

LEFT OUTER JOIN
(SELECT p.SOPNUMBE, p.SOPTYPE, p.LNITMSEQ,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Lot_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) SN --serial/lot numbers
ON SN.SOPNUMBE = S.SOP_Number AND SN.SOPTYPE = S.SOP_Type AND SN.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10202 C --line item comments
ON C.SOPNUMBE = S.SOP_Number AND C.SOPTYPE = S.SOP_Type AND C.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10106 HC --header comments
ON HC.SOPNUMBE = S.SOP_Number AND HC.SOPTYPE = S.SOP_Type

--add permissions
GO
GRANT SELECT ON view_SOP_Line_Items_Serial TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, GP SQL view, Sales Order Processing, SQL code

See the original post:
Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 653

Table Links in Extender

There are many times we see that a user removes a record from Microsoft Dynamics GP, but the Extender data is left in the database.  If a new record is creating that happens to have the same Key on it, the data then links to that new record.  Using the Table Links, it is possible to have the Extender data removed when the Microsoft Dynamics GP data it is linked to is removed as well.

Linking an Extender Window to a table ensures that the Extender Window’s data is deleted when the corresponding Microsoft Dynamics GP table record is removed.   A window can be linked to more than one table. When linked to more than one table, the Extender Window data will only be deleted when there is not a corresponding record in any of the linked tables.

If you link an Extender Window to a work table and not the corresponding history table, then the Extender data will be deleted when the record is posted because the data is removed from the work table during posting.  We recommend that you actually just link to the history table if all of your data is moved to history eventually.  This way the Extender data will only be removed if you remove history on that record.
 

To link a window to a table:
1. Open the window you want to link to in Extender.
2. Select Options – Table Links to open the Table Links window.
3. Click the Add button above the Table Links list.

4. Select the Product, Series and Table that you want to link to.
5. Match the Key Fields on the window to fields in the Table.

6. Click Save.
7. Relaunch Microsoft Dynamics GP to enable the Table Links.

Read more here:
Table Links in Extender

May 31, 2012 · Nicole Albertson · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 286

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: 489

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: 401

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: 560

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: 588