Custom Date and Time functions in SQL

Many of you are probably aware that you can create a function in SQL that returns a value and then use it like a column in a SQL statement.

For instance,
select WS_DateOnly(getdate())
will return a single column containing the date portion of the current date (truncating off the time).

In GP, these type of time truncations are critical because the columns contain either a date or a time, but not both.

I understand the need for using functions to keep formatting and such the same.  They can also simplify SQL statements by removing much of the calculations from the statement.

However, in the case of date and time truncation, format and calculation does not come into play at all.  The following statements are equivalent.

select WS_DateOnly(getdate())
select convert(varchar,getdate(),102)

and

select WS_TimeOnly(getdate())
select convert(varchar,getdate(),114)

The formats for the dates do not really matter other than 102 is a time-less date and 114 is a date-less time.

Here are some reasons that burying this type of calculation in a function are a bad idea:
1. SQL creates a dependency for each place the function is used.  This adds to overhead and erodes performance.
2. A developer reading the statement that uses the function does not know exactly what the function is doing unless they go open it, slowing down troubleshooting.
3. SQL traces see the function call as a separate event, cluttering up traces.


The most painful issue of all is when the function gets added to a check constraint on a table.  This creates a dependency that will crash the upgrade utilities for GP and requires manual correction.

So the takeaways are:
1. Never use functions to mask simple SQL functions like date truncation
2. Use functions sparingly
3. Never use a function in a table constraint on a GP database.

script to read company setup options

/* Break Down Company Options Multi-Select List Box into individual options. */
/* Script by David Musgrave, Created 01-Aug-2012, Last Modified: 01-Aug-2012 */
select INTERID, CMPANYID, CMPNYNAM, Company_Options
, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END — Bit 24
+ CASE WHEN Company_Options & 65536 * 512 > 0 THEN 2 ELSE 0 END — Bit 25
+ CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 4 ELSE 0 END — Bit 26
+ CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 8 ELSE 0 END — Bit 27
+ CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 16 ELSE 0 END — Bit 28
+ CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 32 ELSE 0 END — Bit 29
+ CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 64 ELSE 0 END — Bit 30
+ CASE WHEN Company_Options < 0 /* Negative */ THEN 128 ELSE 0 END — Bit 31

+ CASE WHEN Company_Options & 65536 * 1 > 0 THEN 256 ELSE 0 END — Bit 16
+ CASE WHEN Company_Options & 65536 * 2 > 0 THEN 512 ELSE 0 END — Bit 17
+ CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1024 ELSE 0 END — Bit 18
+ CASE WHEN Company_Options & 65536 * 8 > 0 THEN 2048 ELSE 0 END — Bit 19
+ CASE WHEN Company_Options & 65536 * 16 > 0 THEN 4096 ELSE 0 END — Bit 20
+ CASE WHEN Company_Options & 65536 * 32 > 0 THEN 8192 ELSE 0 END — Bit 21
+ CASE WHEN Company_Options & 65536 * 64 > 0 THEN 16384 ELSE 0 END — Bit 22
+ CASE WHEN Company_Options & 65536 * 128 > 0 THEN 32768 ELSE 0 END — Bit 23

+ CASE WHEN Company_Options % 65536 & 256 > 0 THEN 65536 * 1 ELSE 0 END — Bit 8
+ CASE WHEN Company_Options % 65536 & 512 > 0 THEN 65536 * 2 ELSE 0 END — Bit 9
+ CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 65536 * 4 ELSE 0 END — Bit 10
+ CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 65536 * 8 ELSE 0 END — Bit 11
+ CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 65536 * 16 ELSE 0 END — Bit 12
+ CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 65536 * 32 ELSE 0 END — Bit 13
+ CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 65536 * 64 ELSE 0 END — Bit 14
+ CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 65536 * 128 ELSE 0 END — Bit 15

+ CASE WHEN Company_Options % 65536 & 1 > 0 THEN 65536 * 256 ELSE 0 END — Bit 0
+ CASE WHEN Company_Options % 65536 & 2 > 0 THEN 65536 * 512 ELSE 0 END — Bit 1
+ CASE WHEN Company_Options % 65536 & 4 > 0 THEN 65536 * 1024 ELSE 0 END — Bit 2
+ CASE WHEN Company_Options % 65536 & 8 > 0 THEN 65536 * 2048 ELSE 0 END — Bit 3
+ CASE WHEN Company_Options % 65536 & 16 > 0 THEN 65536 * 4096 ELSE 0 END — Bit 4
+ CASE WHEN Company_Options % 65536 & 32 > 0 THEN 65536 * 8192 ELSE 0 END — Bit 5
+ CASE WHEN Company_Options % 65536 & 64 > 0 THEN 65536 * 16384 ELSE 0 END — Bit 6
+ CASE WHEN Company_Options % 65536 & 128 > 0 THEN -2147483648 ELSE 0 END AS MLSB_Value — Bit 7

, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END AS Opt01 — Bit 24
, CASE WHEN Company_Options & 65536 * 512 > 0 THEN 1 ELSE 0 END AS Opt02 — Bit 25
, CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 1 ELSE 0 END AS Opt03 — Bit 26
, CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 1 ELSE 0 END AS Opt04 — Bit 27
, CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 1 ELSE 0 END AS Opt05 — Bit 28
, CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 1 ELSE 0 END AS Opt06 — Bit 29
, CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 1 ELSE 0 END AS Opt07 — Bit 30
, CASE WHEN Company_Options < 0 /* Negative */ THEN 1 ELSE 0 END AS Opt08 — Bit 31

, CASE WHEN Company_Options & 65536 * 1 > 0 THEN 1 ELSE 0 END AS Opt09 — Bit 16
, CASE WHEN Company_Options & 65536 * 2 > 0 THEN 1 ELSE 0 END AS Opt10 — Bit 17
, CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1 ELSE 0 END AS Opt11 — Bit 18
, CASE WHEN Company_Options & 65536 * 8 > 0 THEN 1 ELSE 0 END AS Opt12 — Bit 19
, CASE WHEN Company_Options & 65536 * 16 > 0 THEN 1 ELSE 0 END AS Opt13 — Bit 20
, CASE WHEN Company_Options & 65536 * 32 > 0 THEN 1 ELSE 0 END AS Opt14 — Bit 21
, CASE WHEN Company_Options & 65536 * 64 > 0 THEN 1 ELSE 0 END AS Opt15 — Bit 22
, CASE WHEN Company_Options & 65536 * 128 > 0 THEN 1 ELSE 0 END AS Opt16 — Bit 23

, CASE WHEN Company_Options % 65536 & 256 > 0 THEN 1 ELSE 0 END AS Opt17 — Bit 8
, CASE WHEN Company_Options % 65536 & 512 > 0 THEN 1 ELSE 0 END AS Opt18 — Bit 9
, CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 1 ELSE 0 END AS Opt19 — Bit 10
, CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 1 ELSE 0 END AS Opt20 — Bit 11
, CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 1 ELSE 0 END AS Opt21 — Bit 12
, CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 1 ELSE 0 END AS Opt22 — Bit 13
, CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 1 ELSE 0 END AS Opt23 — Bit 14
, CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 1 ELSE 0 END AS Opt24 — Bit 15

, CASE WHEN Company_Options % 65536 & 1 > 0 THEN 1 ELSE 0 END AS Opt25 — Bit 0
, CASE WHEN Company_Options % 65536 & 2 > 0 THEN 1 ELSE 0 END AS Opt26 — Bit 1
, CASE WHEN Company_Options % 65536 & 4 > 0 THEN 1 ELSE 0 END AS Opt27 — Bit 2
, CASE WHEN Company_Options % 65536 & 8 > 0 THEN 1 ELSE 0 END AS Opt28 — Bit 3
, CASE WHEN Company_Options % 65536 & 16 > 0 THEN 1 ELSE 0 END AS Opt29 — Bit 4
, CASE WHEN Company_Options % 65536 & 32 > 0 THEN 1 ELSE 0 END AS Opt30 — Bit 5
, CASE WHEN Company_Options % 65536 & 64 > 0 THEN 1 ELSE 0 END AS Opt31 — Bit 6
, CASE WHEN Company_Options % 65536 & 128 > 0 THEN 1 ELSE 0 END AS Opt32 — Bit 7

from DYNAMICS..SY01500

/*
1 Use Shipping Method when Selecting Default Tax Schedule
2 Calculate Terms Discount Before Taxes
3 Enable Intrastat Tracking
4 Separate Payment Distributions
5 Merge Trade Discount and Markdown Distributions in Sales
6 Merge Trade Discount Distributions in Purchasing
7 Calculate Tax Rebates
8 Enable Posting Numbers in General Ledger
9 Allow Negative Debits and Credits in General Ledger
10 Enable GST for Australia
11 Enable Tax Date
12 Enable Shipping Document in Sales Order Processing
13 Enable Reverse Charge Taxes
14 Calculate Taxes in General Ledger
15 Allow Summary-Level Tax Edits
16 Require Tax Detail Totals to Match the Pre-Tax Amount
17 Specify Tax Details for Automatic Tax Calculation
18 Enable VAT Return
19 Enable EU Transaction Tracking
20 Enable DDR and European Electronic Funds Transfer
21 Enable Payables EFT
22 Enable Canadian Tax Detail
23 Exclude Tax in Inventory Cost when Tax is Included In Price
*/

/* Copyright © Microsoft Corporation. All Rights Reserved. */
/* This code released under the terms of the */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

Special Order Parts Business Alert Example

We are having a great time here at Synergy 2016!

Here is the example from the Workflow session we did yesterday.

This is a trigger based business alert that is designed to email the Technician and a dispatch mail group whenever a part is received that is associated with a Service Call.

Here is the stored procedure that constructs and sends the email:

CREATE PROCEDURE dbo.cssp_SpecialOrderPartsReceipt (@ITEMNMBR VARCHAR(31),@ITEMDESC VARCHAR(101), @Service_Call_ID VARCHAR(17))
AS
/*

This is an example for educational purposes. You should test all code for its intended use before placing it in production. No warranty is implied. It you find any issues with the code, please post in comments and I will endeavor to correct it.
20161025 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON
DECLARE @Technician VARCHAR(11),@CUSTNMBR VARCHAR(15),@ADRSCODE VARCHAR(15),@TECHEMAIL VARCHAR(41),@emails VARCHAR(max),@THISbody VARCHAR(max)

SELECT @Technician = Technician,@CUSTNMBR = CUSTNMBR,@ADRSCODE = ADRSCODE
FROM dbo.SV00300
WHERE Service_Call_ID = @Service_Call_ID

SELECT @TECHEMAIL = rtrim(SV_Pager_PIN)
FROM dbo.SV00115
WHERE Technician = @Technician

SELECT @emails = @TECHEMAIL + ';dispatch@key2act.com'
,@THISbody = 'Item: ' + rtrim(@ITEMNMBR) + ' ' + rtrim(@ITEMDESC) +
' Customer: ' + rtrim(@CUSTNMBR) + ' Address: ' + rtrim(@ADRSCODE)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Business Alert'
,@recipients = @emails
,@body = @THISbody
,@subject = 'Special Order Part Received';
END

Here is the trigger that calls the proc.

CREATE TRIGGER dbo.cstr_POP10310_INSERT ON dbo.POP10310 AFTER INSERT
AS
/*
This is an example for educational purposes. You should test all code for its intended use before placing it in production. No warranty is implied. It you find any issues with the code, please post in comments and I will endeavor to correct it.
20161025 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON;
DECLARE @JOBNUMBR VARCHAR(17),@ITEMNMBR VARCHAR(15), @ITEMDESC VARCHAR(101), @QTY NUMERIC(19,5)

DECLARE C_INSERTED CURSOR FOR
SELECT JOBNUMBR,ITEMNMBR,ITEMDESC
FROM inserted
WHERE RTRIM(JOBNUMBR)!=''
OPEN C_INSERTED
FETCH NEXT FROM C_INSERTED INTO @JOBNUMBR,@ITEMNMBR,@ITEMDESC
WHILE @@FETCH_STATUS=0
BEGIN
EXEC dbo.cssp_SpecialOrderPartsReceipt @ITEMNMBR,@ITEMDESC,@JOBNUMBR
FETCH NEXT FROM C_INSERTED INTO @JOBNUMBR,@ITEMNMBR,@ITEMDESC
END
CLOSE C_INSERTED
DEALLOCATE C_INSERTED
END

You will have to configure DB mail on your SQL Server in order for the send mail part to work.  However, you could replace that call with some other action, like writing a log record or calling an eConnect proc.

Enjoy!

script to auto roll calls forward in signature

DECLARE @ROLLDATE DATETIME,
@STATUS INT
SELECT @ROLLDATE=CONVERT(VARCHAR,GETDATE(),102)

EXEC SMS_Auto_Call_Roll_Forward @ROLLDATE,'sa',@STATUS OUT
go

Steps to change the name of a VM running SQL and SSRS

1. Open File Explorer

2. Right click This PC and choose Properties, then Advanced system Settings

3. On the Computer Name tab click change

4. Change the name and restart now

5. Launch SSMS and login (You may need to use the new server name to log in) and run the following:

sp_dropserver 'old_physical_server_name\GP2015'
GO

sp_addserver 'new_name\GP2015', local
GO

6. Restart SQL Server

7. Close SSMS

8. Open SSRS configuration

9. Choose Database then change database and follow the wizard to locate the ReportServer database.

10  Restart SSRS

Get VS Tools for Dynamics to work with 2015

1. Install VS Tools for Dynamics 2013.

2. Copy the folder C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ProjectTemplates\CSharp\Dynamics GP
and paste to
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\ProjectTemplates\CSharp\

3. Open a command prompt as administrator

4. Run the following commands:
cd C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE
devenv /installvstemplates

Set up Auto Logon in windows 2012 R2

1. Run regedit.exe
2. Navigate to

   HKLM\Software\Microsoft\Windows NT\CurrentVersion\winlogon
   

3. Set or create the following keys

   (DWORD)  AutoAdminLogon = 1
   (String) DefaultUserName = Your user name
   (String) DefaultPassword = Your password