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

15 Questions to Ask Your Software Vendor

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

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance of Daffodil DB / One$DB... Read More

Free Software for Newbies and Web Developers

Here is some free software tools to help you build your own websites and create... Read More

HSphere Control Panel Tips and Tricks - Power At Your Fingertips: Part 1

The first topic we are going to discuss is email. HSPHERE (the software... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the traditional CRM providers as Siebel,... Read More

Microsoft Great Plains Integration Manager ? Advanced Techniques

Great Plains Integration Manager scripting and translation - overview for programmer, software developer, ... Read More

Microsoft Great Plains: carpet, textile, fabric, felt distributor ? implementation overview

In this small article we will show you the possible way of deploying Microsoft Business... Read More

Reduce TCO: The Java Database Way

TCO (Total Cost Ownership) is the buzzword in today's business world. This metric... Read More

Benefits of Integrating Online Chat Software with CRM

Customer Relationship Management (CRM) is a strategy and processes used to learn more about customers'... Read More

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

Let us give you - developer some hints in the report creation. C/SIDE ... Read More

MultiNational Corporation ERP Implementation ? Microsoft Business Solutions Great Plains

If you look back to the history, you will see that ERP for large publicly... Read More

Groupware and Online Collaboration: Collaboration Series #4

This article is the fourth of a series of articles exploring specific aspects of groupware.... Read More

Daffodil DB: Web Database

What is a Web Database?A web database is a database for... Read More

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software program, but today I'm so... Read More

Spyware, Adware, etc. -- Terms and Common Sense

When reading an article where some term is used often, it is useful to make... Read More

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with an interesting screensaver. But wait...... Read More

Software Companies: Generate New Revenue Streams and Decrease Costs with Custom e-Learning Content

It's no secret that software companies operate in a very competitive space where rivalry is... Read More

Linux Vs. Windows

This article will not attempt to advocate the use of Linux over Windows or vice... Read More

Keeping Track

One of the things we can be as certain of as death and taxes is... Read More

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

Many reasons made GBM a unanimous choice for experts, one of them is the fact... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in the whole spectrum of industries... Read More

Dashboard Widgets for Windows

For a windows user like me, just can watch with envy the new eye candy... Read More

Introduction To ISDN, Part II

In the previous ISDN article, we looked at how and why one router dials another... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability to convert printed information into... Read More

Spyware, What is it?

Spyware, what it is and what it does. Basically, spyware is a program put on... Read More

Microsoft Great Plains Logistics & Warehouse Management ? Implementation & Customization Highlights

Logistics automation is often considered as barcoding extension to Sales Order Processing, Purchase Order Processing,... Read More

What To Do When Windows Wont Boot

When Windows fails to boot it is normally caused by you installing a program or... Read More

C++ Tutorial 1, Introduction to C++

Introduction to C++Why Learn C++?C++ may at first seem like a boring, confusing programming language... Read More

Microsoft Great Plains 8.0 Brazilian Version ? Overview For International Consultant

Microsoft Great Plains has substantial mid-market share in the USA plus due to the ... Read More

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

In order to meet regulatory and corporate compliance requirements reporting members will require a cost... Read More

10 Programming Tips

(1) Avoid using the same variable again and again for different purposes.This happens with us... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market ERP or so-called standard functionality... Read More

Microsoft CRM Integration with Microsoft Retail Management System (RMS) ? Overview

Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS are both Microsoft SQL Server... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies as well as Navision (which... Read More

Microsoft eCommerce Web-development: Great Plains eConnect .Net ? Highlights for Programmer

In our small article we'll consider Microsoft Business Solutions Great Plains Sales Order Processing module... Read More

Understanding XML Server

XML Server can be a Web Server that stores the XML files in it and... Read More

Adware, What Is It?

Adware is a type of Spyware program that displays some kind of banner advertising as... Read More

Editing Your Photos Using Microsoft Picture It Publishing Platinum 2002 - A Great Dinosaur

I started using PIP (Picture It Publishing) Platinum 2002 right after I got it in... Read More

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

This is intermediate level SQL scripting article for DB Administrator, Programmer, IT SpecialistOur and Microsoft... Read More

Groupware Finds Home in American Business: Collaboration Tool Analysis

What is Groupware?Have you ever had to manage document collaboration in a hurry? No need... Read More

Free Software: How Not To Get More Than You Bargained For!

I completed an experiment recently. I wanted to find out exactly what software I could... Read More

EDI: Electronic Document Interchange for Microsoft Great Plains ? Overview for Software Developer/Pr

Microsoft Great Plains - Microsoft Business Solutions accounting and ERP system, originally targeted to mid-size... Read More

Pros and Cons of Using FREE Software in Your Business

It???s easy to understand why you might be drawn to the allure of free software.... Read More

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP software integrates business activities across... Read More

Microsoft Great Plains Chemicals & Paint Industry Implementation & Customization Notes

Microsoft Great Plains fits to majority of industries, in the case of Chemicals & Paint... Read More

COSMIC: A Small Improvement on the Symons Method

The COSMIC FP (function point) software quality metric, is no longer 'proposed' but an actual... Read More

Beware of The Pirated Software E-Mail Scams!

You have gotten those E_Mails buy software at deep discounts. It is very tempting to... Read More

ERP for Large Corporation ? New Selling Strategy

Our opinion is based on our Microsoft Business Solutions Great Plains, Microsoft CRM sales and... Read More

Lotus Domino: Reports and Connectors ? Crystal Reports, XML, ODBC/JDBC, OLE

Lotus Notes Domino is very efficient in electronic document workflow automation. Unfortunately the electronic document... Read More

Think Of This

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