Finding columns in tables

March 31, 2008

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).


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 = 'ACTINDX') and (objs.type = 'U') and (indx.rowcnt 0)

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 SQL Server 2008, the above 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, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.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


Dynamics GP aims at QuickBooks users

March 23, 2008

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”. Check the full article at http://www.washingtonpost.com/wp-dyn/content/article/2008/03/23/AR2008032301121.html

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


Removing Child Records from a National Account

March 23, 2008

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 agings 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.


declare @custnmbr char(21), @parent char(21)
set @custnmbr = 'YOUR_CHILD_CUSTOMER'
set @parent = 'YOUR_PARENT_CUSTOMER'

select distinct aptodcnm from
( select aptodcnm from rm30201 where custnmbr = @custnmbr and apfrdcnm in
( select docnumbr from rm20101 where custnmbr = @parent and RMDTYPAL = 9
union
select docnumbr from rm30101 where custnmbr = @parent and RMDTYPAL = 9
)
union
select aptodcnm from rm20201 where custnmbr = @custnmbr and apfrdcnm in
( select docnumbr from rm20101 where custnmbr = @parent and RMDTYPAL = 9
union
select docnumbr from rm30101 where custnmbr = @parent and RMDTYPAL = 9
)
) unapply

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, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

UPDATES
05/28/2009 – Edited text and added link to article in Google’s Dynamics GP group. Included signature.


Payables Transactions not in GL

March 23, 2008

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):



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 dbo.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)

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 begining 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, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/


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

March 22, 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.

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


Economic Times and Microsoft ERP Applications

March 15, 2008

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.

So, let the game begin and may the best survive.

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