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

Mashable

7 of the best antivirus software options
Mashable
Finally, you need software that provides an on-demand malware scan, as well as a vulnerability scan, so that you're always in control of what's going on with your data. Security is a fast-moving field so you want software that keeps on top of new ...

and more »

Forbes

How An Event Planning Software Platform Went Global Overnight
Forbes
So, along with designer Mickey Switzer Schubert-Suell and software engineers Niels Schmidt Hansen and Deividas Jackus, Hadzic decided it was time to create a better solution, something that could free up the event organisers' time, be easy to use ...


Financial Times

Paul Allen, software pioneer, 1953-2018
Financial Times
If history gives you only one shot at making a real difference, then Paul Allen seized his with both hands. The Microsoft co-founder, who has died at the age of 65, did not exhibit the charisma and drive that made Bill Gates the main force behind the ...
Paul Allen, who co-founded software company Microsoft, has died age 65The West Australian

all 1,809 news articles »

Forbes

Perforce To Acquire Perfecto Mobile, Because Software Change Is A Constant
Forbes
Software changes all the time. Even the most non-technical user will probably know this due to the ubiquity of the smartphone and the fact that we are constantly being told to 'install updates' on our devices and get the latest version of our favorite ...


WIRED

Review: Google Pixel 3 and Pixel 3 XL
WIRED
But current iPhone users still might not feel Google's extensive software smarts can outweigh the benefits and conveniences that come with iOS. Maybe all of their friends and family are using iMessage on iPhones, and they're wary of losing cross-device ...
Pixel 3 XL review—Google software deserves better than this hardwareArs Technica
Alphabet's Google Pixel Event Isn't About Phones, It's About the SoftwareYahoo News
Pixel 3 and 3 XL review: Google's hardware takes a backseat to softwareEngadget
XDA Developers (blog) -New Zealand Herald -YouTube -Google Product Forums
all 378 news articles »

Government Technology

Gunshot Detection Company ShotSpotter Acquires Predictive Policing Software
Government Technology
ShotSpotter, a gunfire detection system for police departments, signaled a move toward proactive enforcement this week by acquiring HunchLab and its crime-prediction software, according to a news release. Now called ShotSpotter Missions, HunchLab's ...


TechCrunch

Celonis brings intelligent process automation software to cloud
TechCrunch
Celonis puts software and machine learning to bear on the problem. Co-founder and CEO Alexander Rinke says that his company deals with massive volumes of data and moving all of that to the cloud makes sense. “With Intelligent Business Cloud, we will ...

and more »

WSBT-TV

Software issue with Indiana voter registration system appears to be fixed
WSBT-TV
This was sparked by a software issue. For three days, it slowed the entire voter registration system down. Clerks couldn't check to see if people who wanted to vote with an absentee ballot were even registered to vote. Now that it's fixed, clerks are ...


Phys.Org

Reusable software for high performance computing
Phys.Org
Chandrasekaran is an expert on parallel programming—writing software code that can run simultaneously on many multi-core processors. Parallel programming is an increasingly important discipline within computer science as more and more universities ...


Information Age

New government software Code of Practice: security must be built from the code up
Information Age
Providing concrete guidance to manufacturers while also raising public awareness of these issues can only help address the gap that currently exists. It's not just about the hardware anymore, it's about the software behind it, and it's really ...
Code of Practice for Consumer IoT Security - Global Data ReviewGlobal Data Review

all 332 news articles »
Google News

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

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

Data Quality Best Practices for Salesforce.com

Executive SummaryAn effective plan for entering, cleaning and updating the... Read More

Is Your Family Protected?

When you think... Read More

Software Automation Helps Increase your Bottom Line

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

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete... Read More

An Introduction to .NET Framework

.Net Framework is a platform or development environment to seamlessly... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Software Process Improvement -A Successful Journey

Background: For many organizations like ours, the interim target of... Read More

Beware of Spyware

One day, you suddenly realize that your computer started to... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market... Read More

What is Preventive Maintenance Software?

Preventive Maintenance (PM) is defined as scheduled work done on... Read More

Brief Notes About Mainframe Software Pricing

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

Easy Guide to RAID Recovery

What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More

Great Plains Accounting Migration to Microsoft Great Plains - Overview for IT Specialist

This is a short article, written in question/answer/FAQ style to... Read More

Design a Web Album Using Adobe Photoshop- Part 2

So let's begin crunching down these 300 images using Adobe... Read More

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

Microsoft Business Solutions CRM is present several years on the... Read More

String in Java

Handling character strings in Java is supported through two final... 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

How to Choose the Right Accounting Software for Your Business

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

Microsoft Great Plains POP: Purchase Order Processing ? Overview For Consultants

Great Plains Purchase Order Processing (POP) module makes up one-third... Read More

Who Is Minding Your Sensitive Data?

Stealing company information used to be the specialty of spies... Read More

Healthcare Preventive Maintenance Software

Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More

Cross-Platform Custom Software Development & Integration ? IT Strategy for Large Corporation

Microsoft Business Solutions products: Great Plains, MS CRM, Navision, Axapta,... Read More

Crystal Reports For Microsoft RMS ? Overview For Developer/Report Designer

If you are software developer or database administrator - we... Read More

Microsoft Great Plains & CRM in Transportation & Logistics ? Overview

Microsoft Business Solutions Great Plains and MS CRM (client relation... Read More