Interesting Findings & Knowledge Sharing

Interesting Findings and Knowledge Sharing for Microsoft Dynamics

Quick Print Functionality for Sales Transactions

Folks…

In this article, I am going to provide some information about a quick printing functionality for Sales Transactions, which will provide an ease in data entry.

Once we pick up a Sales Invoice in the Sales Transaction Entry window, go to Options >> Quick Print Setup. The various print options can be setup on this window for the various documents in this window. This is a one time setup for the user.

Once we setup the print destination to Printer, we can also specify the number of copies to be printed. This setup is so useful since it helps the printing of the documents without any additional clicks, and also not having to choose the print options every time. :-)

2010-02-07_115034

Once these have been setup, the user can click on Options >> Quick Print, which will automatically make the options that we have selected on the Quick Print Setup and print the documents that we have selected on the Quick Print Setup window.

2010-02-07_115104

This is such a useful functionality especially, since it saves many mouse clicks for the user, since all the print options is automatically setup in the Quick Setup window, and the user does not need to pick these options at the time of printing the document every time.

Hope you find this information useful.

Until next post…

7 February 2010 at 12:07 - Comments
David Musgrave
Hi Siva I am glad you like the Quick print feature. I developed this feature as part of the original ...
Yesterday at Yesterday

Sneak Peek Into Dynamics GP 2010 (v11) – Part III

Folks…

In this article, I will give some insight into some of the key enhancements in the Payables and Receivables Series module.

Enhancements to Select Checks Process

The select checks process has been enhanced so that the user can build check run batches by passing multiple ranges instead of just a couple of ranges for Vendor and Document Number.

And we can provide ranges for any of the following ranges available in the window.

  • Vendor ID
  • Vendor Name
  • Class ID
  • Payment Priority
  • Voucher Number
  • Document Number
  • Payment Number
  • Due Date/Discount Date Cutoff
  • Document Currency

2010-02-07_110251

Vendor Approval Process

The workflow module has been extended to support the approval of vendors. When a vendor is ready to be approved, approvers can be notified and the vendor can be approved, using Microsoft Outlook®, Microsoft Dynamics GP, or Microsoft Internet Explorer®.

Enter Negative Cash Receipts

By this functionality, we can now enter a negative cash receipt in the system. This helps us to decrease the deposit amount by the amount that is entered in the negative cash receipt.

The negative cash receipt is entered the same way as a normal cash receipt, just that the amount is entered as a negative amount and the posted receipt shows up as a deposit with a negative amount.

2010-02-07_110440

Also, the other enhancement in the Cash Receipts is that we can now create a recurring cash receipt batch, to speed up data entry of cash receipts.

2010-02-07_110550

Clearing Recurring General Ledger Batch Amounts

This functionality allows you to clear the distribution amounts present in all the transactions in a recurring batch, so that we can enter different amounts for the different period that we post in the recurring batch. This option is available only when we create a batch with a frequency other than Single Use.

2010-02-07_112422

Budget Transaction Functionality

This is a new transaction which has been introduced in the General Ledger series, which allows the user to post adjustment amounts against the defined budget. We can increase or decrease the budget for a selected range of accounts. Amounts are displayed on a period-by-period basis using the window shown below. This window opens from Transactions >> Financial >> Budget Transactions.

2010-02-07_112128 

I will post more articles on some key features in the next release of Dynamics GP 2010 in the forthcoming articles. Hope you have found these information useful…

Until next post…

7 February 2010 at 11:36 - Comments

Sneak Peek Into Dynamics GP 2010 (v11) – Part II

Folks…

This is the 2nd part of my series of articles being posted to give a preview of Dynamics GP 2010 (v11). I am going to provide further insight into the enhancements to the foundation series in GP v11.

Enhancements to Lookups

The major enhancement to the lookup windows in GP is to allow the user to specify a default lookup so that the lookup opens up based on the requirements he might need.

With the help of this functionality, the user can combine the power of smartlist favorites in lookups and specify a smartlist favorite as the default view for the lookup, so that the users do not have to choose it every time he opens the lookup window.

This enhancement has been provided in the following lookup windows in this release.

  • Items lookup window
  • Accounts lookup window
  • Employees lookup window
  • Customers and Prospects lookup window
  • Salespeople lookup window
  • Vendors lookup window

2010-02-07_095512

This view is available on a per-user per company basis.

Right Click Menu Availability for Text/Numeric Fields

By this functionality, the user can right click on a text (or) numeric field and can access a shortcut menu which contains the functionalities to Cut, Copy, Paste, Select All, Clear, Undo..

This way, the user can copy the contents of a field using context menus instead of having to go to the Edit Menu on the GP window. This is much helpful, since the CTRL + C functionality does not work in some of the fields depending on the usage of the CTRL+C shortcut key on that window.

2010-02-07_102206

Copy User Security

Using this option on the User Maintenance window, we can copy the security information for an existing user (like company access, roles and tasks) into a new user that we are creating.

This eliminates the time to configure security for the user especially if the client is using a complex security architecture. Thus if there is an existing user whose security information can be copied to the new user, it can be done in just a couple of clicks.

2010-02-07_102841

Integration with Microsoft Office Communicator

This functionality allows you to see the online presence of the customers, vendors, and employees from within Microsoft Dynamics GP. This feature is integrated with Microsoft Office Communicator.

Keep watching here for the next article in this series for more information on GP v11.

Until next post…

7 February 2010 at 10:45 - Comments

Sneak Peek Into Dynamics GP 2010 (v11) – Part I

Hi All

Microsoft recently launched the BETA version of Microsoft Dynamics GP 2010, which is called GP v11. I have decided to post some information on the new additions and improvements on the product in the next couple of articles for the community.

Note that this is just a BETA version and not a final release. So there are changes which can be expected, when the final version is released.

Remembering the Login and Default Company

Dynamics GP 2010 has the capability of remembering the login and the default company where the user logs in most of the time. With this functionality the user can by-pass the couple of login screens and get into the application directly when the application icon is double clicked.

2010-02-07_080044 2010-02-07_080100

This functionality can be enabled in the system preferences window which can be accessed from Microsoft Dynamics GP >> Tools >> Setup >> System >> System Preferences.

2010-02-07_104846

Facility to See Reminders as a Cue

We can now display smartlist reminders as a cue on the home page of GP. This is more of a nice to have feature which is provided in Dynamics GP 2010.

2010-02-07_080127 2010-02-07_080138

Word Templates for Sales & Purchase Documents

The users can now print key documents like sales quotes, orders, invoices, purchase orders, receivables statements, and other receivables and payables document in word format, instead of having to print in the report writer format.

The key benefit from this enhancement is that the modification of the format of such forms is made much easier as GP allows you to create customized versions of these templates using Microsoft Word. This eliminates the hassles of report writer modification of these templates, and which can be done by the users themselves with minimal Report Writer experience. We can also create new templates and use them (just like the power of the letter writing wizard).

2010-02-07_082433 2010-02-07_082832

2010-02-07_083621

Emailing Documents

We now have the functionality to email the key documents mentioned above to the customers and vendors. We can define email templates and either attach these documents as HTML, DOCX, PDF, XPS on the email (or) embed the contents of these documents in the email body itself.

We can also create customized messages for different customers and vendors so that these messages can be used when emailing the specific customer (or) vendor.

2010-02-07_083338 2010-02-07_083519

Keep watching my blog for more information on some of the key features which have been released in Dynamics GP 2010. Will be adding more here…

Until next post…

7 February 2010 at 08:47 - Comments

“Alias” in Analytical Accounting

Hi all

Wish you all a wonderful new year and may the new year brings in joy, prosperity, happiness and satisfaction to all of you.

Today, lets have a sneak peek into a little functionality in Analytical Accounting, which I believe would benefit many people using Analytical Accounting for the purpose of their analysis reports.

As all of you are aware, during transaction entry, we enter the transaction dimension codes in the analytical accounting window as we enter the transactions. When the analytical accounting window opens, the focus of the cursor is placed in the Alias field. Most of the users generally transfer the focus into the Transaction Dimension code in the scroll window below and start entering the dimension codes relevant for the specific distribution line. I have even seen requirements coming from users to write a simple customization to shift the focus into the dimension code field when the analytical window opens up. This negates the whole purpose of the Alias field. Lets analyze it a little on this.

If there is just one transaction dimension defined in the system, it might not be a big issue. But assume the case where there are many transaction dimensions defined and we need to enter values for each of these dimensions for every distribution line of a transaction. It definitely becomes a cumbersome and a lengthy process. This is where the Alias comes into effect and can drastically improve the efficiency in the way the analytical accounting window is used in transactions.

The Alias Maintenance window opens from Cards >> Financial >> Analytical Accounting >> Alias.

image

Once we specify the alias name and tab off, the system displays all the dimensions defined in the system in the scrolling window below. The user can then specify the dimension code (if applicable) for the dimensions listed on the scrolling window. We can define as many alias with as many combinations of the dimension codes. We can copy existing alias into a new one (or) we can also import the alias information from an excel file which has been setup in the required format.

image

The format of the excel file should be as shown below.

image

Once all the required aliases have been defined, we can use them at the transaction level. When the Analytical Accounting window is opened for the distribution lines, the focus is at the Alias field. We can enter the appropriate alias manually (or) selected from the lookup. Once the alias has been picked up, the application defaults all the dimension codes setup in the alias for the specific distribution line.

This way, we can also speed up the data entry time for entering dimension codes and also make sure that the probability of errors is minimized since the dimension codes are pre-defined in the alias and once the alias is picked up correctly, the dimension codes are defaulted for the distribution line.

This is a really cool functionality which I have noted in Analytical Accounting and I have decided to share it with you folks. I hope this tip is helpful for many people who are actively using analytical accounting to track multi-dimensional information.

Until next post…

1 January 2010 at 15:43 - Comments

A Collaboration WAVE From Google

Guys…

With the close of the year round the corner, it stills fresh as the first day of the year when we welcomed it. Time really flies… as many say and experience. I have decided to post an interesting article which I came across recently, and wanted to share it with you folks…

As we are aware of, Google has been developing some really cool tools and widgets like Google Docs, Google Maps with Latitude, Google Voice, Google Mail (of course), Google Groups and tons of other apps for the benefits of its user community. The next on course to be launched is what is called “GOOGLE WAVE”. Lets have a sneak peek into this in this article.

Google Wave is an online tool for real-time communication and collaboration. A wave can be both a conversation and a document where people can discuss and work together using richly formatted text, photos, videos, maps, and more.

A wave is equal parts conversation and document. People can communicate and work together with richly formatted text, photos, videos, maps, and more.

A wave is shared. Any participant can reply anywhere in the message, edit the content and add participants at any point in the process. Then playback lets anyone rewind the wave to see who said what and when.

A wave is live. With live transmission as you type, participants on a wave can have faster conversations, see edits and interact with extensions in real-time.

Some key functionalities of wave are:

Organizing Events

Keep a single copy of ideas, suggested itinerary, menu and RSVPs, rather than using many different tools. Use gadgets to add weather, maps and more to the event.

Group Projects

Collaboratively work in real time to draft content, discuss and solicit feedback all in one place rather than sending email attachments and creating multiple copies that get out of sync.

Photo Sharing

Drag and drop photos from your desktop into a wave. Share with others. Use the slideshow viewer. Everyone on the wave can add their photos, too. It is easy to make a group photo album in Google Wave.

Meeting Notes

Prepare a meeting agenda together, share the burden of taking notes and record decisions so you all leave on the same page. Team members can follow the minutes in real time, or review the history using Playback.

Brainstorming

Bring lots of people into a wave to brainstorm – live concurrent editing makes the quantity of ideas grow quickly! It is easy to add rich content like videos, images, URLs or even links to other waves. Discuss and then work together to distill down to the good ideas.

Interactive Games

Add a gadget to a wave to play live interactive games with your friends (we’re hooked on Sudoku!). See everyone’s moves as they make them in a fast-paced game or take a break and come back later. 
Check out the featured extensions.

You can take a sneak peek of this wonderful product, to be launched sometime soon, in the you tube video below. 

Registration to this developer version is based on an invite. To request one to have a look at this and have a hands on the product, go to https://services.google.com/fb/forms/wavesignup/.

Expect a lot more interesting articles coming up in the following days and I hope the following year will definitely be a turn in the tide and fortunes of many who have had a tough year…

Happy holidays folks…

Until next post…

23 December 2009 at 10:29 - Comments

Handling Primary Key Errors in Analytical Accounting

Hi all…

It has been some time, since I have posted something here… Was busy at many other assignments. However, here comes an article which will be of interest and use to many, especially those who are using Analytical Accounting module of Microsoft Dynamics GP.

‘How many times have we received the errors of primary key violations in AAG10000, AAG20000, AAG30000 etc, when creating and posting transactions in Analytical Accounting? Usually,  these kind of errors come when we have restored a production database into a test database for the purpose of some testing.

These errors occur because of the fact that the AA Index Master table which is present in the DYNAMICS database is out of sync with the actual database indexes. Let me explain a little more on this.

All records in AA tables are indexed with an integer value as the key field. This key value is an incrementing integer starting from 1. The last used key value for every master and transaction table is stored in the DYNAMICs database in the table AAG00102.

This table contains the Company ID, the Table Series Value and the last used index value. Every time a record is added into0 the AA table, this master table is referred for the last used index value for that specific table series and generates the next value for saving the record. Since this table is maintained in the DYNAMICS database, every time a production company is restored in the test company, this table values goes out of sync for the test company.

I have attached a procedure below, which does update the AAG00102 table with the proper index values for all companies that are installed in GP. This procedure syncs up the table so that the primary key errors are eliminated. Whenever there is a restore of a production database into the test database, this script needs to be executed. It can be modified to execute it for a specific company values as well.

NOTE: All users must be out of Dynamics GP before this script is executed. Also take a backup of the DYNAMICS database before this script is being executed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
USE [DYNAMICS]
GO
IF EXISTS ( SELECT  *
FROM     sys.objects
WHERE   object_id =  OBJECT_ID(N'[dbo].[sp_FixAARecordCount]')
AND type IN (  N'P', N'PC' ) )
DROP PROCEDURE [dbo].[sp_FixAARecordCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_FixAARecordCount]
AS
DECLARE @INTERID CHAR(5)
DECLARE @CMPANYID INT
DECLARE @SQL CHAR(8000)

DECLARE cr_Companies CURSOR
FOR SELECT  INTERID,
CMPANYID
FROM    DYNAMICS.dbo.SY01500
ORDER BY CMPANYID
OPEN cr_Companies
FETCH NEXT  FROM cr_Companies INTO @INTERID, @CMPANYID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAcctClassID),0) FROM '
+ @INTERID
+ '..AAG00201) WHERE  aaTableID = 201 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDistrQueryID),0) FROM '
+ @INTERID
+ '..AAG00301) WHERE  aaTableID = 301 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaMLQueryID),0) FROM '
+ @INTERID
+ '..AAG00310) WHERE  aaTableID = 310 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimID),0) FROM '
+ @INTERID
+ '..AAG00400) WHERE  aaTableID = 400 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeID),0) FROM '
+ @INTERID
+ '..AAG00401) WHERE  aaTableID = 401 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeNumID),0) FROM '
+ @INTERID
+ '..AAG00402) WHERE  aaTableID = 402 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM  '
+ @INTERID
+ '..AAG00403) WHERE  aaTableID = 403 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM  '
+ @INTERID
+ '..AAG00404) WHERE  aaTableID = 404 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDateID),0) FROM '
+ @INTERID
+ '..AAG00500) WHERE  aaTableID = 500 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTreeID),0) FROM '
+ @INTERID
+ '..AAG00600) WHERE  aaTableID = 600 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaNodeID),0) FROM '
+ @INTERID
+ '..AAG00601) WHERE  aaTableID = 601 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaOption),0) FROM '
+ @INTERID
+ '..AAG00700) WHERE  aaTableID = 700 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAliasID),0) FROM '
+ @INTERID
+ '..AAG00800) WHERE  aaTableID = 800 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaBudgetTreeID),0) FROM '
+ @INTERID
+ '..AAG00900) WHERE  aaTableID = 900 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaUDFID),0) FROM '
+ @INTERID
+ '..AAG01000) WHERE  aaTableID = 1000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLWorkHdrID),0) FROM '
+ @INTERID
+ '..AAG10000) WHERE  aaTableID = 10000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaSubLedgerHdrID),0) FROM '
+ @INTERID
+ '..AAG20000) WHERE  aaTableID = 20000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLHdrID),0) FROM '
+ @INTERID
+ '..AAG30000) WHERE  aaTableID = 30000 AND CMPANYID = '
+ @CMPANYID + ''
)
FETCH NEXT FROM cr_Companies INTO  @INTERID, @CMPANYID
END
CLOSE cr_Companies
DEALLOCATE cr_Companies
GO
GRANT EXECUTE ON sp_FixAARecordCount TO  DYNGRP

Hope you guys find this article useful.

Until next post…

23 December 2009 at 08:11 - Comments

SQL View for Sales Margin Analysis

Guys

The next post that I have decided to make is on a SQL view for generating a sales margin analysis report in Dynamics GP.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
CREATE   VIEW dbo.vw_SOPSalesAnalysis
AS  SELECT  CASE dbo.SOP10200.SOPTYPE
              WHEN 1 THEN 'Quote'
              WHEN 2 THEN 'Order'
              WHEN 3 THEN 'Invoice'
              WHEN 4 THEN 'Returns'
              WHEN 5 THEN 'Back Order'
              WHEN 6 THEN 'Fulfillment Order'
            END AS TYPE,
            dbo.SOP10100.SOPNUMBE AS DOCUMENTNO,
            dbo.SOP10100.DOCID AS TYPEID,
            dbo.SOP10100.DOCDATE AS DOCUMENTDATE,
            dbo.SOP10100.BACHNUMB AS BATCHNUMBER,
            dbo.SOP10100.CUSTNMBR AS CUSTOMERID,
            dbo.SOP10100.CUSTNAME AS CUSTOMERNAME,
            dbo.SOP10100.CURNCYID AS CURRENCY,
            dbo.SOP10200.ITEMNMBR AS ITEMNO,
            dbo.SOP10200.ITEMDESC AS ITEMNAME,
            dbo.SOP10200.UOFM AS UOM,
            dbo.SOP10200.LOCNCODE AS LOCATIONID,
            dbo.IV40700.LOCNDSCR AS LOCATIONNAME,
            dbo.SOP10200.UNITCOST,
            dbo.SOP10200.UNITPRCE AS UNITPRICE,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4 THEN ( dbo.SOP10200.XTNDPRCE * -1 )
              ELSE dbo.SOP10200.XTNDPRCE
            END AS TOTALPRICE,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4 THEN ( dbo.SOP10200.EXTDCOST * -1 )
              ELSE dbo.SOP10200.EXTDCOST
            END AS TOTALCOST,
            dbo.SOP10200.SALSTERR AS TERRITORYID,
            dbo.RM00303.SLTERDSC AS TERRITORYNAME,
            dbo.SOP10200.SLPRSNID AS SALESPERSONID,
            dbo.RM00301.EMPLOYID AS EMPLOYEEID,
            ( LTRIM(RTRIM(dbo.UPR00100.FRSTNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.MIDLNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.LASTNAME)) ) AS EMPLOYEENAME,
            ( LTRIM(RTRIM(dbo.RM00301.SLPRSNFN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSMN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSLN)) ) AS SALESPERSONNAME,
            dbo.SOP10200.PRCLEVEL AS PRIELEVEL,
            dbo.RM00201.CLASDSCR AS CUSTOMERCLASSNAME,
            dbo.RM00101.CUSTCLAS AS CUSTOMERCLASSID,
            dbo.RM00101.COUNTRY AS COUNTRY,
            dbo.IV00101.ITMCLSCD AS ITEMCLASSID,
            dbo.IV40400.ITMCLSDC AS ITEMCLASSNAME,
            dbo.IV00101.USCATVLS_1 AS ITEMUDF1,
            dbo.IV00101.USCATVLS_2 AS ITEMUDF2,
            dbo.IV00101.USCATVLS_3 AS ITEMUDF3,
            dbo.IV00101.USCATVLS_4 AS ITEMUDF4,
            dbo.IV00101.USCATVLS_5 AS ITEMUDF5,
            dbo.IV00101.USCATVLS_6 AS ITEMUDF6,
            CASE MONTH(dbo.SOP10100.DOCDATE)
              WHEN 1
              THEN 'JAN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 2
              THEN 'FEB - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 3
              THEN 'MAR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 4
              THEN 'APR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 5
              THEN 'MAY - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 6
              THEN 'JUN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 7
              THEN 'JUL - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 8
              THEN 'AUG - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 9
              THEN 'SEP - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 10
              THEN 'OCT - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 11
              THEN 'NOV - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 12
              THEN 'DEC - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
            END AS DOCPERIOD,
            YEAR(dbo.SOP10100.DOCDATE) AS DOCYEAR,
            CASE MONTH(dbo.SOP10100.DOCDATE)
              WHEN 1 THEN 'JANUARY'
              WHEN 2 THEN 'FEBRUARY'
              WHEN 3 THEN 'MARCH'
              WHEN 4 THEN 'APRIL'
              WHEN 5 THEN 'MAY'
              WHEN 6 THEN 'JUNE'
              WHEN 7 THEN 'JULY'
              WHEN 8 THEN 'AUGUST'
              WHEN 9 THEN 'SEPTEMBER'
              WHEN 10 THEN 'OCTOBER'
              WHEN 11 THEN 'NOVEMBER'
              WHEN 12 THEN 'DECEMBER'
            END AS DOCMONTH,
            'Open' AS STATUS,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4
              THEN ( ( dbo.SOP10200.XTNDPRCE * -1 ) - ( dbo.SOP10200.EXTDCOST
                                                        * -1 ) )
              ELSE ( dbo.SOP10200.XTNDPRCE - dbo.SOP10200.EXTDCOST )
            END AS MARGIN,
            CASE WHEN dbo.SOP10100.SOPTYPE = 4
                      AND dbo.SOP10200.EXTDCOST > 0
                 THEN ( ( ( ( dbo.SOP10200.XTNDPRCE * -1 )
                            - ( dbo.SOP10200.EXTDCOST * -1 ) )
                          / ( dbo.SOP10200.EXTDCOST * -1 ) ) - 1 )
                 WHEN dbo.SOP10100.SOPTYPE = 4
                      AND dbo.SOP10200.EXTDCOST = 0 THEN -1
                 WHEN dbo.SOP10100.SOPTYPE <> 4
                      AND dbo.SOP10200.EXTDCOST > 0
                 THEN ( ( dbo.SOP10200.XTNDPRCE - dbo.SOP10200.EXTDCOST )
                        / ( dbo.SOP10200.EXTDCOST ) )
                 ELSE 1
            END AS MARGINPERCENT,
            LNITMSEQ AS SALESLINENO
    FROM    dbo.SOP10100
            INNER JOIN dbo.SOP10200
                ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE
                   AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE
            INNER JOIN dbo.RM00101
                ON dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR
            INNER JOIN dbo.IV40700
                ON dbo.IV40700.LOCNCODE = dbo.SOP10200.LOCNCODE
            LEFT OUTER JOIN dbo.RM00303
                ON dbo.SOP10200.SALSTERR = dbo.RM00303.SALSTERR
            LEFT OUTER JOIN dbo.IV00101
                ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR
            LEFT OUTER JOIN dbo.IV40400
                ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD
            LEFT OUTER JOIN dbo.RM00301
                ON dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID
            LEFT OUTER JOIN dbo.UPR00100
                ON dbo.RM00301.EMPLOYID = dbo.UPR00100.EMPLOYID
            LEFT OUTER JOIN dbo.RM00201
                ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
    UNION
    SELECT  CASE dbo.SOP30300.SOPTYPE
              WHEN 1 THEN 'Quote'
              WHEN 2 THEN 'Order'
              WHEN 3 THEN 'Invoice'
              WHEN 4 THEN 'Returns'
              WHEN 5 THEN 'Back Order'
              WHEN 6 THEN 'Fulfillment Order'
            END AS TYPE,
            dbo.SOP30200.SOPNUMBE AS DOCUMENTNO,
            dbo.SOP30200.DOCID AS TYPEID,
            dbo.SOP30200.DOCDATE AS DOCUMENTDATE,
            dbo.SOP30200.BACHNUMB AS BATCHNUMBER,
            dbo.SOP30200.CUSTNMBR AS CUSTOMERID,
            dbo.SOP30200.CUSTNAME AS CUSTOMERNAME,
            dbo.SOP30200.CURNCYID AS CURRENCY,
            dbo.SOP30300.ITEMNMBR AS ITEMNO,
            dbo.SOP30300.ITEMDESC AS ITEMNAME,
            dbo.SOP30300.UOFM AS UOM,
            dbo.SOP30300.LOCNCODE AS LOCATIONID,
            dbo.IV40700.LOCNDSCR AS LOCATIONNAME,
            dbo.SOP30300.UNITCOST,
            dbo.SOP30300.UNITPRCE AS UNITPRICE,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4 THEN ( dbo.SOP30300.XTNDPRCE * -1 )
              ELSE dbo.SOP30300.XTNDPRCE
            END AS TOTALPRICE,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4 THEN ( dbo.SOP30300.EXTDCOST * -1 )
              ELSE dbo.SOP30300.EXTDCOST
            END AS TOTALCOST,
            dbo.SOP30300.SALSTERR AS TERRITORYID,
            dbo.RM00303.SLTERDSC AS TERRITORYNAME,
            dbo.SOP30300.SLPRSNID AS SALESPERSONID,
            dbo.RM00301.EMPLOYID AS EMPLOYEEID,
            ( LTRIM(RTRIM(dbo.UPR00100.FRSTNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.MIDLNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.LASTNAME)) ) AS EMPLOYEENAME,
            ( LTRIM(RTRIM(dbo.RM00301.SLPRSNFN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSMN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSLN)) ) AS SALESPERSONNAME,
            dbo.SOP30300.PRCLEVEL AS PRIELEVEL,
            dbo.RM00201.CLASDSCR AS CUSTOMERCLASSNAME,
            dbo.RM00101.CUSTCLAS AS CUSTOMERCLASSID,
            dbo.RM00101.COUNTRY AS COUNTRY,
            dbo.IV00101.ITMCLSCD AS ITEMCLASSID,
            dbo.IV40400.ITMCLSDC AS ITEMCLASSNAME,
            dbo.IV00101.USCATVLS_1 AS ITEMUDF1,
            dbo.IV00101.USCATVLS_2 AS ITEMUDF2,
            dbo.IV00101.USCATVLS_3 AS ITEMUDF3,
            dbo.IV00101.USCATVLS_4 AS ITEMUDF4,
            dbo.IV00101.USCATVLS_5 AS ITEMUDF5,
            dbo.IV00101.USCATVLS_6 AS ITEMUDF6,
            CASE MONTH(dbo.SOP30200.DOCDATE)
              WHEN 1
              THEN 'JAN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 2
              THEN 'FEB - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 3
              THEN 'MAR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 4
              THEN 'APR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 5
              THEN 'MAY - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 6
              THEN 'JUN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 7
              THEN 'JUL - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 8
              THEN 'AUG - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 9
              THEN 'SEP - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 10
              THEN 'OCT - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 11
              THEN 'NOV - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 12
              THEN 'DEC - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
            END AS DOCPERIOD,
            YEAR(dbo.SOP30200.DOCDATE) AS DOCYEAR,
            CASE MONTH(dbo.SOP30200.DOCDATE)
              WHEN 1 THEN 'JANUARY'
              WHEN 2 THEN 'FEBRUARY'
              WHEN 3 THEN 'MARCH'
              WHEN 4 THEN 'APRIL'
              WHEN 5 THEN 'MAY'
              WHEN 6 THEN 'JUNE'
              WHEN 7 THEN 'JULY'
              WHEN 8 THEN 'AUGUST'
              WHEN 9 THEN 'SEPTEMBER'
              WHEN 10 THEN 'OCTOBER'
              WHEN 11 THEN 'NOVEMBER'
              WHEN 12 THEN 'DECEMBER'
            END AS DOCMONTH,
            'History' AS STATUS,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4
              THEN ( ( dbo.SOP30300.XTNDPRCE * -1 ) - ( dbo.SOP30300.EXTDCOST
                                                        * -1 ) )
              ELSE ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
            END AS MARGIN,
            CASE WHEN dbo.SOP30200.SOPTYPE = 4
                      AND dbo.SOP30300.EXTDCOST > 0
                 THEN ( ( ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
                          / ( dbo.SOP30300.EXTDCOST ) ) - 1 )
                 WHEN dbo.SOP30200.SOPTYPE = 4
                      AND dbo.SOP30300.EXTDCOST = 0 THEN -1
                 WHEN dbo.SOP30200.SOPTYPE <> 4
                      AND dbo.SOP30300.EXTDCOST > 0
                 THEN ( ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
                        / ( dbo.SOP30300.EXTDCOST ) )
                 ELSE 1
            END AS MARGINPERCENT,
            LNITMSEQ AS SALESLINENO
    FROM    dbo.SOP30200
            INNER JOIN dbo.SOP30300
                ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE
                   AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE
            INNER JOIN dbo.RM00101
                ON dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR
            INNER JOIN dbo.IV40700
                ON dbo.IV40700.LOCNCODE = dbo.SOP30300.LOCNCODE
            LEFT OUTER JOIN dbo.RM00303
                ON dbo.SOP30300.SALSTERR = dbo.RM00303.SALSTERR
            LEFT OUTER JOIN dbo.IV00101
                ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
            LEFT OUTER JOIN dbo.IV40400
                ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD
            LEFT OUTER JOIN dbo.RM00301
                ON dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID
            LEFT OUTER JOIN dbo.UPR00100
                ON dbo.RM00301.EMPLOYID = dbo.UPR00100.EMPLOYID
            LEFT OUTER JOIN dbo.RM00201
                ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID

Until next post, happy scripting…

Siva

2 May 2009 at 12:21 - Comments
Jason
Hello there, any tips on hitting non-company databases without creating every GP user in that database?
12 May 09 at 23:46
veeyeskay
@Jason Can u be more elaborate on what your requirement is?
13 May 09 at 02:33

SQL View for Inventory Aging

Guys

I have come across many requests from various friends and clients of mine, who have a need to print a stock aging report in GP.

There is no out-of-the-box report for stock aging report in GP. So I decided to write out a simple view to achieve the same.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE VIEW vw_IVStockAgeing
AS  SELECT  IV00101.ITEMNMBR AS ITEMNUMBER,
            IV00101.ITEMDESC AS ITEMNAME,
            IV10200.DATERECD AS DATERECEIVED,
            IV10200.QTYRECVD AS QTYRECEIVED,
            IV10200.QTYSOLD AS QTYSOLD,
            IV00101.ITMCLSCD AS ITEMCLASS,
            IV10200.UNITCOST AS UNITCOST,
            IV10200.PCHSRCTY AS RECEIPTTYPE,
            IV10200.RCPTNMBR AS RECEIPTNO,
            IV10200.TRXLOCTN AS LOCATION,
            ( IV10200.QTYRECVD - IV10200.QTYSOLD ) AS QTYAVAILABLE,
            ( ( IV10200.QTYRECVD - IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
            DATEDIFF(day, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
            CASE WHEN DATEDIFF(day, IV10200.DATERECD, GETDATE()) < 0
                 THEN 'Current'
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 0
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 30
                      ) THEN '0-30 Days'     -- 1 month
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 30
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 60
                      ) THEN '31-60 Days'     -- 2 months
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 60
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 90
                      ) THEN '61-90 Days'     -- 3 months
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 90
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 180
                      ) THEN '91-180 Days'    -- 6 months
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 180
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 360
                      ) THEN '181-360 Days'     -- 1 year
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 360
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 720
                      ) THEN '> 1 year'    -- 2 years
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 720
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 1440
                      ) THEN '> 2 years'    -- 3 years
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 1440
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 2880
                      ) THEN '> 3 years'-- 4 years
                 WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 2880
                        AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 5760
                      ) THEN '> 4 years'-- 5 years
                 ELSE '> 5 years'    -- Above 5 years
            END AS BUCKET
    FROM    ( IV00101 IV00101
              INNER JOIN IV10200 IV10200 ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
            )
            INNER JOIN IV00102 IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
    WHERE   IV00102.RCRDTYPE = 1
            AND IV10200.PCHSRCTY <> 0

This view can be further extended with the following options:

1. The aging buckets in this view are hardcoded. They can be defined as a setup in GP, using a simple Dex customization and using this setup the aging buckets can be made dynamic.
2. This view ages the stock as of the current date. This can be changed by modifying the view into a stored procedure which takes in a date as a parameter and the aging can be done as of a specific date as well.

Until next post, happy scripting…

Siva

2 May 2009 at 12:06 - Comments
Andrew
I got tasked to make an agin report with buckets and found this. I now have a good start! ...
26 May 09 at 21:36

Security Restrictions for Command Forms

Guys

One recent support request which came from a few friends of mine, is, when they try to login to GP, they get an error message “You do not have security access” and log them out of GP.

I checked with them to see if they have any customizations and they had a customized product which had a few custom menus created for the customization.

These customized menus were created using a Command Form and the mistake they had done is that, they gave the title of the window in the command form as “~Internal~’’. This may look very normal to everyone. But one thing everyone needs to keep in mind is GP is pretty much case sensitive… :-)

I asked them to change the title from “~Internal~” to “~internal~” and then the application loaded fine, without any security issues.

Let’s analyze this issue in detail here…

When you create a window with the title “~internal~”, this window bye-passes any security checks in GP. Hence the system does not check if the user has access to this specific window, since it is considered as an internal window, which gets loaded, whether the user has access to it (or) not. This title is case sensitive and needs to be in the correct case. Also the window name should be “Dummy” with the Auto Open property set to TRUE.

These kind of windows are generally used for performing application level tasks like creating menus etc. This is the reason why we create a command form and load all the commands into it and set the title of the window to “~internal~”, so that when GP loads, the application does not check for security on this window and allows us to initialize all the commands and create all the menus. :-)

I am sure many of you would be aware of this, but in case you are not aware of this fact, this article should benefit… Happy Reading…

Until next post…

Siva

28 April 2009 at 11:46 - Comments