Average Days To Pay Calculation – History & Open (SQL Script)


In my previous post, Average Days to Pay Calculation (SQL Code), I had provided a SQL stored procedure that calculates a customer’s ADTP for a given point of time (between two dates). While this was perfect, it does not include fully applied but open invoices.

Some of the readers (particularly Tim and Steve Pena) requested to amend the script to consider open invoices that are fully applied. An invoice remains open even after fully applied only when we do not run paid transaction removal (PTR). I wanted to work on this script as soon as possible, but somehow I could not.

Better late than never, isn’t it?

Please find the link below to download the SQL procedure that calculates a customer’s ADTP for a given point of time, but looks at both history (RM30101) and open (RM20101) tables, take invoices that satisfy following criteria:

  • Invoices that are fully applied.
    • If invoices are in history table, by default, current transaction amount would be zero.
    • If invoices are in open table, then take those invoices whose receivable outstanding amount is zero.
  • Invoices that are not voided.
  • Invoices that have a document amount, meaning non-zero.

I have verified this script against some sample customers for whom, invoices were either in history (RM30101) or in open (RM20101) or in both.

As always, feedbacks are welcome.

RM_AverageDaysToPay.sql

VAIDY

CBM Cash Receipt AA Journal Issue & Resolution


Couple of days back, a user reported an issue. When she tried to open CBM Cash Receipts window, following message appeared:

Screen Shot 2014-11-25 at 5.48.52 PM

For all other users, when they posted any Cash Receipt, an Analytical Accounting Journal report would open with some entries on it; particularly with 5 cash receipts. User, who reported the issue, confirmed that the records shown on AA journal report were posted by her and only after posting them, the above error message appeared.

Since the report is AA journal, I first checked the AA Sub Ledger tables (AAG20000 series) and confirmed that the entries shown on the AA journal report are present without any issues. If I get rid of the above error message, things would be normal and this particular user can continue with her entries. Task on hand is to find out AA journal report tables and see the stuck/orphaned records and clear them.

Issue: Records got stuck on AAG50000 (AA Sub Ledger Report Header), AAG50001 (AA Sub Ledger Report Distribution) and AAG50002 (AA Sub Ledger Report Assignment) tables.

Resolution: Remove them. Simple. What you MUST note here is that all records (including genuine ones) would be present on these tables. You MUST note down AA Sub Ledger Header ID of the ones that are stuck and use below query to delete records.

DELETE AAG50000 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...) 
DELETE AAG50001 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...) 
DELETE AAG50002 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...)

Replace <aaSubLedgerHdrID> with respective AA Sub Ledger Header ID of the stuck records.

NOTE: Always take a backup of records before you delete.

VAIDY

Simulate DEX_ROW_ID in a SQL View Using ROW_NUMBER() – #MSDYNGP


I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).

Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.

Dex View

Everything looks perfect till you actually see below error messages at runtime:

Dex View - Error 1

Dex View - Error 2

Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.

So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:

ROW_NUMBER USAGE

 

Definition of ROW_NUMBER() can be found here: ROW_NUMBER (Transact-SQL).

A simple yet powerful SQL function has given me the power to do what I wanted in no time. Oh, and my custom lookup referring to this view is working like a charm. Users are happy and so am I.

VAIDY

Delete a Company in Microsoft Dynamics GP – Compatible With GP 2013


We have a SQL script named ClearCompanies.sql, which is available on Customer Source or Partner Source. This script removes all references to those companies that are not available in SQL Server, but pretty much exist in GP records.

It’s an all important script for all implementers, developers and consultants. Now this script has been updated to cater for also GP 2013. I had not used this script for a long time, so never realised it till today. This is particularly important as GP 2013 now support multi-tenant architecture (multiple GP System DB on same SQL instance).

You can download this script from here (provided you have a Customer Source / Partner Source account): ClearCompanies.sql.

VAIDY

Management Reporter Configuration – Part Of Domain Error


I am not sure how many would have noticed this error message. Just thought of sharing this with you all.

I installed Management Reporter 2012 CU7 on my machine yesterday night for some testing. I had not done this outside a network (being physically away from a domain) before, so never expected an error. But following error message popped up:

Snip20140106_4

 

My machine is already part of a domain. So above message was a bit confusing. Later I realised that I MUST be connected to my domain when the configuration process is run.

Which means the machine on which you are installing Management Reporter Server component CANNOT be physically outside the domain (even though it is already added to one) when you configure it.

This morning I restarted the configuration process and it’s done without issues. Interesting.

VAIDY

GP Web Client: Rendering Issue – Some Facts


Almost a month back, I had posted my GP web client test drive results on how the client is rendered on Mac based browsers and possible issue with Silverlight plugin. I am probably wrong.

Everything works other than pictures; that’s what I had found. Upon drilling down further, what I realised is that it sounds obvious that it doesn’t work on Mac based browsers. Reason: Native Pictures.

Definition of Native Picture says following:

Snip20131230_12

Consider, for instance, the following snapshot of GP login window on a web client rendered on Mac Safari:

Snip20131230_10

It’s not shown. Initially I thought it was something to do with Silverlight rendering. But not exactly. It’s because, this picture is a Native Picture. And by definition, it’s specific to Windows OS. Look at this picture definition below:

Snip20131230_9

Apparently, by nature, it’s NOT supposed to show up on any OS other than Windows.

It’s not just this picture. Lookup Button icons, Note icons are all Native Picture types. And due to that, they are not going to render on any other OS. And if I am not mistaken, this will remain as it is at least till next major version of GP.

Those who implement GP web client MUST be aware of this.

VAIDY

Where Have #MSDYNGP Product Printable Manuals Gone?


I am not sure how many of us actually refer to GP user manuals; the ones that come in PDF format and contain module functionalities. But I do, religiously. When I came to know that they are not available offline anymore, I was a bit upset. After a bit of traversing here and there, I have finally found the location of them.

Take a look at below screenshot, which compares GP 2010 Printable Manuals menu and GP 2013 Printable Manuals menu:

Snip20131224_3

 

Apparently, when you click on that “Documentation and resources for Microsoft Dynamics GP” link from GP 2013 Printable Manuals window, it takes you to this webpage:

Snip20131224_4

 

And click on Documentation and resources for Microsoft Dynamics GP 2013 (highlighted above with RED box), you will be taken to following link: http://technet.microsoft.com/en-us/library/jj673201(v=gp.20).aspx.

From there, locate Printable Guides [GP 2013] as shown below:

Snip20131224_5

Once you click that, you will be taken to following link from where you can find all your module manuals: Printable Guides.

Biggest hassle is to click on each link found on this page, being taken to another page and from there download the PDF and it literally kill us. If they are available offline already on GP applications folder, like how it used to be before, it would have been awesome.

Never realised that Microsoft would put them all online and not plant them on our GP application folder anymore. Strange strategy.

VAIDY