Friday, July 30, 2010

From the Newsgroups: What are those GL entries with reference SALESASMxxxx?

Welcome to another edition of From the Newsgroups.

The Microsoft Dynamics GP Online Partner Technical Community forum is one of those virtual places where you get to see and experience it all. One gets to become familiar with real life issues experienced by us partners keen enough to share our implementation and support issues. The following is a thread from the Online Partner Technical Community:

Good afternoon. Our customer keeps getting these "mystery" GL postings [SALESASMxxxx] and wants to know why they are being generated but I cannot find any article or search that shows this document prefix for GP. Does anyone know what type of doc this is and why it may be generated automatically into GL?


The answer comes courtesy of Tristan Thor Clores, a Microsoft Support Engineer.

For transactions that have the SALESASM prefix in their description: These may mean that a sales invoice with a shortage override of a top level BOM (also known as finished good) was posted at one cost. Then when the BOM that fulfilled the shortage was posted its unit cost was different from what the invoice held.

------------------------------------------------------------------------------------
Example:
Let's pretend that we have a $0.00 balance in the Inventory-Finished Goods account. An invoice was posted with a shortage override for 10 units of finished good item HARD DISK. Its unit cost is $10.00 and its unit price is $20. Its journal entry will look like the following:

Debit: Accounts Receivables $200.00
Debit: Cost of Sales $100.00

Credit: Sales $200.00
Credit: Inventory-Finished Goods $100.00

However, when a BOM assembly was posted for 10 units of HARD DISK, its unit cost was actually at $12.00 per base unit. Let's pretend that it has the following account distributions:

Debit: Inventory-Finished Goods $120.00

Credit: Inventory-Assembly Component #1 $50.00
Credit: Inventory-Assembly Component #2 $70.00

This journal entry will result in you having 0 units On Hand for the item but with a debit balance of $20.00 in the General Ledger. So now, Microsoft Dynamics GP will create the following cost variance journal entry to remove this balance and help your GL recognize the true cost of the sale. This change though will never hit Sales Order Processing history and so the SOP Document Analysis report will not print this additional cost and will then print an incorrect profit margin afterwards:

Debit: Cost of Sales $20.00
Credit: Inventory $20.00
------------------------------------------------------------------------------------

I would also like to share the following list with you. It is a list of the transaction reference prefixes that Microsoft Dynamics GP support engineers have encountered with cost variance journal entries so far:

1. BOM – assembly transaction
2. INV – invoice from the Invoicing module
3. IVT – inventory transfer
4. IVA – inventory adjustment
5. IVV – inventory variance
6. SALES – sales invoice from Sales Order Processing
7. PRTN – purchase return
8. MCTE – for a transaction that originated in Manufacturing Component Transaction Entry
9. MRCT – manufacturing receipt
10. MCLS – manufacturing close, including regular close and Quick MO
11. STCK – stock count variance
12. FSSC – field service Service Call
13. FSRMA – field service RMA
14. FSRTV – field service RTV
15. FSWO – field service Work Order
16. PA – project accounting
17. POP – close a PO line in Edit PO Status
18. RECON – created by Inventory Reconcile
19. CONV - created by an upgrade conversion
20. MCTERCT - If the items were issued in the Component Transaction Entry window and then the invoice is completed the adjustment will have a prefix of MCTERCT
21. MRCTRCV - If the items were issued in Mo Receipt Entry (they are "backflushed") and then the invoice is complete the adjustment will have a prefix of MRCTRCV.
22. MCLSRCV - If the items were issued in the MO Close process (the MO was partially received and more "backflushed" items were issued during the close) and then the invoice is completed the adjustment will have a prefix of MCLSRCV.

I recommend going through KB Article 869470 - "Cost Variance for Inventory," "Sales Order Processing," and "Purchase Order Processing" for mor details regarding cost variance in the system.

Let me know how this goes.


I hope you find this article very interesting.

Related Articles

What do those strage reference codes in GL mean?

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Dex - Ctree temp tables versus SQL Server temp tables

For many of you who have been in the channel long enough, whether as developers, partners, or customers and have been using GP for that long, you may still be able to remember the days of c-tree and Btrieve.

Just recently, a developer approached my good friend David Musgrave with a question on a product they had created and been using since the days of GP 6.0. This product makes extensive use of c-tree temp tables and the developer wanted to know whether it was still ok to continue using c-tree tables or convert those to SQL Server temp tables. He received the following answer:


There is nothing wrong with c-tree temp tables. They are faster. SQL Temp tables take a bit of work to create so ctree has less overhead.

The only reasons to change them back to Default or SQL are:

1) You are using them on a report. This allows SQL optimization when generating the report with a single SQL Joined query.
2) You want to use SQL optimized functions such as range where clauses or range copy.
3) The number of records being placed in the temp table is huge and it could cause a local workstation’s hard disk to fill up.

Otherwise, just leave them as is. My policy is to use ctree unless one of the points above says that SQL would be better.

To add to this response, c-tree temp tables are still supported by Microsoft and are used extensively throughout the Microsoft Dynamics GP application. In the end, this may be one of those cases of "if it ain't broken don't fix it".

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, July 23, 2010

GPWindow: The next best thing after the Microsoft Dynamics GP 2010 Cookbook

I have to give credit where credit is due. MVP Mark Polino may have the hottest summer read (see my article Cookbooks), but my good friend and fellow blogger Jivtesh Singh has the mother of all blog sites... literally! Jivtesh is one of those die hard and passionate about Dynamics GP individuals. He probably saw the same problem many of you have seen with the proliferation of Microsoft Dynamics GP blogs and asked himself the same set of questions you may be asking yourself: "how the heck do I keep up with all these blogs?", "I saw an article on [some topic here], where did I read it?".

Jivtesh now provides an answer to these questions with his new GPWindow platform, which aggregates every single blog article, video, and resource about Microsoft Dynamics GP.








C'mon, you know there's absolutely no reason to do this! For all I know, Jivtesh could be home writing cool articles for his own blog... but that's not how we Microsoft Dynamics GP advocates and evangelists feel about the subject. We always need to go the extra mile. Jivtesh has spent a considerable amount of time (over 1 year!) categorizing all these articles and topics and in his own words "Categorization is a work in progress, about 5000 links have been categorized and about 1000 more to go. It’s a very time consuming, but enjoyable process, which I do after my work hours or over the weekend. I enjoy organizing information, so I don't mind all the time this has taken me.".

It's time for you to do your part. Visit GPWindow and at least take the time to play around, but more importantly, let Jivtesh know what you think about this site.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, July 19, 2010

Microsoft Dynamics GP 2010 Architecture Whitepaper available for download

Microsoft Dynamics GP 2010 Architecture Whitepaper is now available for download from Microsoft Download Center. Architecture is something dear to my heart so please be sure to download this document and read up. The document discusses the following topics:

  • The structure of the Microsoft Dynamics GP application, which is composed of the Dexterity runtime engine, the Dynamics application dictionary, and the Microsoft SQL Server database.

  • How Microsoft Dynamics GP efficiently uses system resources for both client workstations and servers.

  • The customization tools that are built into the software and are accessible to all users, and the tools that system administrators can use to customize Microsoft Dynamics GP, such as Modifier, Report Writer, and Visual Basic for Applications (VBA).

  • The tools that are available for importing and exporting Microsoft Dynamics GP data, such as eConnect, Web Services, and Integration Manager.

  • The features that provide platform enhancements, such as flexible account numbers, lists, data-driven scrolling windows, security, international support, macro capabilities, named printers, the mail API, and error reporting.

  • The reporting and analytics tools that are available for Microsoft Dynamics GP, including Report Writer, SmartList, Management Reporter, SQL Reporting Services, and Excel Reports

  • How the workflow capabilities in Microsoft Dynamics GP control the flow of documents through the accounting system

  • The architecture and features of Business Portal, which you can use as your company intranet or as an extension to it to provide Web-based access to Microsoft Dynamics GP data.

  • The additional industry- or market-specific capabilities that are included with Microsoft Dynamics GP, such as multidictionary architecture, the trigger system, and available development options.
Hope you find this whitepaper useful and certainly a gem to have in your product documentation arsenal.

Related Articles

Microsoft Dynamics GP Architectural Foundations Series - featuring Microsoft's Tim Brookins

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, July 16, 2010

Cookbooks

Summer is just getting sizzly here in the United States and as a customary tradition, we begin rolling out our grills and dusting off our cookbooks, but this time around, my wife had me ordered two new cookbooks from Mark Bittman, an American food journalist and author of some of the best cookbooks in the market today. Here is my grill and the cookbooks to go with it.


Webber One Touch Gold



Mark Bittman's How to cook everything series


I am among other things an avid cook, so I was trying to find something productive to do while the stakes were on the grill. I had to decide between the usual and traditional ales (while I wait for the food) or, well more cookbooks. I figured I would log into Amazon and see what was hot. Two weeks later my shipment arrived, and I just could not wait to get my hands on it -- just in time for the summer!



I could not wait to open the box to get my hands on this new cookbook that would revolutionize my downtime while steaks and veggies were on the grill.



This was the ultimate cookbook!



The Marks have just made my summer all the more entertaining! Now, let's get grilling!



What are you waiting on? Order your Microsoft Dynamics GP 2010 Cookbook now by Mark Polino, MVP!



Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

New Article on MSDynamicsWorld: "When It Comes to Customizations for Microsoft Dynamics GP, Which Tool Should You Rely On?"




"The term "customization" can mean different things to different people..."

My new article is out over at MSDynamicsWorld. This time, I go back to basics defining what is a customization and what tools are available to customize the Microsoft Dynamics GP user interface. This article is a good start if you are still trying to figure out your options for developing add-on solutions to Microsoft Dynamics GP. To read the full article, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, July 15, 2010

Enforcing Password Policy with Microsoft Dynamics GP

The ability to enforce password policies was introduced since version 9 of Microsot Dynamics GP (see Why does Microsoft Dynamics GP encrypt passwords? over at Developing for Dynamics GP for more information).




Surprisingly, still many system administrators are not taking advantage of this feature, because they have found it difficult to manage without certain reporting necessary to follow up on Microsoft Dynamics GP logins activity. The typical complaints revolve around the lack of visibility on when a user password will expire or whether the account has been locked or not.

To make administrative tasks even more difficult, Dynamics GP systems administrators must rely on database administrators and Windows Server administrators to resolve any issues arising from a user being locked out the system, typically working their way through a helpdesk on a relatively simple issue.

With that said, I set out to create a query that could provide systems administrators with an insight into Microsoft Dynamics GP logins and their password expiration settings:

LoginPolicies.sql

use master;
go
set nocount on;
go
declare @loginname varchar(200);

declare @logintbl table (
LoginName varchar(20)
,IsLocked char(5)
,DaysUntilExpiration int
);

declare c_logins cursor for
select [name] from sys.syslogins where name in
(select USERID from DYNAMICS..SY01400);
open c_logins;

fetch next from c_logins into @loginname;
while @@FETCH_STATUS = 0
begin
insert @logintbl(LoginName, IsLocked, DaysUntilExpiration)
select
@loginname
,case convert(smallint, LOGINPROPERTY(@loginname, 'IsLocked')) when 0 then 'No' when 1 then 'Yes' end
,convert(int, LOGINPROPERTY(@loginname, 'DaysUntilExpiration'));

fetch next from c_logins into @loginname;
end

close c_logins;
deallocate c_logins;

select * from @logintbl;
go
set nocount off;
go


When the above query is executed in Microsoft SQL Server Management studio, it produces the following results:


LoginName IsLocked DaysUntilExpiration
-------------------- -------- -------------------
sa No NULL
DYNSA No NULL
LESSONUSER1 No 0
LESSONUSER2 No NULL


Note that this query uses a table variable. If you are looking for a more permanent solution, you can replace the table variable for an actual table.

You may also use Support Debugging Tool's SQL Execute option to run the above query -- I have attached the configuration file for the script to be imported into Support Debugging Tool using the Configuration Import/Export option.




Many system administrators would also want to know when was the last time a user logged into GP, but unfortunately, SQL Server does not keep track of login activity, unless you enable some of the auditing functions. Another alternative is to enable Activity Tracking in GP and track all successful login attempts sorted from the most recent. You may then incorporate this information in the above query for a cohesive result.

Related Resources

Microsoft Dynamics GP Application Level Security Series @ Developing for Dynamics GP
The Scoope on Dynamics GP's Application Password System @ Inside Dynamics GP

Downloads

Support Debugging Tool XML configuration file - LoginPolicies.dbg.xml

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, July 14, 2010

DYNSA

There are a number of good blog posts in the community explaining the differences between sa, Microsoft SQL Server's defacto administrative user; and DYNSA, Microsoft Dynamics GP defacto administrative user. In reading a number of these articles I realized one thing: they address little about the DYNSA user.

So what's DYNSA?

You only have to read back a few lines to obtain this answer, however, I want take another direction. Let's start by saying that in Microsoft SQL Server, every database is created with a dbo user. Take for example this blank database created using SQL Server Management Studio:



The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role (like sa for example) who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user DYNSA is a member of the sysadmin fixed server role and creates a table RM00101, RM00101 belongs to dbo and is qualified as dbo.RM00101, not as DYNSA.RM00101. Conversely, if DYNSA is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table RM00101, RM00101 belongs to DYNSA and is qualified as DYNSA.RM00101. The table belongs to DYNSA because he did not qualify the table as dbo.RM00101... and hear is the catch! Because DYNSA is not created as a member of the sysadmin fixed role (otherwise we would have a lot of angry database administrators), it must be associated to fixed database role db_owner, hence all object creation operations performed from Dynamics Utilities must qualify the object creation preceeded by "dbo.".

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

This explains two things:

1) Only sa can perform the first time installation of Microsoft Dynamics GP, since this is when all databases, database objects, and the DYNSA user are created. During installation, DYNSA is made a member of the db_owner fixed database role.

2) In lieu of the sa user, Microsoft Dynamics GP system administrators can use DYNSA to perform any upgrade or maintenance activities. Since DYNSA is the defacto database owner, then it can perform any activities related to that database. This is the reason why you should ensure that DYNSA remains the database owner prior to initiating any upgrade activity. The following script should help with this:


1. Run the following making sure that DYNSA is the database owner:


sp_helpdb DYNAMICS;
go
sp_helpdb %COMPDB%;
go


2. If the above returns anything different than DYNSA, use the following script to set the database owner to DYNSA:


use DYNAMICS;
go
sp_changedbowner 'DYNSA';
go
use %COMPDB%;
go
sp_chagedbowner 'DYNSA';


In the above scripts, replace %COMPDB% for your company database name on SQL Server.

Related Articles:

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, July 9, 2010

eConnect Integration Service for Microsoft Dynamics GP 2010

One of the biggest improvements in eConnect 2010 is the addition of the new eConnect Integration Service. The eConnect installer now creates a new Windows Service application named eConnect for Microsoft Dynamics GP 2010 integration Service. The eConnect Integration Service is a Windows Communication Foundation (WCF) service that replaces the (very unstable) eConnect COM+ object available in previous versions of eConnect.

The eConnect Integration Service supports the operations of the eConnect .NET assemblies, the BizTalk adapter, and MSMQ interfaces. In addition, you can use the service directly from an application that adds a service reference to the eConnect Integration Service.




If you add a service reference, you do not need to add the Microsoft.Dynamics.GP.eConnect assembly and namespace to your development project.



To add the eConnect Integration Service to an application, you must first add a service reference to the Visual Studio project for that application.

To add a service reference to a Visual Studio project, the properties for the project must specify the target framework as .NET Framework 3.5. The following is a typical URL for the eConnect Integration service.

net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/


The interface provided by the service reference includes the same methods you find in the Microsoft.Dynamics.GP.eConnect assembly and namespace. Before you use a service reference to access eConnect Integration Service, you should become familiar with WCF development concepts.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, July 7, 2010

From the Newsgroups: What happened to Integration Manager "Save As" function?

In previous versions of Integration Manager you had the ability to save an integration with another name, but v10 onwards this feature was removed. Here is what Beth Gardner from the Microsoft Dynamics GP Online Partner Technical Community had to say:

"We removed the Save As functionality in one of the IM 10.0 service packs. The reason why, was the Save As feature had a bug with it for many years. It would copy the integration source file instead of creating a new integration source file in the new integration. This meant that if you were to change the source file on the second integration to browse to a different file, the original integration source file would also change.

This caused a large amount of confusion and problems for customers. Due to this, we removed it from the menu. What you will need to do instead is use the File Import Integrations process. This will allow you to browse out the the IM.mdb file you currently have open and select integrations. You will then be prompted to change the integration name and integration source names. This is a true copy of the original integration but has no ties to it and everything is unique.



This is the process you will need to follow going forward."


I hope you found this information useful.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, July 2, 2010

Microsoft Dynamics GP 2010 and Office Web Components

A recurring question throughout these days of upgrades to Microsoft Dynamics GP 2010 is what version of Microsoft Office Web Components (OWC) is compatible with Microsoft Dynamics GP 2010. In this article, I will address this question by explaining what is OWC and how it is used.

OWC are a collection of Component Object Model (COM) controls for publishing spreadsheets, charts, and databases to the Web and are required to support the display of metrics on a Microsoft Dynamics GP home page. These components have been released with various versions of Microsoft Office, as shown below.







Source: OfficeBlogs.net

The latter is important to understand, because the Microsoft Dynamics GP home page is an HTML rendering done from Dexterity code with the aid of some XSL and cascading stylesheets. The HTML code embeds calls to OWC functions via JScript to display the metrics.

The current supported version of OWC is Microsoft Office 2003 Web Components for versions 9, 10, and 2010.

So now that we have gotten past this point, Office Web Components have been discontinued in Office 2007 (so don't expect to see them in Office 2010 either), and are not included, except as a part of Microsoft Office Project Server 2007. However, they will still be available for download from Microsoft's website. Microsoft has not yet offered a complete replacement for the Office Web Components. However, developers can use a combination of third party products, Excel Services or Visual Studio Tools for Office to provide similar functionality.

My guess is that Fargo will continue to use OWC until there is a new roadmap in place for the product. We may have started to see some of this with the use of the Dundas Chart Controls, featured in Microsoft Dynamics GP 2010 if using SSRS metrics.

Please let me know what you think.

Related Articles

Microsoft Office 2003 Web Components - download

KB Article 916673 - Error message when you view the home page in Microsoft Dynamics GP: "Metrics are not available because Microsoft Office Chart..."

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Thursday, July 1, 2010

Third year as a Microsoft MVP

This time of the year brings some stress for those of us MVPs in the July renewal cycle and holders of this prestigious Microsoft award. The bottom line is, every July 1st I find out whether I continue to keep my MVP stripes or... not! The good folks at Microsoft have felt once more that my contributions to the Microsoft Dynamics GP community in general have made me worthy of such honor, so it's with great joy that I announce I get to keep my award for one more year starting today, making it the 3rd consecutive year of such privilege.

NOTE: The record for consecutive years in our track is currently held by MVP Victoria Yudin, with 6 consecutive years.

Also, I must congratulate MVP Frank Hamelly for being reawarded. Frank's contributions have also earned him the right of passage.

So you ask, "what does a Microsoft MVP do?" Here is just a few things I did since last July:

  • Published over 160 blog articles on my site with replies to more than 100 comments
  • Answered over 2500 questions on the various user newsgroups and forums
  • Answered over 1300 questions on the Partner Technical Forum
  • Wrote over 20 articles for various outlets
  • Presented at the Microsoft Dynamics GP Technical Conference 2009
  • Presented at the Microsoft Convergence 2010 in Atlanta
  • Presented at the Decisions 2010 Spring virtual conference
  • Continue to provide beta testing support for Support Debugging Tool and Menus for Visual Studio Tools.

Ah, and more importantly, continue to earn a living for the family while doing all the above. For more information on the Microsoft MVP Award Program, visit the MVP Award Program Blog site.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/