Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/a26f9f83/public_html/articles/includes/config.php on line 159
Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report > NetSparsh - Viral Content you Love & Share

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

In The News:

This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

CNBC

VW's CEO was told about emissions software months before ...
CNBC
Volkswagen Chief Executive Herbert Diess was told about the existence of cheating software in cars two months before regulators blew the whistle on a ...
VW's CEO was told about emissions software months before scandal: Der SpiegelReuters

all 41 news articles »

Forbes

Could Software Analytics Platforms Extinguish The Threat Posed By Hackers?
Forbes
RYAZAN, RUSSIA - JUNE 28, 2017: The silhouette of a young man with a mobile phone against a red background with a projected message related to the Petya ransomware; on 27 June 2017 a variant of the Petya ransomware virus hit computers of ...


GeekWire

Five experts explain how software development and operations teams are adjusting to the rapid changes caused by ...
GeekWire
While software continues to eat the world, the philosophies and tactics used to build that software are constantly changing. Getting from idea to working software has never been easy, and companies that never needed cutting-edge expertise in software ...


Inverse

Tesla V9.0 Software Isn't Out Yet but Elon Musk Has Plans for Version 10
Inverse
With the upcoming release of its V9.0 software, Tesla CEO Elon Musk put out the call for game developers to produce games for the car's touch screens in early August. But Musk may already have plans for other forms of in-car streaming entertainment for ...

and more »

South Bend Tribune

Were red flags with 911 dispatch software overlooked by St. Joseph County?
South Bend Tribune
In 2014, St. Joseph County and the cities of South Bend and Mishawaka shopped for dispatch software needed at the consolidated center that was then being set up. A national search drew interest from a handful of companies. After some debate, the ...


MIT Technology Review

Why adding bugs to software can make it safer
MIT Technology Review
This can be serious, but there is a large class of software, such as background microservices, that is designed to handle crashes gracefully by restarting the software while the user is none the wiser. These bugs are much less serious than ones that ...


MSPoweruser

Microsoft shares ways for software vendors to minimize false positives
MSPoweruser
The company shed light on the false positive issue which basically means labelling a genuine software/program as a virus. Usually, this poses a security issue as people don't know if they can trust a software after it has been flagged by the Anti-virus ...


Hamilton Journal News

Software may change how 911 calls are dispatched in Butler County
Hamilton Journal News
Smart 911 is new software being used by Butler County law enforcement agencies.

and more »

CoinDesk

Zcash Sets Stage for 'Sapling' Upgrade With New Software Release
CoinDesk
Privacy coin zcash is gearing up for a major upgrade with the first release of network software compatible with its upcoming Sapling hard fork. Zcash version 2.0.0 was released Thursday, according to a blog post penned by Sean Bowe, an engineer with ...


The Economist

New software helps uncover Mafia crime masked as ordinary business
The Economist
That has made it harder to spot extortion by analysing a business's books, says Antonio Basilicata of the Anti-Mafia Investigation Directorate (DIA) in Rome. But software developed by Crime&tech, a firm in Milan, can still help identify companies with ...

Google News

Anti-Spyware Protection ? Holes in the Shining Armor

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

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

Microsoft Great Plains Beverage Production & Distribution ? Implementation & Customization Highlight

Microsoft Business Solutions Great Plains has many years of successful... Read More

Resume Software ? Advantages Revealed

The various resume software offered, particularly on the internet, can... Read More

5 Mac Security Tips You Can?t Live Without

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

XML Parser and Their Types

XML parser is a software module to read documents and... Read More

eStore Advantage ? Extending Microsoft eConnect for MBS Great Plains

eStore Advantage allows front-office applications to communicate with back-office business... 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

Microsoft Navision Customization and Reporting ? Tips For Programmer/IT Specialist

C/SIDE (Client/Server Integrated Development Environment) - The core of... 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

Manufacturing Solutions for Microsoft Great Plains ? Overview for Consultant

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

Mapping Software: Putting Data Visualization on the Map

Mapping Software Improves Data VisualizationFrom the outset, it is important... Read More

Groupware as a Document Manager: Collaboration Series #3

This article is the third of a series of articles... Read More

Fast Car Knoppix

Small can be beautiful! Working with Knoppix for the past... Read More

Falling in Love With More Than One Screensaver: The Fun Part

Having from five to ten and more favorite screensavers is... Read More

Your Computer May Be Infected, Heres How To Check (NOT about virus)

NOTE: Please take time to read on - it may... Read More

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More

IBM Lotus Domino or Microsoft Exchange?

IBM Lotus Domino or Microsoft Exchange?The severe competition continues for... Read More

Great Plains Bill of Materials Setup & Customization for Process Manufacturing ? Overview

Microsoft Great Plains has full-featured Manufacturing suite of modules: Capacity... Read More

PHP On-The-Fly!

IntroductionPHP can be used for a lot of different things,... Read More

Microsoft CRM: Data Conversion ? Import from Act!

Best Software Act! is very popular CRM for small and... Read More

Putting Screensavers Under Control

No matter how much you enjoy your favorite screensavers, sometimes... Read More

Examining the Substance of Studio MX

To all web designers out there, this article is for... Read More

Corporate ERP Selection: Microsoft Great Plains

In our opinion, traditional approach when you select ERP/MRP system... Read More

Information Products: A Business Owners Best Friend

We live in a post-industrial age where information is the... Read More