Monday, March 31, 2008

Finding columns in tables

In the past days, I have found a lot of people asking themselves, 'how do I know in which tables I can find xyz column name, and how do I know I have all the tables?' The answer to this question is often used to make database wide updates and perform a number of maintenance functions for master records and transactions that were not recorded as originally intended. The following example query attempts to solve the issue by exposing all tables were the account index (ACTINDX) column is found within the Fabrikam database (TWO).


-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
DECLARE @ColumnName VARCHAR(30);

SET @ColumnName = 'ACTINDX';

SELECT DISTINCT RTRIM(OBJS.NAME)
FROM SYS.COLUMNS COLS
  INNER JOIN SYS.OBJECTS OBJS ON (COLS.OBJECT_ID = OBJS.OBJECT_ID)
  INNER JOIN SYSINDEXES INDX ON (COLS.OBJECT_ID = INDX.ID)
WHERE (COLS.NAME = @ColumnName) AND (OBJS.TYPE = 'U') AND (INDX.ROWCNT <> 0);
GO


The results are as shown below:


CM00100
CM20400
GL00100
GL00103
GL00104
GL00105
GL00201
GL10001
GL10002
GL10110
GL10111
GL20000
GL30000
GL40101
IV30500
IVC10300
IVC10500
MC00200
MC00201
MC00300
PA00002
PM10500
PM30700
PM80700
POP10390
POP30390
RM10601
RM30601
SOP10102
SOP10105
SVC00230
SVC00231
SVC00609
SVC00731
SVC05030
SY01100
SY03100
TX00201
UPR00100
UPR10209
UPR19901
UPR30401
UPR40500
UPR41200



By checking the rowcount in the sysindexes table, we are ensuring that we focus our efforts in those tables that contain data. Very helpful, isn't it?

SQL Server 2005 and SQL Server 2008

If working with Microsoft SQL Server 2005 or above, the query can be simplified as follows:


SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ACTINDX' ORDER BY TABLE_NAME


Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


UPDATES TO THIS ARTICLE:
12/13/2008: Modified SQL query to look at system views and change article formatting.
01/08/2009: Added SQL Server 2005 and 2008 query to INFORMATION_SCHEMA

Sunday, March 23, 2008

Dynamics GP aims at QuickBooks users

I couldn't have let the day gone by without updating you with what's going on in the Microsoft Dynamics GP world. According to the Washington Post, "Microsoft is trying to make it easy for businesses to switch from Intuit's accounting products to its applications". Read the full article at:

http://www.washingtonpost.com/wp-dyn/content/article/2008/03/23/AR2008032301121.html


Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Removing Child Records from a National Account

This is an actual problem I faced at an actual customer of mine a few months aback and posted today on the Google's Dynamics GP board. It seems that more often than one could assert, a customer's payment records get assigned to the wrong national account (or parent account). This can cause problems down the road when incorrect statements or aging reports are submitted to the corporate customer.

In light of this issue, I developed a two fold solution:

1) The following query identifies the credit documents posted and applied by the parent customer on behalf of the child account.


-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode

DECLARE @ChildCustomerNumber CHAR(21), @ParentCustomerNumber CHAR(21);
SET @ChildCustomerNumber = '{child_customer}';
SET @ParentCustomerNumber ='{parent_customer}';

with ApplyInfo as (
 select 
    custnmbr as ChildCustomerId
  , cprcstnm as ParentCustomerId
  , trxsorce as TransactionSource
  , date1 as ApplyDate
  , glpostdt as GLPostingDate
  , aptodcnm as InvoiceNumber
  , aptodcty as DocumentType
  , aptodcdt as InvoiceDate
  , apptoamt as InvoiceAmount
  , apfrdcnm as PaymentNumber
  , apfrdcty as PaymentType
  , apfrdcdt as PaymentDate
  , apfrmaplyamt as ApplyAmount
  , actualapplytoamount as ActualAmountApplied
 from rm20201
 union all
 select 
    custnmbr as ChildCustomerId
  , cprcstnm as ParentCustomerId
  , trxsorce as TransactionSource
  , date1 as ApplyDate
  , glpostdt as GLPostingDate
  , aptodcnm as InvoiceNumber
  , aptodcty as DocumentType
  , aptodcdt as InvoiceDate
  , apptoamt as InvoiceAmount
  , apfrdcnm as PaymentNumber
  , apfrdcty as PaymentType
  , apfrdcdt as PaymentDate
  , apfrmaplyamt as ApplyAmount
  , actualapplytoamount as ActualAmountApplied
 from rm30201
)
select * from ApplyInfo where ChildCustomerId = @ChildCustomerNumber and ParentCustomerId = @ParentCustomerNumber;
GO 


2) You will then need to use Dynamics GP's Professional Services Tools Library (PSTL) to unapply the invoices associated to the child record, which in turn will unapply the credit documents posted under the national account. Make sure you print all reports generated by Professional Tools as you will need them to reapply the credit documents.

Well, hope this helps in solving a major headache faced by many AR departments around the globe.

Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.IntelligentPartnerships.com/

Edit:

09/25/2014: The previous script has been replaced with this new script which directly retrieves the parent customer column from the apply table, thus improving the results. This script has been tested with Microsoft Dynamics GP 2010 and above. Support for common table expressions (CTEs) were introduced with SQL Server 2005, thus this script may not work with prior versions of SQL Server.

Saturday, March 22, 2008

Payables Transactions not in GL

Picture this: the auditors are in, they asking for myriads of reports, and precisely one of the things they ask is something you cannot easily achieved from the slur of reports and SmartLists available from within Microsoft Dynamics GP. To make matters worst, the "go to" guy for queries and special requests is out of the office sick with the flu. Well worry no more! If you are asked to show all the payables transactions that were never recorded or posted to GL you can run the following query from SQL Server Management Studio (or Query Analyzer if you still happen to be on SQL Server 2000):

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT
   A.VENDORID, A.DOCNUMBR, A.DOCDATE, A.CNTRLNUM, GL.JRNENTRY,
   GL.TRXDATE, GL.ACTINDX, C.ACTNUMST, D.ACTDESCR, GL.REFRENCE,
   GL.ORTRXTYP, GL.ORCTRNUM, GL.ORMSTRID, GL.ORMSTRNM,
   GL.ORDOCNUM
FROM PM00400 A LEFT OUTER JOIN
  ( SELECT JRNENTRY, TRXDATE, REFRENCE, ORTRXTYP, ACTINDX, ORCTRNUM, ORMSTRID,
      ORMSTRNM, ORDOCNUM
    FROM GL20000
    WHERE SERIES = 4

    UNION ALL

    SELECT JRNENTRY, TRXDATE, REFRENCE, ORTRXTYP, ACTINDX, ORCTRNUM, ORMSTRID,
      ORMSTRNM, ORDOCNUM
    FROM GL30000
    WHERE SERIES = 4
  ) GL ON(A.VENDORID = GL.ORMSTRID) AND (A.CNTRLNUM = GL.ORCTRNUM) AND 
    (A.DOCNUMBR = GL.ORDOCNUM)
LEFT OUTER JOIN GL00105 C ON (GL.ACTINDX = C.ACTINDX)
LEFT OUTER JOIN GL00100 D ON (GL.ACTINDX = D.ACTINDX);
GO


Knowing what transactions have not been posted or recorded in GL is particularly useful when attempting to reconcile the AP subsidiary module to General Ledger. It will also allow you to identify those records loaded as beginning balances from within AP.

With little or no effort, this query can be added to SmartList Builder (SLB), by creating a SQL Server view with the inner query, and exposing the view to SLB. Don't forget to run the GRANT.SQL utility script to assign permissions to the DYNGRP.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Friday, March 21, 2008

Dynamics GP 10.0 Service Pack 2 Compatible with SQL Server 2008 and Windows 2008

The news everyone was expecting! The Microsoft Dynamics GP 10.0 Development team has "certified" the product will work on both Microsoft Windows Server 2008 and Microsoft SQL Server 2008 release candidates. The team will continue to test the product on the RTM version. According to the team "it is expected that full functional compatibility will be achieved with Microsoft Dynamics GP 10.0 Service Pack 2, which will be made available with the Microsoft Dynamics GP 10.0 Feature Pack 1 release, targeted to RTM in Q2 of CY2008". That's it for now, I will keep you posted on any new events.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Saturday, March 15, 2008

Economic Times and Microsoft ERP Applications

I want to open this blog by commenting on the current economic times and the middle market segment of ERP applications. It's certainly interesting to see how key vendors such as Microsoft, Epicor, Sage, Lawson, among others, are revamping their marketing and sales strategies to gain new customers. The suite of products, VARs, and ISV offerings in this space are especially well positioned to become the "go to" solutions for budget concious organizations looking for well priced and robust products, and never before, has Microsoft been in a better position to advance sales within its Business Solutions division. With a wide array of business applications, global network of partners and ISVs, and short time to market, Microsoft is certainly a force to reckon with.

Technology platform can no longer be considered a point of contention for those looking to introduce the "scalability and reliability" argument. With the introduction of the "triple-play" lauch (Windows Server 2008, SQL Server 2008, and Visual Studio 2008), Microsoft clearly seeks to take advantage of the economic downturn by allowing its customers (the new ones at least) to acquire a robust platform for their business and technology infrastructure. Furthermore, the MBS Dynamics application suite will soon follow as being perhaps the first readily available products from Microsoft to take advantage of its newly released platforms.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/