(1888PressRelease)
April 05, 2007 - 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
Alba Spectrum Group, http://www.albaspectrum.com help ( @ ) albaspectrum dot com 1-866-528-0577, 1-630-961-5918, serving Microsoft Dynamics GP locally in Houston, Dallas and Chicago metros as well as USA / Canada nationwide via remote support: California, New York, Florida, Arizona, Colorado, Wisconsin, Indiana, Georgia, Louisiana, Quebec, Ontario, Washington, Nevada, Virginia, Pennsylvania, Ohio, Iowa, Nebraska, North and South Dakota, Wyoming dot
Alba Spectrum Group, http://www.albaspectrum.com help ( @ ) albaspectrum dot com 1-866-528-0577, 1-630-961-5918
###
