Popular SQL Queries for Microsoft Dynamics GP – highlights for consultant

As technology advances, computer professional needs balanced level of SQL querying skills to support ERP application, sufficient to be able to produce queries for reporting, minor data repair, transaction auditing.  Typical scenarios are: month closing and the need to analyze transactions which need to be corrected via General Ledger offsets.  Let’s come to examples:

The following query will give you transactions which are originated in SOP with SOP number starting with SLS-10, where distribution goes to Cost of Goods Sold account

select

b.ACTNUMBR_1+' ' +b.ACTNUMBR_2+' ' +b.ACTNUMBR_3 as Account,

a.JRNENTRY, a.DEBITAMT, a.CRDTAMNT, a.SOURCDOC, a.REFRENCE, a.DSCRIPTN, a.TRXDATE

from

GL20000 a

join GL00100 b on a.ACTINDX=b.ACTINDX

where

b.ACTNUMBR_1 like '5%'

and a.OPENYEAR=2007

and a.SOURCDOC='SJ'

and a.ORDOCNUM like 'SLS-10%'

order by TRXDATE desc

Well, if you think it is too complex and you really don’t have a clue, please let’s go to starting point.  In order to analyze and research GP tables structure, go to Tools->Resource Description->Tables.  If you are familiar with SQL – this reference will give you enough info to advance our query above or design your own query

For MS Dynamics GP beginners we would like to reprint the most popular SQL scripts and fixes:

Delete stalled user:  delete activity where userid=’john’ – run this query against DYNAMICS  database, change John to real userid

Unlock stalled posting batch.  Update SY00500 set BCHSTTUS=0. MKDTOPST=0 where BACHNUMB=’MYBATCH’ – run it against company database, replace MYBATCH with real batch name

Getting message – this document is edited by another user.  In this case analyze SY00801 table, see if the lock is there and unlock.  We will not provide the script here, as it is surgery and should be done by professional