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

---

Lowering The Risks In Developing Do-It-Yourself Software Projects

Mike Dunville* had a decision to make. As the new... Read More

Are You Waiting for Microsoft Longhorn Operating System

I love new technology. I am still ready to wait... Read More

Microsoft Great Plains, Navision, Axapta ? Selection Considerations

During the years of our consulting practice, which comes back... Read More

Vlans

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

How To Create A Data Capture Procedure Checklist For Your Small Business CRM Software

Fortunately one of the most common reasons cited for the... Read More

Destination: Desktop for Google

First we had the original Google search that evolved into... Read More

Software Automation Helps Increase your Bottom Line

When you own a small business, time is money. And... Read More

How to Make Own CMS

Every day millions of new web documents emerge on the... Read More

Monitoring Software Can be Used for Spying as Well

We all already got used to computer monitoring both at... Read More

Microsoft Great Plains Food Processing ? Implementation & Customization Highlights

Microsoft Great Plains might be considered as ERP platform to... Read More

Free PDF Publishing Software

In a previous article, I wrote about OpenOffice... Read More

Oracle Development: JDeveloper 10G ? Java, J2EE, EJB, MVC, XML - Overview For Programmer

In 2004 Oracle, Inc. made its new step toward J2EE... Read More

Industry Experts Answer the One Million-dollar Question - Why Genie Backup Manager?

Many reasons made GBM a unanimous choice for experts, one... Read More

Linux for Home Users

Hey Guys! Don't raise your eyebrows or fear by hearing... Read More

Microsoft Navision Integration with Microsoft RMS - Overview for IT Specialist

Microsoft Business Solutions Navision serves both European and American megamarkets.... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

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

Interactive Mapping Brings Information to Life

What is Interactive Mapping?Interactive mapping is a visual display medium... Read More

Adware and Spyware

What is 'adware'?Adware is basically software or scripts that are... Read More

What Stands Behind The Software Description?

With thousands of web pages added to the Net every... Read More

Freight Forwarding ERP: Microsoft Business Solutions Great Plains customization & setup ? overview

Microsoft Great Plains may be recommended for international freight forwarding... Read More

Cisco Certification: Introduction To ISDN, Part IV

In part III of this ISDN primer, we learned that... Read More

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

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

Microsoft Great Plains as ERP and Microsoft CRM as CRM

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

Running a Program on a Remote Server Using SSH

How do you run a program on a remote server... Read More

A Symons Mark II Function Point Counting Example

I provide, here clear explanations and a count of function... Read More

Microsoft CRM Programming Secrets ? Tips For Developers

This article is for advanced Microsoft CRM SDK C# developers.... Read More

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Groove Network. Are you in it?

If you are in a business that passes documents around... Read More

Keeping Track

One of the things we can be as certain of... Read More

Does your Company have Documentum?

Are you lost in the mess of documents that get... Read More

How to Choose the Right Accounting Software for Your Business

With any good luck and a good amount of hard... Read More

International Support - Microsoft Great Plains VAR/Partner Selection ? Overview for V.P. IT

Looks like Microsoft Great Plains becomes more and more popular,... Read More

Microsoft Great Plains eCommerce: overview for developer

Microsoft Business Solutions Great Plains was designed back in the... Read More