Australian Goods and Service Tax (GST) Business Activity Statement (BAS) XML File fails to import into the Electronic Commerce Interface (ECI)

David Meego - Click for blog homepageToday I am going to discuss a support case where we found an issue with the XML file created when exporting the Business Activity Statement (BAS) tax return report for Australian Goods and Services Tax (GST).

So, if you don’t have to work with Australian companies and the rest of this article does not make sense, please don’t worry.

Before I start, I should explain that the issue discussed below has been fixed for GP 2010 (v11.0) Service Pack 3 and later as Problem Report 63031. If you are still on version 10.0 read on, if you are on GP 2010, install SP3 or later (or read on).

Get ready for lots of TLAs (Three Letter Acronyms).

 

Background 

Tax returns for Australian Goods & Service Tax (GST) are called Business Activity Statements (BAS) and there are a number of different forms based on what components of the tax legislation the business needs to report on. The Australian Taxation Office (ATO) provides software called the Electronic Commerce Interface (ECI) which allows the BAS data to be sent electronically to the ATO. The ECI also allows you to export and import the data using XML files.

Microsoft Dynamics GP has an additional BAS Report module for Australian installations. This BAS Report module will gather the data for the required date period in the form needed by the ATO from the source transactions in Microsoft Dynamics GP. Once the information has been collected, it can be either:

  • Manually transferred on the hand written paper form. While the BAS Report module can print a form that looks very like the real paper form, only the real paper form from the ATO can be lodged.
     
  • Manually transferred (cut and paste) directly into ECI software and lodged electronically.
     
  • Automatically transferred via XML files to the ECI software and lodged electronically.

It is this last method that we are interested in.

The way that the XML file method works is as follows:

  1. In Microsoft Dynamics GP, process the BAS data for the desired date period.
  2. Once complete, Edit the Business Activity Statement to see and adjust the data as needed.
  3. From the ECI software, locate the form provided by the ATO which needs completion and export it to an XML file.
  4. Back in GP, import the XML file to populate the BAS header information.
  5. Then export from the BAS a new XML file which now contains the additional transaction data.
  6. From the ECI software, import the updated XML file.
  7. From the ECI software, send the document to the ATO via the internet.
  8. Back in GP, mark the BAS as lodged and close the appropriate Tax Periods.

Note: There is a method of automating the export/import processes to combine steps 3 & 4 into a single step and steps 5 & 6 into a single step.

 

The Problem 

The issue we are seeing occurs at step 6 above. When you attempt to import the XML file into the ECI software it generates an error similar to the one below:

Error – BT – E104
An unexpected error occurred while opening a form.
Further details: unable to access form resource archive for NAT4235-0.2001.V2

Notice that it has a Document Type Description (DTD) of NAT4235-0.2001.V2. All of the different BAS forms have a number similar to this. This number is for a BAS-G type form. However, if we compare the XML exported from the ECI software (step 3) and compare it to the XML exported form the BAS software (step 5) we can see a difference in the headers:

Original ECI Software exported XML


V5>

 

Updated BAS Software exported XML


V2>

 

There is a difference in the DTD with the version number.  The “V5″ in the original XML file has been changed to “V2″ in the updated XML file. “V2″ is incorrect and so when you attempt to import the XML back into the ECI Software, it generates the error.

 

The Cause

OK, so the version has been updated and the BAS code was written using the old version. However, this was something that we expected and the BAS code had special handling exactly for this situation. There is a BAS_Report_Forms_SETP (BAS40200) table whose only job is to store the latest updated DTD version numbers for the different BAS forms. If you import a form with a higher number than the one the BAS code was expecting, it will write it into the table and then use that DTD when exporting.

So what went wrong, why did the “future proofing” code not work?

Well, I have worked it out, it took a while, but the cause is very subtle. Firstly, you have to understand that the BAS code that is reading the XML file is not actually an XML processor. To use the XML libraries requires Dexterity to use COM (Component Object Model) calls, but the BAS code was written before Dexterity supported COM. So the XML file is just read as a Text file and the BAS code interprets the XML tags accordingly.

This is import because the code that handled the “future proofing” of the Document Type Descriptions (DTDs) is looking for the “looking for it at the beginning of a line! Look closely at the XML excerpts above and you will notice that the “

This is why the DTD version check was being skipped and why the updated version was not getting written to the BAS_Report_Forms_SETP (BAS40200) table.

 

The Solution

In GP 2010 Service Pack 3 or later, the code has been fixed in two ways. The default Document Type Descriptions (DTDs) have been updated to the latest versions AND the “future proofing” code that updates the BAS_Report_Forms_SETP (BAS40200) table has been adjusted.

However, if you can’t install GP 2010 SP 3 or later, you can manually fix the issue by populating the BAS_Report_Forms_SETP (BAS40200) table with the updated DTDs.

The SQL script below can be executed against each Australian Company database to update the contents of the table.

  

Transact SQL Script

delete from BAS40200
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (1, ‘NAT4189-9.2001.V5′) — BAS_FORM_A
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (3, ‘NAT4195-9.2001.V5′) — BAS_FORM_C
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (4, ‘NAT4191-9.2001.V5′) — BAS_FORM_D
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (6, ‘NAT4190-9.2001.V5′) — BAS_FORM_F
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (7, ‘NAT4235-9.2001.V5′) — BAS_FORM_G
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (8, ‘NAT4236-4.2001.V5′) — BAS_FORM_H
insert into BAS40200 (BAS_Form_Type_Number, BAS_Form_Type_Code)
 values (16, ‘NAT4646-3.2005.V2′) — BAS_FORM_P
select * from BAS40200

/* 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 this interesting and not too confusing and more importantly… useful. 

David

Read More:
Australian Goods and Service Tax (GST) Business Activity Statement (BAS) XML File fails to import into the Electronic Commerce Interface (ECI)

March 1, 2013 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 327

Copying Report Formats between Reports… and a warning about Word Templates

David Meego - Click for blog homepageThere is an unsupported method of copying report formats between reports that is very commonly used (and even recommended by me), but we recently had a case that highlighted a problem with the technique. This blog post will discuss the method and the issue we have seen.

 

The Situation

The situation is very common and can have two variants. I will use the Sales Order Processing Reports for my example, here are the scenarios:

  1. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to other document types (Blank, Long, Other, Short, with options, etc.).
     
  2. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to the historical version of the report: SOP Blank History Invoice Form.

Next, we will look at how the report formats can be duplicated. 

 

The Method

Before we go into details, I must emphasize that this method is not officially supported as it required direct editing of Customization Maintenance packages.

Below are the steps to copy a report format from one report (Source Report) to another report (Target Report):

  1. Ensure that both the Source Report and the Target Report have both been inserted into the Modified Reports list (right hand pane) in the Report Writer. The Source Report should already be there, inserting the Target Report makes one of the following steps easier.
     
  2. From the Microsoft Dynamics GP application, select Microsoft Dynamics GP >> Tools >> Customize >> Customization Maintenance and export both reports separately as packages. You can locate the files on your Desktop for ease of access.
     
  3. Open Notepad.exe and drag and drop the Target Report’s package into the Notepad window.
     
  4. Then copy the report naming line(s) to your clipboard.  For example:
     

    Report “SOP Blank History Invoice Form”
     
  5. Drag and drop the Source Report’s package into the Notepad window.
     
  6. Select the report naming lines(s) and paste from your clipboard to replace the information.
     
  7. Optional Step when changing report from Work to History: Perform the appropriate Find and Replace steps to update the tables used on the report:
     
    Find: SOP_HDR_WORK    Replace with: SOP_HDR_HIST
    Find: SOP_LINE_WORK    Replace with: SOP_LINE_HIST
     
    Tables which are used for Work and History do not need changing, such as SOP_LINE_CMT_WORK_HIST, SOP_Serial_Lot_WORK_HIST, sopUsrDefWorkHist.
     
  8. Select File >> Save As and save the updated package file as a new file with the name of the Target Report plus “Updated”. Keep the original Target Report’s package file as a backup in case you need to restore it.
     
  9. Back in Microsoft Dynamics GP, import the updated Target Report package via Customization Maintenance.
     
  10. Adjust Alternate Modified Forms and Reports security settings to use the now modified version of the report. 

Note: You must also ensure that the Main Table (as shown on the Report Definition window in Report Writer) is the same for the two reports involved. If the main tables do not match, it is very unlikely that the target report will print successfully.

 

The Problem 

While the method above works extremely well for the Report Writer reports, we have found that reports updated using this method do not always work with Word Templates. If you go back to the original report, the Word Templates work, but with the modified report the Word Template does not always work. This is often noticed when the Historical versions of the reports fail to email.

Our case had the issue that the SOP Blank Invoice Form would print and email via Word Templates, but the SOP Blank History Invoice Form would print but not email. Research found a blog post by Andrew Hall from Touchstone in the UK (see below) that highlighted the issue.

To understand what is happening you need to understand a little about how Word Templates work. Word Templates are tied into the Section Breaks of a report. When the different sections print, events fire in Dynamics which are picked up by the Word Templates handling code and are used to capture details about the key fields upon which the section breaks are based. The code in Dynamics and the section breaks in the reports match so they both refer to the same field in the same table.

If you start changing the sections, or the fields upon which section breaks are based, the code and section breaks no longer match and Word Template functionality will start to fail.

The problem as highlighted in Andrew’s blog post is that the Section Breaks in the Work SOP reports are subtly different from the matching Section Breaks in the History SOP reports. When we used the method above to copy the report format, we unintentionally broke the Word Template Email functionality by changing the Section Breaks.

Below are screenshots of the Dummy1 Header and the Back Order Footer from the Work Invoices that show that the SOP Number field is being captured from the Sales Transaction Work (SOP_HDR_WORK) table.


Dummy1 Header from SOP Work Invoice (SOP Number from SOP_HDR_WORK)


Back Order Footer from SOP Work Invoice (SOP Number from SOP_HDR_WORK)

Below are screenshots of the Dummy1 Header and the Back Order Footer from the original (unmodified) History Invoices that show that the SOP Number field is being captured from the Sales Transaction Amounts History (SOP_LINE_HIST) table, which is not the Sales Transaction History (SOP_HDR_HIST) that came across when we copied the format.


Dummy1 Header from SOP History Invoice (SOP Number from SOP_LINE_HIST)


Back Order Footer from SOP History Invoice (SOP Number from SOP_LINE_HIST)

As the report is not breaking on the correct field in the wrong table, the data passed to the Word Template code is incorrect (blank) and the Email functionality fails to work.

 

The Solution

The fix for this issue is to edit the Section Breaks for the History Report so that the Dummy1 Header and Back Order Footer use the SOP Number field in the Sales Transaction Amounts History (SOP_LINE_HIST) table again. 

The exact steps are listed in Andrew’s Blog:

 

The Warning 

The lesson learned here is to avoid changing section break tables and fields for reports that are used with Word Templates. If the Word Templates fail to work in any way, test with the original Report Writer report and see if that works. If it does, compare the table and field for each section break with the original report and make sure they match.

A good method would be to never remove fields or change section breaks. You can always hide fields or supress section breaks if you want to make changes. Also keep testing (and saving packages) as you modify the reports to make sure you have not broken anything. If you test as you go along, it will be easier to remember the last changes made so they can be backed out.

 

More Information

Below are some blog articles which discuss aspects related to this post:

 

Thanks to Andrew Hall for his troubleshooting on this issue, hopefully I have clarified the reasons and a possible cause in duplicated reports.

David

See more here:
Copying Report Formats between Reports… and a warning about Word Templates

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

Using Problem Steps Recorder (PSR) To Capture Replication Steps

Allan Cahill - Click for blog homepageHi

I recently had a support request where, due to time differences, I was not able to immediately stream / screen share.  It was also difficult to understand the exact set of steps taken leading up to the issue. Due to these difficulties, a viable option was to record the exact set of steps taken leading up to the problem.  This is where Windows Problem Steps Recorder (PSR) came into play.  The following is an overview as to steps to run PSR. 

 

1.  Overview

Problem Steps Recorder can be used to automatically capture the steps performed by a user on a computer running Windows 7, 2008 R2, 8, and 2012, including a text description of where they clicked and a picture of the screen during each click. This capture is then saved to a file that can be used by a support professional to help the user troubleshoot the issue or understand what steps were taken by the user.

The output describes what the user did as well as screen shots of what was going on at the time.  In addition, the screenshots included are clickable to view full size.  PSR has an additional button titled Add Comment.  When you click this button, it allows you to highlight an area of the screen and type in a comment.  This allows you to specify additional comments to the repro steps to make it easier to follow during a complex recording. 

PSR is more than just a screenshot capturing tool. Besides automating the capturing of what’s going on the screen, it will also highlight the user’s mouse clicks, and, most importantly, will provide a detailed textual metadata with a description of what the user is doing.

There is a short video on TechNet that gives a demonstration of the Problem Steps Recorder.

 

2.  How Do I Use Problem Steps Recorder?

Step 1: Firstly, open the application by writing “steps” and pressing enter in the search bar.

You can also open Control Panel and type in problem in the search box, then click on the “Record steps to reproduce a problem” link:

By default, PSR will record only 25 screenshots, yet this can be changed to any number up to 100. Click on the small down-arrow next to the help icon, and then select Settings:

If you have more than 100 actions, which is possible, screenshot #1 would be removed in order to capture screenshot #101. The text detail for Step #1 would still remain.

Step 2: To start recording, click Start Record. On your computer, go through the steps on your computer to reproduce the problem. You can pause the recording at any time, and then resume it later.

NOTE: As you click screenshots are being recorded.  You will see a red dot while you click to indicate this.

During the recording session you can hit the Add Comment button and highlight an area of the screen and leave a comment or question.

Step 3: When you’ve completed reproducing the problem, click Stop Record.

Step 4: In order to save or email the file Support, Save or Email and give the file a descriptive name.

 

NOTE:  In Windows 7/2008 R2, once the recording is stopped, the Save As window is automatically opened.

Step 5:  To view the record of the steps you recorded, open the .zip file you just saved, and then double-click the file. The document will open in your browser.

When the .ZIP file is extracted, the .MHT file can be double-clicked and viewed inside Internet Explorer. Scroll through the steps, or choose to watch them as a slide show. Each step provides text details of the action taken and clearly defines in the screenshot where
the action should be taking place.

The “Additional Details” section at the end of the .MHT file provides a text only version of all the steps taken as well as specific version information. This information can be especially helpful in a Technical Support situation.

 

3.  Notes

  • There are differences between the Windows 7 version of the application and Windows 8 version. Firstly, in Windows 8, Steps Recorder runs as administrator by default but in Windows 7/2008 R2, it does not. Secondly, the Windows 8 version allows you to view the recording before saving or sharing it but the previous version save the recording directly as a zip folder. 
  • The same functionality is available in Windows 7, where it is called the “Problem Steps Recorder”, but no shortcut is automatically created for the Problem Steps Recorder during Windows installation.  From Start >> type PSR. 
  • PSR runs as a standard user mode process. 
  • To record steps in an app that’s not on your desktop, you’ll need to start Steps Recorder from your desktop, go to the app, record the steps you want, and then return to the desktop to stop Steps Recorder.
  • To view the steps you recorded, open the file you saved, and then double-tap or double-click the file.
  • In some cases, tapping or clicking Email in Steps Recorder won’t open an email message. If this happens, try closing your email app before you tap or click Email in Steps Recorder or open a new email message and attach the .zip file.

 

Video Demonstration

(Please visit the site to view this video)

 

Additional Resources

 

Thanks for taking the time to have a read through this blog.

Allan

More:
Using Problem Steps Recorder (PSR) To Capture Replication Steps

February 22, 2013 · Allan Cahill · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 657

Quick Tip: Using Third Party Tables to fill a Scrolling Window

David Meego - Click for blog homepageMy good friend, Mariano Gomez (The Dynamics GP Blogster) raised this issue recently. He wanted to use the method I discussed in my previous Quick Tip post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to gain access to the third party table so he could display it in a Scrolling Window on his form.

The reason why this will not work in its simplest form is that the anonymous table buffer that we have which points to the third party table only has the scope of the callback global procedure. Once that procedure completes access to the third party table buffer is lost.

So what are the alternative solutions?

Going back the Cross Dictionary Dexterity Development techniques, I previously would have suggested a duplicate table definition, however as discussed in point 3 of the previous article, this is not a recommended method anymore.

Note: I am assuming here that the data to be displayed is a small subset of the entire third party table and not containing thousands of records. If you want access to the entire table, the duplicate table definition method might be for you.

Now I would suggest the use of a temporary table to contain the data you want to display in your Scrolling Window. To make this work all you need to do is populate the temporary table before you issue the fill window command and if you are allowing edits and or adds, you must write back the data from the temporary table to the actual table when you have finished.

If the third party table is stored in SQL Server (database type: SQL or default), then make sure that your temporary table is also created in SQL (database type: SQL or default) and not a local ctree table. This means that we can leverage SQL optimization to transfer the data between the actual third party table and the temporary table.

Note: I would suggest adding extra boolean fields to the temporary table to allow you to track what records have been edited or added. You might also want to add keys to the temporary table with these boolean fields as the first segment in the index.This will help later when you need to write the data back to the original third party table.

Below are some suggested methods for transferring the data between the original third party table and your temporary table:

  1. Use the method described in the post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to capture the table buffer. You can then set a range and use range copy to move the data into the temporary table. The range copy command will use a single SQL insert statement if both tables are SQL tables (and there are no Dexterity triggers on the tables). To copy the data back, you can remove all records in the range from the original table and use range copy to replace the updated data (as long as your temporary table contained every column in the original so no data is lost). Otherwise you could use Dexterity to range on updated or added records and write back the data record by record.
     
  2. Use pass through SQL to create the appropriate statements to transfer data to and from the temporary table. Use the Table_GetOSName(table temp_table_name) command to obtain the physical SQL name for the temporary table. The boolean flags added to the temporary table can be used to control what records are inserted and what records are updated at the SQL level.
     
  3. You can also use a call to a SQL Stored procedure, but this will require the creation of the stored procedure at the SQL level and the creation of a Dexterity prototype procedure to call it. The advantage of stored procedures is that the code can be precompiled. However, in this case as the temporary table’s name is not known, the code will have to be dynamically generated and then executed with the EXEC (@variable) command. Thus losing any precompiled benefits…. Forget it this method is not worth the effort.

I hope this post has provided some insight into how you can access third party data in your own scrolling windows. 

Enjoy

David

Excerpt from:
Quick Tip: Using Third Party Tables to fill a Scrolling Window

February 18, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 248

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,


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

See original article:
How to Track unused Journal Numbers

February 17, 2013 ·  · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 202

Surface Pro has been released

David Meego - Click for blog homepageOn 26th October 2012, the Surface RT was released (see post: Pre-order your Surface today) and following on from the success of the first tablet offering from Microsoft….

On 9th February 2013, the Surface Pro was released and is available for purchase. If you are not sure which model suits your needs, then download the Specifications Sheet (which has a comparison) and check out the link below:

For more details or to start your order go to www.surface.com or click on the image below.

Check out the new videos for the Surface Pro below:

Surface Pro Commercial “The Vibe”

(Please visit the site to view this video)

  

Surface Windows 8 Pro Overview

(Please visit the site to view this video)

 

I can’t wait for the Surface Pro to be available in Australia, so I can order a 128GB version with a Type Cover to compliment my Surface RT with Touch Cover.

David

PS: If you enjoyed The Surface Movement video, check out the Behind the Scenes video and the Extended Footage Video.

Read more here:
Surface Pro has been released

February 15, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 283

Document Attachment in Microsoft Dynamics GP 2013

I am a bit late on this topic. We already have many posts (including Feature of the Day – Inside Microsoft Dynamics GP) on this written by several community members. So instead of repeating it again, I am just adding pointers here to those posts.

To just brief what it is: Document Attachment is a new feature in Microsoft Dynamics GP 2013, which would enhance the way we currently deal with document attachments on a record. If you had sensed it right, yes it’s almost like OLE Notes, but much more than that.

This scores above OLE Notes, purely on grounds of three main factors:

  1. It doesn’t restrict itself to 32 bit architecture, like OLE notes container does.
  2. It is compatible with Web Client, but OLE notes is not.
  3. It stores the attachments on company specific SQL tables. You heard it right. It stores on database by harnessing SQL Server’s file stream, if I am not wrong.

More information about this (and how to use) could be read on below posts:

VAIDY

See original article:
Document Attachment in Microsoft Dynamics GP 2013

February 14, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 579