The Story behind this topic
Microsoft Dynamics GP Project Accounting is one of those wonderful module that can track Time & Materials for your resources. Until GP 2013R2 it was using the companion product Personal Data Keeper (aka PDK) that was integrated with the Business Portal (BP) for many years to allow end-users to quickly enter their Time & Expenses towards Project Accounting (PA) in GP. Though this no longer applies for newer GP versions like 2015 and up, the content of this blog post could still be used for other modules and I’m going to post another example with similar actions.
Recently the payroll master at the company I was working for was worried that if I wouldn’t be available one day to return a posted TS report for correction, he would be get in trouble, as much of his reporting was relying on the PDK transaction tables. You need to know that BP sends the data first to a set of PDKxxxxx tables in each GP company before they get posted to the PAxxxxx tables for financial transactions posting.
Those PDK tables do not have history tables, so everything remains forever in ‘work’ tables. The TimeSheet (TS) data uses one set of header & detail tables, whereas Employee Expenses (EE) uses another set of tables. The PA module in GP covers more transaction types than those two, but lets focus on TS transactions for now.
The TS header tables carries a status field that tells PDK the stage of each TS when it moves along from BP entry to PDK approval & processing, until PA posting. Sufficient to know that once a TS is submitted by an employee, the status is 5 (Submitted) and once it’s approved (7), processed (8) and finally posted (9) GP, it updated several times its value.
GP PA has a way to reverse a transaction that is posted in GP once it is posted (9), but due to a bug (or design flaw), the status of the TS itself never gets updated, thus forcing you to do this manually with a SQL script from the backdoor in the GP company DB. The transaction reversal in GP negates all amounts posted towards a project, but leaves you only with the option of submitting a new TS report, which due to reporting source coming from the PDK tables, would raise a concern as the data would just double up for an employee in the same time period, which of course is not good.
The same happens when a TS report has been processed (8) but not posted yet in GP, it is simply hold in a PA batch ready to post. Unfortunately, if you realize there was a mistake in the TS and you delete the batch, thinking you can just fix the TS & re-submit it, than you’re wrong. GP would flag (correctly) the TS as deleted (11) after deleting the PA batch and the corresponding transaction. But a TS with status 11 isn’t visible anymore in PDK to be managed.. thus having to grab back to SQL again.
How Can GP Power Tools help me out here ?
Now you may ask, what does GP Power Tools (GPPT) have to do with PDK ? after all, GPPT is only available in GP, not in PDK.. It doesn’t really matter, as we want to use GPPT to fix the design flaw that prevents a proper status management of a returned TS report.
Well, here comes the handy part of GPPT called ‘Developer Tools’ which is one of the four modules that you can license in GPPT. If you haven’t purchased, I hope you’re going to change your mind after reading thru this blog post.
Basically the Developer Tools in GPPT allows for powerful scripting of various languages and setting triggers to act either on-demand or automatically, depending on your needs. The supported languages range from Dexterity, Transact-SQL, Visual C# and Visual Basic.Net. The GPPT Dev tools allow even for writing custom functions for GP Report Writer fans, but that’s a topic for another time.
You got me hooked, where do I start ?
First, as with most GPPT Dev tools project, well you’re best to start by creating a GPPT Project 🙂 .. In GPPT, the project will hold together all the various components, the parameters, triggers, code & security for user / company assignment. This also simplifies the export / import of the project component to another GP instance, lets says when you’re the happy owner of a dedicated TEST environment and want to put your fully tested code into the PRODuction environment.
This can be done under : GP Power Tools Main page > Cards > Project Setup
Tick the box “Current Project”, so that during the time you’re working on this, you’ll not have to lookup the project ID every time you open this form. For now the list in the lower section is empty, but it fill as we move forward. Once your project is completed, you’ll have a nice overview of all the elements and a double-click on one element will just open the corresponding form.
If you intend to export this project to an identical environment where your users & companies are the same, tick the other box “Transfer User and Company details”. During the export, GPPT will also include this information.
Lets continue with the easy stuff.. Next we’re going to define a trigger which will help firing the code that we need. Select from the GPPT Main page > Cards > Trigger Setup and enter the information like below.
Pay attention to the details and select the PA module in GP for the trigger to be set on the PA Adjustment Timesheet entry form. At the bottom you can also define a GP Menu entry that the end-user will be able to see in the ‘Additional’ menu of the form.
Next, we’re going to define the parameters that will be required for our TS report reversal. Remember that our goal is to make that posted TS visible and modifiable again thru PDK or BP. In order to do so, you’ll have to define a parameter field in GPPT to allow for the entry of the TS document number (something that looks like this ABCDE2008-TS-052618-1, the part being the Employee ID in GP). Open the GP Power Tools Main page > Cards > Parameter Lists
The TS report document number is field of 31 characters in the tables, but unless you use very long Employee ID’s, a field of type ‘String’ with max 30 chars should do the job.
You can click on the Preview button in the menu bar and get a glimpse of what the input prompt is going to look like :
As this goal of this blog post is to show a simple step-by-step approach to start using the Dev Tools of GPPT, we’re not going to put a lot of validation into the input form, but it could be done for sure. When closing the preview form, GPPT will prompt you to show that the created parameter actually works.
Alright, I’ve the basics.. show me the meat!
Not so fast!.. Remember that we talked about SQL code ? Before I had to implement this customization, I was using a T-SQL script to change the status of the TS report. This was looking similar to this :
UPDATE PDK10000 SET PDK_DOCUMENT_STATUS = 5 WHERE PDK_DOCUMENT_STATUS = 9 AND PDK_TS_NO LIKE 'ABCDE2008-TS-052618-1' AND PAYR=2018 --> 2=saved, 3=returned, 4=ReSubmitted, 5=submitted, --> 7=Approved, 8=Processed, 9=Posted, 11=Deleted
The important part here is the PDK_TS_NO field where you match the current TS report you want to return. The payroll year field (PAYR) is optional, as it is very unlikely that you’re going to have 2 years with the exact same period beginning (the lat part of the TS doc number), but if you want to be safe, include it.
The TS status at the bottom are just comments to show you their real meaning within PDK and BP.
In Part 2 of this blog post, I’m going to show you how you can put that T-SQL code into work with the help of GPPT Developer Tools.
Stay tuned and enjoy the nice summer!
@GP_Beat
The Dynamics GP Geek Blog 