Handling Australian GST Requirements for Payables Transactions in eConnect

David Meego - Click for blog homepageAs part of the handling of Australian Good and Services Tax (GST), Microsoft Dynamics GP needs to be able to track if a Tax Invoice has been received for payables transactions.

This is important as a company must have a Tax Invoice for a purchase before they are allowed to claim the Input Tax Credit for that purchase. The Input Tax Credits are balanced against the tax they have collected from sales and only the balance is due to the Australian Taxation Office (ATO).


So what are the requirements for a Tax Invoice in Australia? This information is not specifically needed for this article, but the accounting geeks out there might find it interesting.

Below are some guidelines, but as I am not a Tax Accountant, please get advice from a Tax professional if you have questions.

Supplies of less than $1000

Tax invoices for taxable supplies totalling less than $1000 must include:

  1. the Australian business number (ABN) of the supplier
  2. the GST inclusive price of the taxable supply
  3. the words ‘Tax Invoice’ stated prominently
  4. the date of issue of the tax invoice
  5. the name of the supplier
  6. a brief description of each item supplied
  7. the GST amount. This can be shown separately or as a statement along the lines of “the total price includes GST”


Tax Invoice example for less than $1000

Supplies of $1000 or more

Tax invoices for taxable supplies totalling $1000 or more must include:

  1. the ABN of the supplier
  2. the GST-inclusive price of the taxable supply
  3. the words ‘”Tax Invoice” stated prominently
  4. the date of issue of the tax invoice
  5. the name of the supplier
  6. the name of the recipient
  7. the address or the ABN of the recipient
  8. a brief description of each thing supplied
  9. the quantity of the goods or the extent of services supplied
  10. A: when GST payable is exactly 1/11th of the total price, a statement along the lines of “the total price includes GST” or  B: the GST amount

 
Tax Invoice examples for $1000 or more


To handle the tracking of this requirement (once Australian GST is enabled in Company Options), there will be a Tax Invoice Required checkbox added to the Tax Detail Maintenance window, and a Tax Invoice Received checkbox added to the Creditor Maintenance Options (Vendor Maintenance Options) window. These two checkboxes are used to identify when a transaction using specific Tax Details requires a Tax Invoice and which Creditors/Vendors always provide Tax Invoices.

On the Transaction, there is a Tax Invoice Required checkbox which will be set if any of the Tax Details used on the transaction have their Tax Invoice Required checkbox selected. There is also a Tax Invoice Received checkbox which defaults to the value stored on the Creditor/Vendor and is tracked in the PM_Tax_Invoices (PM30800) table. If the transaction is not marked as Tax Invoice Received during the original data entry, it can be marked as received after the fact using the Track Tax Invoice Received window (under Purchasing Routines).

Now that we have the background information all sorted, here is the problem:

eConnect does not have any of the handling for Tax Invoices for Australian GST added to its code.  I have raised this issue as Problem Report 69941. However, as this can be resolved with a custom post integration stored procedure, the fix will not be a high priority.

For this custom code to work I needed to solve a couple of issues:

  1. How to read the Enable GST for Australia Company Option from the SY_Company_MSTR (SY01500) table.
  2. How to make sure that I looked at the correct System Database when running under Microsoft Dynamics GP 2013.

For the solutions to these issues please see the articles listed under More Information.

 

The code below is an excerpt of the body of the taPMTransactionInsertPost stored procedure. The complete code with the parameter list is attached to the bottom of this article.  

SQL Code Excerpt for taPMTransactionInsertPost

SET NOCOUNT ON

SELECT @O_iErrorState = 0

— Check that Australian GST is enabled in Company Options for current company
declare @Opt10 INT
declare @SQLCode NVARCHAR(4000)
declare @SYSDBNAME CHAR(80)
if exists (select * from dbo.sysobjects where id = object_id(N’dbo.SY00100′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
 select top 1 @SYSDBNAME = DBNAME from SY00100
else
 set @SYSDBNAME = ‘DYNAMICS’
set @SQLCode = N’select @pOpt10 = CASE WHEN Company_Options & 65536 * 2 > 0 THEN 1 ELSE 0 END — Bit 17: Australian GST
     from ‘ + rtrim(@SYSDBNAME) + ‘..SY01500 (nolock)
     where INTERID = DB_NAME()’
exec sp_executesql @SQLCode, N’@pOpt10 int output’, @Opt10 output

IF @Opt10 = 1 BEGIN
 — Update Transaction’s Tax Invoice Required Flag based on Tax Details   
 IF exists (select * from PM10000 (nolock) where VCHNUMWK = @I_vVCHNUMWK) BEGIN
  update PM10000 set TaxInvReqd = (select max(D.TaxInvReqd) as TaxInvReqd from PM10500 T (nolock)
          join TX00201 D on D.TAXDTLID = T.TAXDTLID
          where T.VCHRNMBR = VCHNUMWK)
  where VCHNUMWK = @I_vVCHNUMWK
 END

 — Update Tax Invoice Received Table, default in value from Vendor table
 IF not exists (select * from PM30800 (nolock) where VCHRNMBR = @I_vVCHNUMWK) BEGIN
  insert into PM30800 (VENDORID, VCHRNMBR, DOCTYPE, DOCNUMBR, POPRCTNM, Tax_Date, TaxInvRecvd, GSTDSAMT)
   values (@I_vVENDORID, @I_vVCHNUMWK, @I_vDOCTYPE, @I_vDOCNUMBR, ”, @I_vTax_Date
    , (select TaxInvRecvd from PM00200 (nolock) where VENDORID = @I_vVENDORID)
    , (select sum(TAXAMNT) as GSTDSAMT from PM10500 (nolock) where VCHRNMBR = @I_vVCHNUMWK))
 END

END

RETURN (@O_iErrorState)

/* Copyright © Microsoft Corporation.  All Rights Reserved.                     */
/* This code released under the terms of the                                    */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

 

 

More Information: 

 

Hope you find this information useful, even if not working with Australian GST. 

David

Visit site:
Handling Australian GST Requirements for Payables Transactions in eConnect

July 29, 2013 · David Musgrave · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 1,085

Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 6

Part 6 – Tools for Troubleshooting Web Client issues: Other Tools



In Part 5 we looked at Fiddler, a proxy web debugger application and how it is able to break down HTTP and HTTPS traffic between a client app and a Web Server. Today, I will look at 2 tools that go down right to the wire – literally!

Wireshark

Wireshark is a free and open-source packet analyzer. It is used for network troubleshooting, analysis, software and communications protocol development, and education. Originally named Ethereal, in May 2006 the project was renamed Wireshark due to trademark issues.

Wireshark is cross-platform, using the GTK+ widget toolkit to implement its user interface, and using pcap to capture packets; it runs on various Unix-like operating systems including Linux, OS X, BSD, and Solaris, and on Microsoft Windows. There is also a terminal-based (non-GUI) version called TShark. Wireshark, and the other programs distributed with it such as TShark, are free software, released under the terms of the GNU General Public License.

Wireshark Network Analyzer

Wireshark is very similar to tcpdump, but has a graphical front-end, plus some integrated sorting and filtering options. Wireshark allows the user to put network interface controllers that support promiscuous mode into that mode, in order to see all traffic visible on that interface, not just traffic addressed to one of the interface’s configured addresses and broadcast/multicast traffic. However, when capturing with a packet analyzer in promiscuous mode on a port on a network switch, not all of the traffic travelling through the switch will necessarily be sent to the port on which the capture is being done, so capturing in promiscuous mode will not necessarily be sufficient to see all traffic on the network. Port mirroring or various network taps extend capture to any point on the network.

So why is Wireshark used with the Web Client? Since it analyzes TCP/IP traffic, you could potentially use it to understand if the proper ports are being used by the application when communicating with the Web Server(s) or the Session Host(s), especially when traffic has to traverse intranet, DMZs, and extranet zones on your network. You could potentially determine if there are any translation issues between external DNS addresses and internal network addresses.

ClearSight Analyzer from Fluke Networks

This product is advertised as Wireshark on steroids as it supports the Wireshark decode engine. In addition, it’s able to make sense of all TCP/IP traffic by implementing some powerful graphics showing how machines and devices interact with each other.

Network diagram

 

Conversation Chart

ClearSight also implements a powerful bounce chart for traffic on single or multi-segment networks. Now here’s the bummer: it’s not free! However, for a small fee you are able to obtain a fully featured product.

If you are more like me – a visual person – then ClearSight is certainly worth the price. If you are comfortable in your own skin looking at TCP/IP raw traffic, then Wireshark is the way to go. In any event, you have two powerful tools that can really breakdown your network traffic with ease, for you to analyze where things may be breaking down, preventing Web Client from functioning.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Visit link:
Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 6

July 26, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 363

Creating a Word Template for Dynamics GP – Dynamics GP Insights

Creating a Word Template requires working with Microsoft Word and Dynamics GP Report Writer. This post will walk you through the required steps.

View the original here:
Creating a Word Template for Dynamics GP – Dynamics GP Insights

July 26, 2013 · Victoria Bell · No Comments
Tags:  · Posted in: Blogs I Follow Total Views: 99

Creating a Word Template for Dynamics GP

Creating a Word Template requires working with Microsoft Word and Dynamics GP Report Writer. This post will walk you through the required steps. It’s important to perform these steps in order. You also will be working with the Template Configuration and Template Maintenance in Dynamics GP.

You must have two applications installed before beginning this process:  Microsoft Dynamics GP Add-On for Word and Open XML SDK 2.0 for Microsoft Office. Open XML will be installed when you install Dynamics GP. The Dynamics GP Add-On for Word is located on the Dynamics CD.

8833-1

8833-2

To use Microsoft Word with Dynamics GP, you must add the “Developer” tab to your Word ribbon bar. Go to Options | Customize Ribbon and select “Popular Commands.” Add the Developer function to the Ribbon list and then check the box. Click “OK.”

8833-3

Template Configuration-Reports | Template Configuration

The configuration window allows you to enable a specific form(s) to work as a template.

Expand the trees for Enable, Company and Series. Mark the document(s) for which you want to create a template. Be sure to mark the Enable Report Templates and, if desired, to allow use of the Standard form even though you’re using the template.

8833-4

The next step is to create a modified GP report in Report Writer. You may want to modify the report with a definition field. You can place the definition field anywhere on the report, regardless of where you place it on the template. You will need to make note of the section where you have placed the field, such as the “Report Header” shown below. (I have added the Vendor ID.) Save and close the report. Go back into Dynamics.

8833-5

Dynamics | Tools | Setup | System | Alternate Modified Forms & Reports

You must give access to the modified form for the series with which you are working.

8833-6

Creating an XML File

The next required step is to create an XML file in order to create your template. You will need to go to the entry screen of the series you are working with and bring up a transaction to print. When you print the form, you will select the Standard form and print to a file. The file must be an XML file.

8833-7 8833-8

Click “OK.” Open the file to make sure it is an XML file.

8833-9

Template Maintenance-Reports | Template Maintenance

Select the report name you will be working with. Be sure to select the modified report.

8833-10

Once you have selected your modified report, you will be required to create the template you will be working with. Select “Modify.”

8833-11

Selecting “Modify” will launch Microsoft Word. Click on the “Developer” tab on the ribbon bar. Click on the “Field List” tab on the ribbon bar.

8833-12

Clicking “Field List” will open the “Source File” window to the left of the document. Select the XML resource. This will populate the report section list.

8833-13

Once you bring up the source list, you will have to “Remove Source.” Once you click “Remove,” you will then click on “Add Source.” 8833-14

When you click “Add Source,” you will select the XML file you saved. Click “Open.”

8833-15

This will take you back to Microsoft Word. Select the source file again. Select the Report Section in which you placed the field definition in Report Writer. Scroll down to the field definitions and select the field definition that you put on the modified report in Report Writer. In this example, select “PM_Vendor_MSTR”, “Vendor ID.”

8833-16

Before you drag that field definition to the template, you will be required to insert a cell where you want to position that field. Click the area of the template and then right-click to insert a cell. In this example, I have inserted a cell in the “Header” section. You can now drag and drop the field definition into that cell.

8833-17

Once you have made your modifications you will need to save the template as a Word document. It will want to save the file to the temp directory, but it is recommended to save it to a shared location on your server. In this example, I have saved it to my desktop.

8833-18

Once the Word document is saved, you will return to the Template Maintenance screen.

In the Template Maintenance screen, you will need to add that new template. You may replace the existing template, rename it or create a new named file. Highlight the existing template and click the plus sign above it. This will launch Windows Explorer for you to select your saved Word template.

8833-19

Select your new Word template, and then click “Open.”

8833-20

You may get a “Replace” message if you gave the report the same name as the original. Click “Yes.”

8833-21

You will be required to assign the template to a company or companies and vendor(s). Select “Customers” if you are working with sales templates. You must have at least one “Default” template.

Click “Assign.” This will open an alternate screen.

8833-22

This is the Company screen. Select the company or companies that will be using this template. Click “Set Default” to select your template.

8833-23

Click “Save.”

8833-24

Click “Assign” again for your vendors. Click the plus symbol to open the “Add Vendors” window.

8833-25

Select a vendor or a range of vendors. Click “OK.”8833-26

Mark the vendors you want to use the template or click “Mark All.” Save.

8833-27

Close the Template Maintenance screen.

When you go back to the Series Transactions screen, select your document and print the template to the screen to verify your changes. Be sure to select “Template” as your report type.

8833-28

You should see your template with the modifications you have made.

8833-29

Note:  Although you may make cosmetic changes to your template in Word, if you add any field definition, you must make that change in Report Writer. Once you have made that change, you are required to create another XML file and start the process again.

For more information, please contact our Microsoft Dynamics GP Support Center.

 

 

 

See the article here:
Creating a Word Template for Dynamics GP

July 26, 2013 · Victoria Bell · No Comments
Tags: , , ,  · Posted in: Blogs I Follow Total Views: 1,221

Management Reporter – An Unknown Error Has Occurred

After dealing with an obscure and unhelpful Integration Manager error recently, I’ve also had a Management Reporter one; when trying to generate a report the following error was consistently produced; An unknown error has occurred while processing report As well … Continue reading

Continue Reading:
Management Reporter – An Unknown Error Has Occurred

July 26, 2013 · Ian Grieve · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 1,589

Developing with the Microsoft Dynamics GP 2013 Named System Database

David Meego - Click for blog homepageMicrosoft Dynamics GP 2013 introduced another long sought after feature….
The Named System Database.

Instead of the System database being hardcoded to DYNAMICS, a different name can be selected when installing GP.

So what does that mean? Well, it means that more than one Dynamics GP system can reside on a single instance of SQL Server. Before when a second Dynamics GP system was needed, you would have to install a new instance of SQL Server. Now you can have multiple System Databases on a single server without needing additional instances of SQL Server.

For hosting partners, it means that they can host multiple clients off a single SQL Server instance. As a side benefit for consultants supporting multiple versions and multiple customers, it means no longer having to have lots of SQL Server instances on their machines

As a developer, how can I make sure my code uses the correct System Database for my instance?

From a Dexterity perspective it means updating your code to use a call to the function GetSystemDatabaseName() rather than using the constant SQL_SYSTEM_DBNAME to obtain the name of the System Database.

If you have code shared between previous versions of Dynamics, you can create your own function which has conditionally compiled code based on the value of a “version” constant. See the code below for an example:

Code MBS_GetSystemDatabaseName()

function returns string sSystemDBName;
optional in boolean fConnected = true;

#if MBS_PROD_MAJ >= 12 then
    sSystemDBName = GetSystemDatabaseName(fConnected);
#else
    sSystemDBName = SQL_SYSTEM_DBNAME;
#end if

 

But how can you write SQL code which can run against a company database, but reference data in the System Database?

You can’t just prefix the table names with “DYNAMICS..” anymore as that might not be the correct name for the System Database.

I discussed this issue with Patrick Roth who pointed out that the GP 2013 company databases now have a new sySystemDB (SY00100) table with a single record in it that provides the Company ID and the name of the System Database. So if you need to know the Company ID of the current company database or the name of the System Database, just get the first record in the table (ie. from Dexterity: get first table sySystemDB;). 

From SQL, try the query below to see the table.

select top 1 * from SY00100

So armed with this knowledge I created some code to build a query as a string to return some data from a table in the System Database. Then I used the exec() SQL function to call it. This works, but how can I return the value from the pass through SQL code to my script? The exec() function cannot do this, it has no mechanism to pass data back to the calling script.

I then asked my friend and SQL guru, Robert Cavill, “How you can execute pass through SQL code AND return data from the code?”. He told me about the sp_executesql stored procedure which allows for parameters to be defined and passed to and from the pass through SQL code.

The final piece to the puzzle was identifying whether to use the hardcoded “DYNAMICS” (for pre GP 2013) or to use the data in the SY00100 table for the System Database name. This was fairly easy to solve as the SY00100 table will only exist in a company database from GP 2013 or later.

The code below should be executed in the context of a company database. It will identify the associated System Database and then look at the Company Master table and return whether the Use Security Checkbox in the Company Setup window is selected:

SQL Code Example

— Define Variables
declare @UseSecurity INT
declare @SQLCode NVARCHAR(4000)
declare @SYSDBNAME CHAR(80)

— Identify System Database Name
if exists (select * from dbo.sysobjects where id = object_id(N’dbo.SY00100′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
 select top 1 @SYSDBNAME = DBNAME from SY00100
else
 set @SYSDBNAME = ‘DYNAMICS’

— Define and Execute pass through SQL Code
set @SQLCode = N’select @pUseSecurity = USESCRTY from ‘ + rtrim(@SYSDBNAME) + ‘..SY01500 (nolock)
     where INTERID = DB_NAME()’
exec sp_executesql @SQLCode, N’@pUseSecurity int output’, @UseSecurity output

— Display result from Variable
select @UseSecurity AS UseSecurity

/* Copyright © Microsoft Corporation.  All Rights Reserved.                     */
/* This code released under the terms of the                                    */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

 

 

I hope you find these techniques useful. I know I did. In my next post you will see why….

David

More:
Developing with the Microsoft Dynamics GP 2013 Named System Database

July 26, 2013 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 490

Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 5

Part 5 – Tools for Troubleshooting Web Client issues: Fiddler


In Part 4 of the series, we visited the Microsoft Fix It! solution which allows you to collect myriad of information about your Web Client environment configuration during a support case. Continuing with the series, I will look into Fiddler, which I briefly referenced in my article “Unable to access SnapIn config data Store” accessing Web Management Console when I was having problems with the Web Management Console application.

Fiddler is a free HTTP debugging proxy server application written by Eric Lawrence, formerly a Program Manager on the Internet Explorer team at Microsoft. Fiddler captures HTTP(S) traffic and creates trace files that can be used to analyze web traffic. It can also be used to “fiddle” with HTTP traffic as it is being sent. By default, HTTP(S) traffic, captured via the Microsoft’s Windows Internet (WinINet) API,  is automatically directed to the proxy at runtime, but any browser or application (and most mobile devices) can be configured to route its traffic through Fiddler.

Unlike the original version, Fiddler 2 offers support for interception and tampering with HTTPS traffic.

Now the basics…

Once you have downloaded and installed Fiddler, you are pretty much ready to go. By simply opening the application, you can begin to capture any HTTP(S) traffic running on your machine and a server you are trying to reach.

Fiddler Web Debugger

With the Web Client in particular, you will open your browser, enter the Web Client URL, typically https:///GP. You can then switch over to Fiddler to see the trace being captured.

Web Session

Each entry in the list is known as a web session. Each session captures information such the Fiddler assigned number of that session, the result, the protocol used to access the content, the host (including port numbers accessed), the URL of the session, body, caching (if required), content type, and the process. You can also add a number of columns to the list. Each Fiddler number is shown with visual cues to facilitate information reading.

Fiddler can show statistical information specific to each session, by simply highlighting the session in the list and clicking on the Statistics tab on the right pane. You can get an idea of the Web Client’s overall performance metrics. You can select all sessions to see the total number of requests and bytes sent and received, broken down by content type or in a pie chart. By exposing all HTTP(s) traffic, Fiddler easily shows which files are used to generate a given page: users can multi-select the number of requests and bytes transferred to get a “total page weight”.

Statistics tab

There are obviously, countless other measurements taken by Fiddler, including the powerful Timeline feature. The Transfer Timeline allows you to visualize the HTTP(S) traffic on a “waterfall” diagram. This feature has two modes of recording information: buffering mode and streaming mode.

Fiddler Transfer Timeline

Streaming mode ensures that HTTP responses are not buffered by Fiddler. Buffering alters the waterfall diagram, where none of the images begin to download until their containing page completes.

Since the purpose of this article is to expose you to Fiddler, I can only say go ahead and download the tool and begin to familiarize yourself with the features. Fiddler traffic is routinely collected by Microsoft Dynamics GP Support to determine whether there are issues between your browser and the Web Server preventing the proper functioning of the Web Client.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Taken from:
Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 5

July 26, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 805