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

---

OSI Layers Model

IntroductionDuring the early years of our modern computer era, very... Read More

15 Questions to Ask Your Software Vendor

When making a decision to buy any piece of software... Read More

Microsoft Great Plains: If You are Orphan Client ? What to Do and FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software eEnterprise,... Read More

Great Plains Dexterity History and Programming Overview

As of now - Great Plains Dynamics/eEnterprise is transformed/renamed into... Read More

SyncUp ? A File/Folder Synchronizer For Windows

SyncUp, a file synchronizer is designed to assist the home... Read More

Microsoft Great Plains - Payroll & HR Inexpensive Solution? Not Any More

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

Microsoft Navision Database Selection: C/SIDE or MS SQL Server - Overview For IT Specialist

There are certain pluses and minuses in both cases and... Read More

Can Group Collaboration Software Meet Business Needs?

According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric... Read More

Three Steps To Windows Safety Heaven

Now there are Three Steps To Heaven Just listen and... Read More

Preventive Maintenance Software Companies

Several software companies design programs for preventive maintenance. Most of... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

Unwanted Files

A LOT OF UNWANTED FILES.When you uninstall an item of... Read More

Microsoft Great Plains: Large Scale Implementation

Microsoft Business Solutions Great Plains, Navision, Axapta, Solomon and CRM... Read More

The Dreaded Paper Label - Should it be Used?

While paper labeling CDs and DVDs may appear to be... Read More

Think Of This

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

FreeDOS

Before September 1995, Microsoft Windows was an MS-DOS program. DOS... Read More

Brief Notes About Mainframe Software Pricing

We don't think about mainframe software pricing anymore, we just... Read More

Google Brings the Earth to Your Desktop

Google Inc. has launched a new software package that allows... Read More

Great Plains Customization ? Programming Auto-apply in Accounts Receivable

Microsoft Great Plains is one of three Microsoft Business Solutions... Read More

Crystal Reports - Geterogeneus databases: SQL Server, Oracle, Unidata, Btrieve ? Tips For Developer

Crystal Reports is the most flexible tool on the market... Read More

.NET :Solving the Multiple Inheritance Issue Under .NET Platform

.NET platform does not support multiple inheritance. Do not confuse... Read More

Adware and Spyware

What is 'adware'?Adware is basically software or scripts that are... 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

Corporate ERP: Standard vs. Rich functionality ? Microsoft Great Plains

Traditionally we were considering functionally rich systems, such as SAP,... Read More

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

This is intermediate level SQL scripting article for DB Administrator,... Read More

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW ? ReportWriter ? Tips for Developer

Microsoft Business Solutions Great Plains is written in Great Plains... Read More

Fleet Maintenance Software Reviews

Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More

An Easy Way to Develop JAVA Enterprise Applications

Research bears that less than 70 percent of development projects... Read More

Lotus Domino: Application Integration ? A Programmer View

There are two approaches for application integration:? Programmer's approach ?... Read More

Separate Anti-Keylogging Protection: Who Needs it Most?

If there still are few unprotected computers left, I haven't... Read More

Microsoft Axapta, Navision or Great Plains: ERP Selection for Large Corporation

If you would like to pick something from Microsoft, or... Read More

Linux ? Keyboard Or Mouse

Just stress testing one of the latest Linux distributions. Been... Read More

Basic Tips and tricks for Windows XP

Running Applications in Compatibility Mode With Windows XP, you can... Read More