Little Tricks With Report Writer Package Files

I remember when I figured out that I could open, in Notepad, a package file (Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Export) containing a report. I thought, hey, that's cool.  And then I figured out that I could copy the "guts" of a report from one package to another in order to do something like....make the SOP Other Invoice Form look exactly like the SOP Blank Invoice Form.  With that, I thought, hey, that is really cool!  So, I thought I had it all figured out-- easy enough to do as long as the tables of the reports were pretty much the same. 

But, then, my coworker Cindy Boersma asked if I knew I could make the SOP Blank History Invoice Form look exactly like the SOP Blank Invoice Form using the same method.  Holy cow!  No, I did not, although it all makes logical sense.  Since all the information, including table names, is in the package file.  So, here are the basic steps, adapted from Cindy's notes.  As always, please make sure you either keep backups of the package files or the reports dictionary before attempting any of these steps to avoid the loss of your modifications :)

1.  Export the report you want to use as the "basis" of the other report (Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Export)

2.  Open the package file created using Notepad

3.  At the top of the package file, you should see the component and report name as highlighted in the screenshot below

4.  Simply edit these two (2) references to the report name to match the report that you wish to copy the package to (for example, SOP Blank History Invoice Form"). Check Report Writer if you have issues finding the report name (it needs to be the name used in Report Writer for the report, which is also the name that appears across on the top border of the Screen Output window when you print a report to the screen-- NOT the name that prints on the report itself, this is not always the report writer name).

5.  Next, if the report you are copying to uses different tables (e.g., the SOP Blank History Invoice Form uses the SOP history tables while the SOP Blank Invoice Form uses the SOP work tables), you will need to use Edit-Replace in Notepad to make the substitutions.  Now, in this case, it assumes that the field names in the tables are the same.  So we find/replace SOP_LINE_WORK with SOP_LINE_HIST and SOP_HDR_WORK with SOP_HDR_HIST.  And here are the results...

Not only did we replace the component and report identifiers, but the find/replace also updated the table references in the report package as well.

6.  Now, we can import our package file back in to GP, Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Import.  Remember, no other users can be in Dynamics GP when you complete this process (assuming you are accessing a shared reports dictionary).

And there you go.  Creating a historical invoice that looks identical to the standard invoice in minutes! So cool.  And why I love this job-- learn something new (even if it was sooo obvious) every day!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Using Custom Icons to Differentate GP Versions

I develop Dynamics GP customizations and integrations for alot of different customers, and currently they are running GP 9, GP 10, GP 2010, and GP 2010 R2 (thankfully nothing older than GP 9!).  Most use only the core GP modules, while some use Project Accounting, and a few use Analytical Accounting.  Because of this, I have to maintain several different versions of GP on multiple virtual servers.

This is a pretty standard situation, but one small annoyance I have had is that the GP 10 icon is the same as the GP 2010 icon.  So if I have both versions installed on a server, I have to try and differentiate the icons on my start menu, task bar, and desktop so that I can tell which is GP 10 versus 2010.  And then I have one machine with GP 2010 and a separate install of GP 2010 R2, so that is also fun.

I don't know why it didn't occur to me earlier, but tonight I thought I would try and create custom icons for each version of GP.  If you are using Visual Studio 2008 or Visual Studio 2010, it's a pretty simple process.

I already have a standard Dynamics GP icon file that I created several years ago, or if you want to create your own, you can use an icon editor or extractor.

I just made a copy of the icon file, opened it in Visual Studio, and then added a basic "2010 R2" to the 32x32 icon, and just an "R2" to the 24x24 and 16x16 icons.  I've made a similar one for GP 10.  And now I can use the same technique to create one for my separate Analytical Accounting, Project Accounting, and Manufacturing installs.

Obviously desktop icons have a text label, but the custom icons are very helpful for the icons in the system tray.

Unfortunately, while writing this, I just discovered one very big limitation.  The Visual Studio 2010 icon editor does not support editing of 32-bit (color depth) icons.  Since I am using my custom icons on a virtual server that I access via RDP, the 16-bit icons are fine for me.  But if you want to make 32-bit versions, you'll need a real icon editor, such as the very good Axialis Icon Workshop, or perhaps there is a free product available.

It appears that Axialis Icon Workshop includes a Visual Studio 2010 Add In, so that's a nice feature if you do much icon editing.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Credit Limits and Sales Process Holds

I just finished up a post for the BKD blog on sales process holds, and I thought I might spend a little time here on one the neat side benefits of using process holds.  For those of you that are not familiar with them, sales process holds are user-definable holds that you can assign to Sales Order Processing transactions (Quotes, Orders, Invoices, etc) that can prevent the document from being printed, posted, fulfilled, and/or transferred.  These holds are quite handy, as they can be applied to ranges of documents, individual documents, or even default when certain document types are used.

There is a bit of a "bonus" to using sales process holds in tandem with customer credit limits. You can actually specify a process hold to be automatically assigned to a document when the customer exceeds their credit limit.  For example, let's say that your credit manager has to review all documents that exceed a customer's credit limit.  By assigning the process hold automatically, the credit manager could complete his or her review and remove the holds on those documents that can continue to be processed. 

The setup for this feature is quite simple.  Start by setting up the process hold, Microsoft Dynamics GP-Tools-Setup-Sales-Process Holds.

Enter the Process Hold ID and Description.  Specify a Password if you want users to enter a password in order to remove the hold.  And then mark the items you want to "Apply Hold To".  Click Save.

Next, you need to specify the hold on the specify invoice and order types that should automatically have a credit limit hold applied.  Go to Microsoft Dynamics GP-Tools-Setup-Sales-Sales Order Processing Setup.  Click the Sales Document Setup button and choose either Invoice or Order.



Select the Order or Invoice ID, and then select the appropriate process hold in the Credit Limit Hold ID field.  Click Save.  Now, when you enter an order using this Order ID and the customer exceeds their credit limit, the process hold specified will be automatically assigned to the order.  In order for this functionality to work, you CANNOT have a password specified for the "Exceed Credit Limit" option in Receivables Management Setup (Microsoft Dynamics GP-Tools-Setup-Sales-Receivables).

When entering an order that exceeds the customer's credit limit, a warning will be displayed.  Choose Continue, and the hold will automatically be applied to the order when it is saved.

Note that the CREDIT process hold has automatically been applied.  How easy is that (to borrow from the fabulous Ina Garten)?

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!

I recently upgraded a Dynamics GP Visual Studio Tools AddIn from GP 9 from GP 2010.  The AddIn reviews some GP transactions, performs some calculations, and then inserts new payroll transactions using eConnect.

The upgrade was straightforward and worked fine on my development server.  It installed smoothly on the client server and looked like it was working...right up to the point where the following eConnect error occurred:

The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.

In this particular case, the AddIn was inserting an entire payroll batch, but the specific stored procedure in the error could have been any eConnect procedure.

I've never seen that type of error before, so I started with the obvious--I checked the SQL database to see if the procedure existed.  It definitely did exist in the database.

I then thought that maybe it was a permission issue, so I confirmed that the eConnect domain user was setup as a SQL Login, and that an eConnect user was properly setup for the company database.  I even checked the DYNGRP role to make sure that the stored procedure was listed with EXEC permissions.

Everything looked fine.

I did a little research on the error, and saw a post that recommended checking the database connection string.  If the database was not specified properly, and the import was somehow referencing the master database or even DYNAMICS, naturally that would cause this type of error.  So I added some debugging code, but the connection string looked fine--the proper GP company database was being referenced.

I was running out of options, so I dug out the Direct Document Sender, a tool used by GP support to troubleshoot eConnect issues.  It allows you to specify the database connection info, select an XML file containing the eConnect data to be imported, and then it sends the XML document off to eConnect.  This helps to rule out any bugs in your custom integration.

Interestingly, the Direct Document Sender received the same error message indicating that the stored procedure did not exist.  Although it helped to rule out the VS Tools AddIn and its eConnect code, and it helped me realize that there was a lower level issue with the client's environment, I still didn't have any specific clues as to the real cause.  I was thinking that maybe it was a problem with the eConnect procedures in the client's databases, or that perhaps something did not upgrade properly from GP 9 to GP 2010.

As a long shot, I ran the dbmaintenance.exe utility in the Dynamics GP application directory to recreate the Functions and Stored Procedures on the company database.  That completed successfully, but when I ran the integration and the Direct Document Sender, both still received the same error.

As a final test, I wanted to get a baseline to test against in the client's environment, so I created the TWO / Fabrikam test company database.  GP Utilities setup the company, and I then went into SQL Server Management Studio and added the eConnect login as a user on the TWO database.

To perform my test with the Direct Document Sender (which uses Windows authentication to access the SQL Server), I logged into the client's server as the eConnect domain user, just to make sure that no other user account variables were involved.  I launched the Sender and started to setup the connection string, but when I went to select the database, TWO was not listed.  I verified everything several more times, but TWO didn't show up.

I then logged out of the server and logged back in as the Administrator.  When I did that, the TWO database did show up.  Puzzled, I checked and rechecked the eConnect user for the TWO database, but it looked fine.  For some reason I couldn't get TWO to appear in the database list.

Since this didn't make any sense, I went back to the SQL Server Login window for the eConnect user and checked the settings for at least the fifth time.  While I was staring at the window, something caught my attention.

I scrolled to the right and saw that the user record for the TWO database had a Default Schema of dbo, but the records for the company databases had the eConnect user listed as the Default Schema.

The light bulb instantly came on and it all made sense.  Well almost all.

If you aren't familiar with SQL Server Schemas, you should definitely read about them just enough to understand what they are.   They are not used by GP, so you don't need to be an expert; however, because they are not used by GP, if someone happens to use them in a GP database, it will likely cause problems, so you will need to know just enough to identify, understand, and resolve the issue.

Because the eConnect SQL user was assigned a Default Schema other than dbo, when eConnect logged into the database and tried to execute the payroll stored procedure, it literally didn't exist.  In fact nothing existed--no tables, no stored procedures, nothing.

I tried changing the Default Schema for the user back to dbo, but for some reason that didn't solve the problem.  I had to delete the eConnect user and eConnect schema completely from all databases, and delete the eConnect login for good measure, and then recreate the login and users with the proper dbo default schema.

Once I did that, the TWO database showed up in Direct Document Sender, and sure enough, eConnect worked just fine.  The AddIn immediately started working properly and was able to import payroll batches.

I'm not entirely sure how the eConnect schema got created on the SQL Server or how it was assigned as the Default Schema for the eConnect user on the company databases.  And I don't understand why changing the Default Schema back to dbo didn't fix the problem.  But in the end, it was easy enough to remove the users completely and recreate them.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

eConnect 2010: CreateEntity vs. CreateTransactionEntity

If you develop eConnect integrations, you probably know by now that with eConnect 2010, Microsoft changed the method used to submit transactions to eConnect.

With GP 10 and earlier, there was a single method called "eConnect_EntryPoint".  Simple and easy, it accepted all of the eConnect document types.

With GP 2010, there are now two different methods.  There is now a method called "CreateEntity", and a second called "CreateTransactionEntity".

Although I have one guess, I haven't asked and don't really know why this change was made, or what the rationale was behind implementing two methods--I just hope that some thought was put into it, and I hope that there was some benefit behind the scenes.

I hope that because it's slightly annoying to have to make two different calls now.  By no means a big deal, but just slightly annoying, especially if you are upgrading from eConnect 9 or 10.  You have to review your code and make sure that your code to create customers calls CreateEntity, and your code that imports invoices calls CreateTransactionEntity.

The eConnect 2010 Programmer's Guide provides this mediocre and slightly vague explanation the two new methods:

CreateEntity:  Creates a record using information from an eConnect XML document. Use CreateEntity to add data entities like customers or vendors. You specify the type of record to create with a parameter that represents an eConnect XML document. If the operation succeeds, the method returns True as a boolean value.

CreateTransactionEntity:  Create a transaction using information from an eConnect XML document. Use CreateTransactionEntity for transaction documents like sales orders or inventory transactions. You specify the type of transaction to create with a parameter that represents an eConnect XML document. If the operation succeeds, the method returns an XML string that represents the eConnect XML document that was created.

So, this is fine for obvious record types like a vendor vs. a voucher, but what if I create a new batch using SMTransactionBatchType?  Is a batch an "Entity", or a "TransactionEntity"?  That starts to become an existential question, because the current eConnect documentation offers no guidance when discussing each of the transaction types.  You're stuck trying both methods to figure out which one to use (hint--the one that causes your app to crash is the wrong one). 

With the griping aside, there is one last thing to pay attention to.  Okay, it's really one last gripe.

Unless you paid very careful attention, you probably missed one critical difference between CreateEntity and CreateTransactionEntity.  Do you know what it is?

They have different names obviously, but they both accept the same parameters, and they both seem to function in the same way.  Read the last sentence of each of the paragraphs from the help file quoted above.

"If the operation succeeds, the method returns True as a boolean value."

"If the operation succeeds, the method returns an XML string that represents the eConnect XML document that was created."

WHY????

Despite having done at least a dozen eConnect 2010 integrations, I apparently hadn't paid close enough attention to that, so I discovered this the hard way:  By having to submit a support case.  I had upgraded an integration from GP 10, and when I had to create my two eConnect methods, I created a CreateEntity method, then copied that to do a CreateTransactionEntity method.  I just changed the eConnect method name and that was it.  Except that it wasn't.

I forgot to change my return variable from a bool to a string.  When you make that mistake, you will get this error:

Unexpected error in InsertTransaction: Conversion from string " < eConnect xmlns:xsi="http://www." to type 'Boolean' is not valid.

After inserting the transaction, eConnect returns the XML string, but the code fails when it tries to send that into your boolean return variable.

Strangely, the .NET compiler didn't catch the mistake, so I thank Chris Roehrich at GP support for pointing out my mistake.

UPDATE:  I just figured out why the .NET compiler didn't catch the problem.  This particular eConnect upgrade was the first time I had upgraded an old VB eConnect integration to GP 2010.  It seems that the VB compiler does not detect the type mismatch.  I just tried the same thing in C#, and the compiler picks it up right away.  All of my previous eConnect 2010 integrations and upgrades have been in C#, which is why I never had the issue before.

Why they couldn't just make both methods have the same return type...well, again, I hope there was a some thought put into it and there is a reason and a benefit...

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

The Not So New World Order- Cost Variances and Dynamics GP

Once upon a time, Dynamics GP calculated cost variances for FIFO/LIFO perpetual valuation method items in purchase order processing, sales order processing, and/or inventory control and didn't always post to the general ledger.  This was widely, I think, understood and the importance of printing and reviewing the Cost Variance Journal was drummed in to users collective noggins everywhere.  And then came Dynamics GP 9.0.  Yes.  9. Point. O.

Why am I writing about this now?  Well, although there are definitely articles out there on cost variances, it seems like most of them are from the pre-9.0 world or have not been completely updated correctly.  So, after a lovely chat with Pam Peterson at Microsoft today, I thought...heck, why not, let's lay it all out for everyone!

In the purchase order processing training manual, there is a section (at the end of Receivings Transaction Entry) that outlines three different kinds of cost variances related to FIFO/LIFO perpetual items.  Here they are in a nutshell.

Scenario #1

• Item has a quantity on hand of 5 with a current cost of $1
• A quantity of 15 is sold, causing the item to go negative, this sale would be recorded with a unit cost of $1
• So, in the IV10200 (Inventory Purchase Receipts table), you would now have an override layer for the amount that was overriden (in this case, 10)
• A quantity of 20 is now received and invoiced in purchasing, with a unit cost of $1.25
• This creates a variance of $2.50 (.25 variance X 10) for COGS and Inventory
• This variance will print on the Cost Variance Journal when the receipt is posted
• Now, here is where it gets tricky...
• In GP 8.0 and earlier, this variance would NOT be automatically created in the GL
• In GP 9.0 and later, this variance IS automatically created in its own GL entry

On a side note, the adjustment outlined above is only created automatically if the transaction that caused the override was recorded in the system in GP 9.0 or later.  This ensures that the "outflow" record is properly recorded in the IV10201 (Inventory Purchase Receipts DetaiL) table.  When upgrading from versions earlier than 9.0, the system arbitrarily selects the most recent receipt for an item and creates a dummy outflow for it only.  For this reason, if you test this process using an item in Fabrikam that is already negative, it will most likely NOT create the adjustment automatically.  This is due to the fact that the data in Fabrikam has been upgraded from version to version.  If you want to test out this scenario, make sure you do the whole process, and don't just start with an item that is already negative.

Scenario #2

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25
• This results in a $2.50 variance between the original cost posted and the the invoice (.25 variance X 10)
• In this case, the difference is handled in the posting of the invoice.  It will be recorded to the inventory account or to the purchase price variance account (depending on if revalue has been selected on the Item Purchasing Options, Cards-Inventory-Item Purchasing Options).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Scenario #3

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 15 is sold to a customer, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25 (price to customer $3/each)
• This results in a $1.25 variance between the original cost posted and the the invoice (.25 variance X 5)
• In this case, the difference is also handled in the posting of the invoice.  It will be recorded to cost of goods sold, inventory, and purchase price variance (if the item is not marked to revalue).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Hope this helps to simplify cost variances, and the associated GL impact in GP.

For more information on the GL entry that is created by the variances, refer to TK #2448193.  And for more information on cost variances and perpetual valuation methods, including average perpetual, refer to this article.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Sherlock Holmes I Am Not- Finding Missing Deductions and Benefits

This week I am spending a couple of days back in the old neighborhood (Denver, Colorado) training a client on reporting tools (Report Writer, SmartList Builder, and SQL Reporting Services).  After spending this morning discussing table structure and selecting a reporting tool, we dove in to working on reports this afternoon.  In our brainstorming session for reporting ideas/issues, I was asked for a way to "check" if employees had been set up for the occupational tax here in Denver.  We do this by assigning to a benefit and deduction for the "head tax", rather than using the local tax feature in GP as there are some unique aspects to how Denver calculates the tax.

It is easy enough with SmartList or even Report Writer to test for those that do have the benefit or deduction.  But it becomes a little more difficult to test for something that is not there.  Especially when the employees would have other deductions and benefits, so we really only want to know those that are not set up for these specific codes.  So after a lovely visit to the new IKEA store here in Colorado (oh, my, yes I love me IKEA), and a decent dinner, I came back to the hotel to chew on how to best test for something not existing.  And in that, I found my answer...the NOT EXISTS function in SQL.  Ah, yes. 

Here is what I came up with to test for both scenarios.  The first part tests for the OCCPAT deduction, and displays the employee ID, name, and a message if it does not exist for a record.  And then the second part tests for the corresponding OOCCPUP benefit and returns a similar set of data.  One lovely list.  To be included in my lovely SQL report.

SELECT a.employid, a.frstname, a.lastname, 'NO OCCPAT Deduction' from UPR00100 as a where not exists (select * from UPR00500 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.deducton='OCCPAT') and a.inactive=0
UNION
SELECT a.employid, a.frstname, a.lastname, 'NO OOCCUP Benefit' from UPR00100 as a where not exists (select * from UPR00600 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.benefit='OOCCUP') and a.inactive=0

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.