Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

This is intermediate level SQL scripting article for DB Administrator, Programmer, IT Specialist

Our and Microsoft Business Solutions goal here is to educate database administrator, programmer, software developer to enable them support Microsoft Great Plains for their companies. In our opinion self support is the goal of Microsoft to facilitate implementation of its products: Great Plains, Navision, Solomon, Microsoft CRM. You can do it for your company, appealing to Microsoft Business Solutions Techknowledge database. This will allow you to avoid expensive consultant visits onsite. You only need the help from professional when you plan on complex customization, interface or integration, then you can appeal to somebody who specializes in these tasks and can do inexpensive nation-wide remote support for you.

Let's look at interest calculation techniques.

Imagine that you are financing institution and have multiple customers in two companies, where you need to predict interest. The following procedure will do the job:

CREATE PROCEDURE AST_Interest_Calculation

@Company1 varchar(10), --Great Plains SQL database ID

@Company2 varchar(10),

@Accountfrom varchar(60),

@Accountto varchar(60),

@Datefrom datetime,

@Dateto datetime--,

as

declare @char39 char --for single quote mark

declare @SDatefrom as varchar(50)

declare @SDateto as varchar(50)

select @SDatefrom = cast(@Datefrom as varchar(50))

select @SDateto = cast(@Dateto as varchar(50))

select @char39=char(39)

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AST_INTEREST_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [dbo].[AST_INTEREST_TABLE] (

[YEAR] [int] NULL ,

[MONTH] [int] NULL ,

[COMPANYID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ACTNUMST] [char] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[BEGINDATE] [varchar] (19) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ENDDATE] [varchar] (19) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[YEARDEGBALANCE] [numeric](19, 5) NULL ,

[BEGBALANCE] [numeric](38, 5) NULL ,

[ENDBALANCE] [numeric](38, 5) NULL ,

[INTERESTONBALANCE] [numeric](38, 6) NULL ,

[INTERESONTRANSACTIONS] [numeric](38, 8) NULL ,

[INTEREST] [numeric](38, 6) NULL ) ON [PRIMARY]

exec("

delete AST_INTEREST_TABLE where [YEAR] = year("+ @char39 + @Datefrom + @char39 +") and [MONTH]=month("+ @char39 + @Datefrom + @char39 +")

insert into AST_INTEREST_TABLE

select

year(X.BEGINDATE) as [YEAR],

month(X.BEGINDATE) as [MONTH],

X.COMPANYID,

X.ACTNUMST,

X.BEGINDATE as BEGINDATE,

X.ENDDATE as ENDDATE,

X.YEARBEGBALANCE as YEARDEGBALANCE,

X.YEARBEGBALANCE+X.BEGBALANCE as BEGBALANCE,

X.YEARBEGBALANCE+X.ENDBALANCE as ENDBALANCE,

X.INTERESTONBALANCE as INTERESTONBALANCE,

X.INTERESTONTRANSACTIONS as INTERESONTRANSACTIONS,

X.INTERESTONBALANCE+X.INTERESTONTRANSACTIONS as INTEREST

--into AST_INTEREST_TABLE

from

(

select

"+ @char39+ @Company1 + @char39+" as COMPANYID,

a.ACTNUMST,

"+ @char39 + @Datefrom + @char39 +" as BEGINDATE,

"+ @char39 + @Dateto + @char39 +" as ENDDATE,

case when

b.PERDBLNC is null then 0

else b.PERDBLNC

end as YEARBEGBALANCE,

sum

(

case

when (c.DEBITAMT-c.CRDTAMNT is not null and c.TRXDATE ="+ @char39 + @SDatefrom + @char39 +" and c.TRXDATE =year("+ @char39 + @Datefrom + @char39 +")

where

a.ACTNUMST>="+@char39+@Accountfrom+@char39 +"

and a.ACTNUMST="+ @char39 + @SDatefrom + @char39 +" and c.TRXDATE =year("+ @char39 + @Datefrom + @char39 +")

where

a.ACTNUMST>="+@char39+@Accountfrom+@char39 +"

and a.ACTNUMST

---

Linux Vs. Windows

This article will not attempt to advocate the use of... Read More

Crystal Reports For Microsoft Navision - Overview For Programmer/IT Specialist

Let us give you - developer some hints in the... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in... Read More

Great Plains DOS Support ? Notes for Consultant

Great Plains Accounting, accounting package for mid-size and small companies... Read More

Easy Guide to RAID Recovery

What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More

Microsoft Great Plains as ERP and Microsoft CRM as CRM

If you have Microsoft Great Plains and support it for... Read More

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More

OS 101: Suggestions for Choosing an Operating System

An operating system (abbreviated OS) is essentially the path through... Read More

5 Mac Security Tips You Can?t Live Without

So, you've bought a new Macintosh, and now you may... Read More

Microsoft Great Plains POP: Purchase Order Processing ? Overview For Consultants

Great Plains Purchase Order Processing (POP) module makes up one-third... Read More

Databases ? How We Love to Hate Them!

You've finally created databases that you can actually use to... Read More

Think Of This

Think of this, first we had the HAM Radio, then... Read More

Secure File Transfer Using SSH Plus Additional Audit & Automation - FSA Reporting

In order to meet regulatory and corporate compliance requirements reporting... Read More

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

Vlans

In order to implement VLANs in a network environment, you'll... Read More

How to Upgrade Dexterity Customization ? Tips for IT Manager

If you have Microsoft Great Plains and support it... Read More

eConnect: eCommerce Development for Microsoft Great Plains

Microsoft Business Solutions Great Plains has several options to enable... Read More

The Hidden Power of Online Manual

Writing software manuals is boring, isn't it? We often think:... Read More

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

Is Software Tester a Most Infamous Person in a Software Project Team?

The fact that a software tester is a most infamous... Read More

Microsoft CRM Customization ? Programming Closed Email Activity

Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

The XP Firewall Isnt Enough

You might think you don't need a firewall... Read More

Anti-Spyware Protection ? Holes in the Shining Armor

Looking at all the ads which promise to get rid... Read More

Microsoft Great Plains Multicurrency ? Overview For Implementation Consultant

When you first think about multicurrency ? you probably have... Read More

Manufacturing Solutions for Microsoft Great Plains ? Overview for Consultant

Microsoft Business Solutions Great Plains has full-featured manufacturing set of... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

At the end of XX century, in the late 1990th... Read More

The Religion And Philosophy Of Small Internet Business

I have always had a tendency to focus on the... Read More

Great Plains Dexterity: Customizations & Source Code Programming

Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

Microsoft Great Plains customization ? Freight Forwarding/Transportation industry example

Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More

ERP Implementation: Success Factors

As seeing large number of implementations ? in our case... Read More

3 Reasons Why Medical Billing Software is Leading the Way

Since technology changes so quickly, it is hard to begin... Read More