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
|
|
|
Enterprise Resource Planning Overview
ERP (Enterprise Resource Planning) Overview covers What is ERP, Brief history of ERP, Why is it necessary, Market Leaders and the future of ERP.What is ERP?Enterprise Resource Planning or ERP is an industry term for integrated, multi-module application software packages that are designed to serve and support multiple business functions. An ERP system can include software for manu...(related: Software)
Ukraine: A Prospective Player On The World Software Outsourcing Market
The destruction of the Soviet Union about 15 years ago, made a huge country with a great level of IT science divided into small bricks. Nowadays, despite Russia's expansion into the world software development market, Ukraine keeps playing an important role here too. There are several prerequisites for this.PrerequisitesTraditionally, Ukraine always was a technical region of the USSR. No wonder, the IT education was and still is one of the most popular and respective education in the region. Such well-known Ukrainian universities as Kyiv National Technical University, Donetsk National Technical University etc. as well as new establishments with more innovative approach for IT specialists education, such as Donetsk State Institute of Artificial Intelligence and others, educate a new prospective generation of youn...(related: Software)
When Is A Software Engineer Not A Software Engineer?
The title of "software engineer" has got to be among the most highly abused in the corporate high-tech world. It's also one of the most popular.And why not? It sounds a lot better than "computer programmer," and it looks much better on one's business card. Unfortunately, it's often inaccurate. Engineering is, after all, the application of sound technical principles to develop systems that are ...(related: Software)
Tools For Customizing Great Plains
Microsoft Business Solutions ? Great Plains has captured the US market with its integrated business applications for small and mid-size organizations. Its comprehensive accounting and business management capabilities provide businesses with tools to customize various modules of the Great Plains software. These tools have been proven to have contributed to business growth and tighter control over its processes.There are several ways in which customization and integration can be applied to Great Plains applications because of its open architecture characteristic. Integrity of data and performance of processes can also be validated using one of the many tools as listed ...(related: Software)
Running A Program On A Remote Server Using Ssh
How do you run a program on a remote server using ssh?For this example we'll have two servers, one named Johnny and another named Cash. Both are running openssh. Our goal is to have a program on Johnny login to Cash and run a program on Cash. To make the task a little more complex we'll be using different users on each machine.The first thing we'll need to do is generate public and private keys on Johnny. So, logged into Johnny as user 'boy' we create public and private keys by creating them in the .ssh directory as follows:Johnny$> pwd/home/boy/.sshJohnny$> ssh-keygen -t rsa -f sueGenerating public/private rsa key pair.Enter passphrase (empty for no passphrase):...(related: Software)
Can Group Collaboration Software Meet Business Needs?
According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric Collaboration," 51 percent of businesspeople utilizing technology such as Mic...(related: Software)
Resume Software ? Advantages Revealed
The various resume software offered, particularly on the internet, can seem very attractive to job seekers; especially to those not comfortable writing resumes. At first glance resume writing software can provide a number of perks and can appear to be the perfect solution to many job seekers.There are some disadvantages to using software, however; and before utilizing it to create a resume, a wise job seeker will take a few moments to compare both the advantages and the disadvantages of resume software.Software for writing resumes is also commonly known as resume writers, resume creator, resume maker and resume builder.Advan...(related: Software)
Microsoft Great Plains Version 8.5: Upgrade, Customization, Vba, Crystal Reports - Highlights
Microsoft Great Plains is one of the Microsoft Business Solutions family ERP products: Great Plains, Navision, Axapta, Solomon, Small Business Manager. MBS also has Microsoft CRM - Client Relation Management software and Microsoft Retail Management System (Microsoft RMS)Microsoft Great Plains 8.5 is scheduled to be released in the second quarter of 2005. It is usual practice when you here announcements and then you are notified about software release delays. Expect first the version to be released for US market and then you can expec...(related: Software)
Business Plan Software Review: Ultimate Business Planner?
One of the main reasons business owners and entrepreneurs use business plan software is to simplify building their financial models. Even with an explosion in business plan software ...(related: Software)
The Xp Firewall Isnt Enough
You might think you don't need a firewall because windows XP has onebuilt in,but read on for a plain-english explanation why this isn't enough.Before broadband and cable connections were common most people didn't need apersonal firewall because they weren't connected to the Internet for extendedperiods of time.Every computer that connects to the internet has an IP address. This is likea sort of phone number, and is issued either to an individual computer or toa "gateway" that connects a private network to the 'net.Domestic dial-up internet connections normally use a system called DynamicIP Addressing where people are issued with a sort of "new identity" everytime they connect, so it's difficu...(related: Software)
Spyware: What It Is And How To Combat It
Spyware is software or hardware installed on a computer without the user's knowledge which gathers information about that user for later retrieval by whomever controls the spyware.Spyware can b...(related: Software)
Microsoft Great Plains Furniture & Fixtures ? Implementation & Customization Highlights
Microsoft Great Plains, former Great Plains Software Dynamics / eEnterprise was introduced in 1993 as first Microsoft Windows and Macintosh based graphical accounting/ERP application for Mid-Size businesses. Considering the history of furniture retailer and custom assembly lines ? they show...(related: Software)
Beware Of Spyware
One day, you suddenly realize that your computer started to worknoticeably slower than it used to. You decide to run de-fragmentation of your hard drive and add more virtual memory to the system. No luck! May be, it's probably some viruses, you reckon turning on your virus scanning software. Even after running the anti-virus program you notice that the problem won't resolve. Not understanding what's happening frustrated you pick up the phone. It's time to call your tech-support. Sounds familiar? You are not alone! Each day, thousands of users are facing similar problems caused by malicious software called Spyware. According to Dell technical support...(related: Software)
site-map - Copyright © 2008 | Contact Webmaster | All Rights Reserved. | Software