Software informational articles

Microsoft great plains: appeal answer case in point ? stored method for precious stone article - software

 

This is intermediate level SQL scripting condition for DB Administrator, Programmer, IT Specialist

Our and Microsoft Commerce Solutions goal here is to educate list administrator, programmer, software developer to permit them aid Microsoft Great Plains for their companies. In our estimation self assistance 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 Big business Solutions Techknowledge database. This will allow you to avoid dear consultant visits onsite. You only need the help from authority when you plan on center customization, border or integration, then you can application to a celebrity who specializes in these tasks and can do economical nation-wide cool assist for you.

Let's look at appeal computation techniques.

Imagine that you are financing association and have many customers in two companies, where you need to predict interest. The next method will do the job:

CREATE Modus operandi AST_Interest_Calculation

@Company1 varchar(10), --Great Plains SQL folder ID

@Company2 varchar(10),

@Accountfrom varchar(60),

@Accountto varchar(60),

@Datefrom datetime,

@Dateto datetime--,

as

declare @char39 char --for definite 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


MORE RESOURCES:
Developed by:
home | site map
goldenarticles.net © 2018