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

Data Quality Best Practices for Salesforce.com

Executive SummaryAn effective plan for entering, cleaning and updating the data for your salesforce.com (SFDC)... Read More

Microsoft RMS Customization ? PO Items Receiving in Great Plains

Microsoft Retail Management (RMS) and Microsoft Great Plains are retail and accounting/ERP solutions coming from... 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

How To Identify, Cure and Prevent Spyware/Adware Infections

Just when you thought you were Web savvy, one more privacy, security, and functionality issue... Read More

Microsoft Great Plains Accounting/ERP Implementation ? Finance Industry Customization Example

Microsoft Business Solutions Great Plains is very generic accounting application out of the box and... Read More

Top Questions to Ask Before Buying a Help Desk & Asset Management Software

#5 All your hardware components like Printers, PCs etc come under various maintenance contracts with... Read More

DVD Burning Tips

CD and DVD replication is a process that works by creating discs from scratch. Not... Read More

Groupware: Answers the 5 Questions of Document Collaboration

Every organization which creates collaborative documents, whether they are budgets, presentations, reports, spreadsheets, or other... Read More

25 Things Mapping Software Can Do For You

1. With mapping software you can create a report that tracks your retirement plan for... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the Mozilla package (Netscape is also... Read More

Best Spyware Removers

Finding the best spyware removers to detect and remove spyware and adware from your computer... Read More

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating web browser from Microsoft. IE... Read More

Will Adobe Manage to Replace Industry Work Horse Quark Express by Giving Adobe InDesign for Free?

And kill the best layout software in the process of gaining market share?*** Heard about... Read More

Implementing ERP for Large Publicly Traded Corporation ? Microsoft Great Plains

We would like first emphasize the change in the paradigm. If you look back ?... Read More

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete novice attempting to provide data... Read More

Increase Office Efficiency With One Simple Tool

When you need a phone number, you do a quick search on the Internet and... Read More

Microsoft Great Plains BR: Bank Reconciliation

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

Choose your Java Wisely

Java has come along a long way. Many would agree with this. I did not... Read More

Great Plains Dexterity Customization Options ? Overview For Developers

Looks like Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles... Read More

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

Microsoft Business Solutions Great Plains is written in Great Plains Software programming tool: Great Plains... 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

Off The Record - Tips For Picking Recording Software

Need software to record your voice, streaming audio or musical instruments? There are dozens of... Read More

Microsoft Great Plains Installation ? Overview for IT Director/Controller

Microsoft Great Plains is main mid-market application from Microsoft Business Solutions. This is short article,... Read More

Microsoft Great Plains customization ? Freight Forwarding/Transportation industry example

Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great Plains eEnterprise/Dynamics 7.0, 6.0, Dynamics... Read More

CRM 101: Customer Relationship Management for Beginners

Customer Relationship Management, abbreviated "CRM," is the term for a business strategy that is designed... Read More

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

If you would like to pick something from Microsoft, or its ERP subdivision ? Microsoft... Read More

Google Brings the Earth to Your Desktop

Google Inc. has launched a new software package that allows you to see satellite images... Read More

Introducing GRML

Creating a new markup language.Introduction.General Reuse Markup Langauge, or GRML, is a markup language for... Read More

Ukraine IT Myths Dispersed

While Ukraine is becoming a new popular IT outsourcing destination, there are... Read More

How To Avoid Getting Hooked By Pfishing

"Pfishing", sometimes spelled "Phishing", is a word that's used to describe a method of identity... Read More

Troubleshoot Windows with Task Manager

Task Manager is a Windows system utility that displays thetasks or processes currently running on... Read More

Explore the Internet in a Whole New Way

For a long time now Microsoft's Internet Explorer has ruled as 'King of Internet browsers'.... Read More

Microsoft CRM Data Conversion FAQ

Microsoft Business Solutions CRM data conversion deserves FAQ type of article, where IT people could... Read More

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

In 2004 Oracle, Inc. made its new step toward J2EE application development simplification, releasing new... Read More

Microsoft CRM for Corporate Business ? Working Offline

If your company has regional and worldwide operations, you might already realized that it is... Read More

Microsoft Great Plains: Data Conversion & Migration Scenarios ? Overview for Consultant

Microsoft Business Solutions Great Plains serves multiple industries in the US, plus it has strong... Read More

Chinese Input - Step by Step Instruction on How to Input Chinese Characters in English Windows XP

Enabling Chinese input is quick and easy, there are only a few steps you need... Read More

Dreamweaver vs FrontPage

There are two major WYSIWYG(What You See Is What You Get) editors available for beginners.... Read More

C++ Tutorial 2, Input and Variables

This is the tutorial where we really get into programming. Input and variables are the... Read More

netstat - Linux Command to Display Networking Information

In linux, one of great commands for finding out information about your network connections is... Read More

Microsoft CRM Integration With Microsoft Office Documents ? Overview For System Integrator

Microsoft CRM is CRM application, maintained and supported by Microsoft Business Solutions. Microsoft CRM utilizes... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for mid-size company and probably good... Read More

ERP Software Financing: the Future? ? Overview for Company Owner

In our case ? we serve Microsoft Business Solutions ERP and CRM products: Microsoft Great... Read More

Software Upgrades Arent Always the Best Move

When my daughter was getting into AOL instant messaging (AIM) and using all the cool... Read More

When is a Software Engineer Not a Software Engineer?

The title of "software engineer" has got to be among the most highly abused in... Read More

Microsoft Retail Management System (RMS) SQL Customization ? Overview for Programmer

Microsoft Retail Management System serves retail single store as well as retail chains clientele. The... Read More

Free Preventive Maintenance Software

While several preventive maintenance software manufacturers offer free trials for their products before charging a... Read More

Keeping Track

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

Best Practices In Choosing Network Monitoring Software

Blue Cross and Blue Shield of Hawaii (HMSA) found itself with a rapidly expanding network... Read More

What is Software Piracy?

There are several kinds of software piracy. The bottom line is when software is pirated,... Read More