BSankaran

Payables Aging View

2011-12-02T12:26:00.001+05:30

Hi All,

Most of my customers have always asked this to be on excel, as the aging in GP does not produce it over in excel. A simple yet powerful script.

Regards,

Bala

If Exists(Select * From Sys.Objects Where Name = N'GSPayablesAging' And Type = 'V')
Begin
    Drop View GSPayablesAging
End
Go
Create View
    GSPayablesAging
With
    Encryption
As
Select
    LTrim(RTrim(A.VendorId)) As VendorId,
    IsNull((Select IsNull(VendName,'IsBlank') From PM00200 Where VendorId = A.VendorId),'IsBlank') As VendName,
    LTrim(RTrim(A.BchSourc)) As BchSourc,
    IsNull(A.DocDate,'01-Jan-1900') As DocDate,
    IsNull(A.DueDate,'01-Jan-1900') As DueDate,
    IsNull(A.DocType,0) As DocType,
    Case A.DocType
        When 1 Then 'Invoice'
        When 2 Then 'Finance Charge'
        When 3 Then 'Miscellaneous Charge'
        When 4 Then 'Return'
        When 5 Then 'Credit Memo'
        When 6 Then 'Payment'
    Else
        'Un Known'
    End As DocTypNm,
    LTrim(RTrim(A.VchrNmbr)) As VchrNmbr,
    LTrim(RTrim(A.DocNumbr)) As DocNumbr,
    A.TrxDscrn As TrxDscrn,
    A.CurncyID As CurncyID,
    IsNull((Select IsNull(CurrnIdx,0) From Dynamics..MC40200 Where CurncyId = A.CurncyID),0) As CurrnIdx,
    IsNull((Select IsNull(FunlCurr,'IsBlank') From MC40000),'IsBlank') As FunlCurr,
    IsNull((Select IsNull(FunCrIdx,0) From MC40000),0) As FunCrIdx,
    IsNull(B.XchgRate,0) As XchgRate,
    IsNull(B.ExchDate,'01-Jan-1900') As ExchDate,
    Case A.DocType
        When 5 Then (Abs(A.DocAmnt) * -1)
        When 6 Then (Abs(A.DocAmnt) * -1)
    Else
        Abs(A.DocAmnt)
    End As DocAmnt,
    Case A.DocType
        When 5 Then (Abs(IsNull(B.OrDocAmt,0)) * -1)
        When 6 Then (Abs(IsNull(B.OrDocAmt,0)) * -1)
    Else
        Abs(IsNull(B.OrDocAmt,0))
    End As OrDocAmt,
    Case A.DocType
        When 5 Then (Abs(A.CurTrxAm) * -1)
        When 6 Then (Abs(A.CurTrxAm) * -1)
    Else
        Abs(A.CurTrxAm)
    End As CurTrxAm,
    Case A.DocType
        When 5 Then (Abs(IsNull(B.OrCTrxAm,0)) * -1)
        When 6 Then (Abs(IsNull(B.OrCTrxAm,0)) * -1)
    Else
        Abs(IsNull(B.OrCTrxAm,0))
    End As OrCTrxAm,
    IsNull(GetDate(),'01-Jan-1900') As 'Aging Date',
    Case
        When IsNull(A.DueDate,'01-Jan-1900') > IsNull(GetDate(),'01-Jan-1900') Then IsNull(DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')),0)
    Else
        0
    End As 'Aging Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) < 0) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As 'Not Yet Due',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 0 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 30) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '0 - 30 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 31 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 60) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '31 - 60 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 61 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 90) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '61 - 90 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 91 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 120) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '91 - 120 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 121 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 150) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '121 - 150 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 151 And DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) <= 180) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '151 - 180 Days',
    Case
        When (DateDiff(dd,IsNull(A.DueDate,'01-Jan-1900'),IsNull(GetDate(),'01-Jan-1900')) >= 181) Then
        Case A.DocType
            When 5 Then (Abs(A.CurTrxAm) * -1)
            When 6 Then (Abs(A.CurTrxAm) * -1)
        Else
            Abs(A.CurTrxAm)
        End
    Else
        0
    End As '181 Days And Above'
From
    PM20000 A
    Left Outer Join
    MC020103 B
On
    (A.VchrNmbr = B.VchrNmbr And A.DocType = B.DocType)
Where
    A.Hold = 0
    And
    A.Voided = 0
Go
Grant Select On GSPayablesAging To DynGrp
--Select * From GSPayablesAging

Analytical Accounting Analysis

2011-08-12T12:15:00.002+05:30

Hi All,

Its been a while, since I had done a post. The current blog is all about using SQL Views to produce reports on Analytical Accounting module of Dynamics GP. All along AA has been a very powerful tool to track additional information on transactions affecting General Ledger. Having said that, as powerful as the module itself, AA lacks very much interms of reporting. The Multi Query report of AA is too complex that a normal customer would not have enough knowledge to generate the reports the way they want.

Having faced the same issue with a variety of customers, I developed this SQL view which would generate all the GL / AA information in one single query that can be used by the customers to generate custom reports.

GSAADimensionAnalysis

NB : Check the version information, to get the latest file.

Dynamics GP - User Preferences

2011-03-05T00:01:00.000+05:30

Hi All,

Its been a while, since I had made a blog. Well as my character is defined, I guess I am a bit too lazy to do come and blog. Trying to overcome this as I type this blog. Coming back to Blog, I have always been asked this question, by almost all my customers and hence the blog.

The question is always like this. " I am comfortable using the Return Key (Enter Key) to shift between fields in my old ERP. In Dynamics GP, I have to use Tab Key.". This is confusing to me and makes my life difficult. Can you please help me ?".

My answer is always a Smile and a Big YES. I am here by mentioning the steps that you need to do in order to make this changes. Login into Dynamics GP. Once logged into Dynamics GP, go to the Shortcuts tab on the Home Page. Click on the User Preferences option the Shortcuts. This would open up the User Preferences window within Dynamics GP. Right on the screen you would have an option called Entry Key. Select the option you want and Click on the OK button.

Bingo, you have made the changes.

Regards,

Balaji

NB : The picture attached with this blog explains the step by step approach.

Dexterity Warning Message With Carriage Return

2010-02-11T23:54:00.000+05:30

Hi All,

Welcome back to another blog of mine. This time the Blog is on Microsoft Dexterity For Dynamics GP. A very nee-sh market segment yet very famous within the domain of Small & Medium Business Applications. Throughout the application programming, we have come across various stages we would have to use Warning Messages. One of the easiest way to setup a warning message within Dexterity is to use the keyword warning which would throw up a system based warning message. This can be pretty much compared with Message Box function within .Net, with very limited features. The code to produce a simple warning message would be as follows

Normally when we use a warning statement, we would get a Model Dialog window with the message written on it. This would look as shown below.

Looking at the picture one can be certain of the fact that the warning dialog produces a string based result. In order for us to incorporate Carriage Return based message we should first make sure that the message is text based message. A text based message can, within Dexterity can be simply produced by converting a string to text. This can be achieved by using the keyword text before the actual message. The code for the same would look as follows.

Now while we debug this piece of code, we can see the difference in the way Dexterity handles the change in data type. The message box displayed by the above code would look as follows

A close look at the code reveals the fact that a text data type is the only one which would have an ability to handle a Carriage Return within Dexterity. Now lets attempt to generate a Dexterity Based Warning Message using Carriage Return. The ASCII code for Carriage Return within Dexterity is char(13)

We would use this code to change the code so that the resultant message is capable of handling Carriage Return. Now lets us attempt to change the original code so that we are able to handle Carriage Return.

We The above mentioned code is pretty much self explanatory. A local variable with data type text is being initialized with certain data which contains Carriage Return. When the system warning message is used with this local variable, the displayed warning message would look as follows

Thus we have successfully created a Dexterity Based warning message with Carriage Return built into it. The same technique can be applied to bring about any other non - printable ASCII Codes within Dexterity, of course there are a few limitations.

InterfacedCOMDll

2009-11-02T23:20:00.002+05:30

Hi All,
When I stepped into the .Net world, it was a random jump from the world of ERP Programming. Till that point in time, I was more into changing objects, which were created not sure by whom, but had some kind of GUI associated with it and a business purpose behind it.

Once into the .Net world, things started changing drastically infront of my eyes. Things which I never imagined possible started emerging from no where. Trust me!! It takes allmost 30 lines of code, in my legacy programming language, to strip a FilePath and get the file name and that too, with the limiation of providing the extension as an option. !!.

Wohoooooo, Thats what I would call .Net. A very powerful collection of Object Oriented Environment that allows a developer to choose

What They Want
How They Want
Where They Want

Thats .Net for you all. Now let us come back to the topic. Ever since I started .Net programming, as it has been with every other beginner, it was really tough to get help for the problem that I face. Searching in Google / MSDN is the best option. Even with that there is very little help that one can get until one becomes an expert in understanding the Help itself. So much is there on the world of .Net, that the help itself is too complex to understand. I think, I have this habit of going too much out of the topic !!!!...I think, I am nuts !!..

Let's continue with the topic. We will take a look into the most basic function that one would do using .Net, which is creating a Dynamic Linked Library (*.dll). Lets understand a bit more about a DLL. The name DLL itself is self explanatory. It is a library of Dynamic Linked objects. The objects are called Dynamic because of the dynamic nature of the objects itself; which means that the component objects themselves are dynamic meaning, they exist only when required.

Lets go into a step by step example as to how to create a Dynamic Linked Library. The environment that I am using for this purpose is Visual Studio 2005 SP1. Having said that the same code would work moving forward and backward interms of environment variables. Now lets us see how to make a DLL and then expose the DLL to other COM Compatible applications.

The first step in creating the DLL's is to create a Visual Studio(VS) project. We would open up our VS environment and select a new project.

VS would now open up a Dialog window where as a developer one would be able to create umtine number of application starting right from a class all the way to a Website. As far as the current topic goes the plan is to create a DLL. So the choice automatically goes to a Windows Based Class(*.cs). Please refer the figure below as to how to setup the Project Properties.

The language preference is one the best features of .Net. It allows you to basically select the language of your choice. I am into C#, as far as development language is concerned. I will leave it upto you guys to decide which is the best language for you to go ahead. Once we setup the base of the project as per the above figure our next step would be go ahead and make the DLL itself. Now a few things that we need to take a look here before we make the launch. Ofcourse these are not very important, but would be a good addition for sure.

Upon clicking the OK Button on the above figure, VS would create and append all required references and base code for the DLL. You might have to wait for a while, ranging from a couple of seconds to a couple of minutes based on the system hardware that you have. System Hardware is my way of saying System Requirements. Please look into Microsoft website to figure out what is the system requirement to have VS 2005 SP1 running, of for that matter what ever is your development environment. Grrrr..I hate going out of topic...Man..I am seriously nuts !!!!

Please note that the next step is an optional step, as the system would anyway prefill all these information based on the VS setup itself. Just thought would share the way I do it. What I am doing here is that, I am going to show you how to setup the Assembly Information, as per your choice. For the current example, I am taking it as my Name itself (Balasubramonian Sankaran abbreviated as BSankaran :D). To do this right click on the Solution Name as shown in the figure below.

Now click on the Assembly Information button as shown in the above figure(Right Side). The assembly information window would open up. Fill up this window as per the choice of yours (Mostly your organization would have some standard in creating the DLL's. Touch base with your seniors !!!!)

Ok. Lets make a come back to our original project itself. Once we setup the above mentioned information, now we are all set to get to creating the Assembly. Please note that the system by default creates a unique GUID for the assembly. This would be used by the Registration tool (RegAsm.exe) to register the DLL to the registry of the operating system. The next step is to start creating the Assembly itself. You would have noted that I have changed the word DLL to Assembly in the last couple of lines. DLL is the extension of the file where as the name of the file type is called Assembly. Hence this change. I am taking you into .Net world slowly but steadily.

Now lets us take a look at the solution itself. You would have found that the system would have created a file named Class1.cs in the project. This is the main class for your project. Basically a class is a collection of various functions / procedures / attributes (You name it !!!). As far as the example goes, I am going to rename the class name to InterfacedCOMDll.cs. You can just right click on the Class1.cs and rename it just like how you rename any other file. This would automatically change the Assembly Information too (I have applied this change on the Assembly Information figure above).

Now the one of the most important step. On the top of the class, above the namespace tag you would notice that there are a couple of lines of code, which start with the word "using".These are basically the references that we are adding to this class of ours. You have to add the reference called System.Runtime.InteropServices. This is the reference that would allow you to make the Assembly expose to an interface and use the interface to call the various elements within the class itself. I have also added a reference to the System.IO class. This basically allows us to do some logging activities as you would see later in the blog. Overall your class would now look somthing like as shown in figure below

Now we are all set to create the Assembly. For the sake of this example, we are going to create a function that would write the System Information(Not all, but a few which I like plus the Stack) onto a log file. I have explained in the code what each line of code does. For simplicity, add the function within the class as shown in figure below

Now overall our project looks good. Till now all that we have done is creation of a public method within the class. I am sure all of you would have done this. The next step is the most important of all. That is what the Blog is all about. How to make the Assembly Interfaced. We will add an interface to this class. What is an interface. As the name indicates, it is a means to reach the class when exposed to COM. The easiest way to create an interface is to just click inside the class. Right Click >> Refactor >> Extract Interface.

Or alternatively you could use the Project >> Add New Item >> Interface. The latter would require you to add the interface manually, where as the former would provide you a GUI based window where upon you would be able to make the selection. Voila !!! You have created the interface for the class. You will now notice that the an additional class file (*.cs) has appeared on your project plus the namespace has changed in your project file. This is because of the interface. You would need to manually remove any additional item from the namespace. The name space would ideally have to be ClassName : InterfaceName

Now last but not least. Remember the namespace and the class name. Above that add the following attributes that allows the class to be interfaced when used by a COM supportive application.

To know what the above mentioned properties does, please take a look at your COM Object Handbook or you can just ask your senior. I am planning to do another blog on that regard. Lets leave it as of now. What is more important is that you make these changes to your script. Please note that the ProgId can be set to any string. As a tribute to this blog, you can even put my name :). Now atleast you all will agree that I am really nuts !!

Coming back to the project. You have successfully interfaced the class that we created and now we are all set to initialize the class. Use the RegAsm.exe, which is part of your .Net Framework and register the DLL.

Open the VS Command Promt or use the Normal Command Promt. In the case of the later, you would have to manually navigate to the .Net Framework directory. Use the below mentioned scripts to register & unregister the Assembly.

Un Register regasm \u "FullyQualifiedPath\AssemblyName.dll"

Now that makes the DLL Interfaced and COM Compatible. You could use this DLL with any COM Compatible application and make use of the DLL itself. Wow I have completed my Blog !!!!

Feel free to comment on the blog. Your comments are most appreciated !!

Thanks & Best Regards
Balaji

Source Code
Link http://www.teradepot.com/lrkd773cypdz/InterfacedCOMDll.rar.html
Password balaji1983