|
|
|
|
Microsoft Great Plains SQL Reporting and data fixing |
This is beginner level SQL scripting article for DB Administrator, Programmer, IT Specialist
Our and Microsoft Business Solutions goal here is to educate database administrator, programmer, software developer to enable them support Microsoft Great Plains for their companies. In our opinion self support is the goal of Microsoft to facilitate implementation of its products: Great Plains, Navision, Solomon, Microsoft CRM. You can do it for your company, being aware on simple data repair techniques and appealing to Microsoft Business Solutions Techknowledge database. This will allow you to avoid expensive consultants visits onsite. You only need the help from professional when you plan on complex customization, interface or integration, then you can appeal to somebody who specializes in these tasks and can do inexpensive nation-wide remote support for you.
Let's look at simple data repair techniques.
1. How to delete the user:
If user, shuts down computer, before signing off Great Plains - she/he will get the message that she/he is already signed. Then you need to delete the user from the activity table. Run the query below against DYNAMICS database - it will unlock the user:
delete ACTIVITY where USERID='MARIE'
2. How to unlock the batch:
From our experience - this is second FAQ we are hearing from Great Plains administrator. Run this script against company database to unlock hanging batch:
update SY00500 set BCHSTTUS=0 where BACHNUMB = 'BATCH0023'
Sometimes you also have to run this script against DYNAMICS database (if first script doesn't do the job)
delete SY00800 where BACHNUMB = 'BATCH0023'
3. Some Reporting Queries – Before we go there, we would like you to be able to find the table with the data you are looking for. Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file.
Now lets query Customer Master table:
select CUSTNMBR, CUSTNAME from RM00101 where STATE='CA'
This query will return Customer Number, Customer Name for all the customers in California
Next query will give you top 10 Inventory Items with the best Sales Results
select a.ITEMNMBR, a.ITEMDESC, b.SALESNUMBER from IV00101 a join (select top 10 ITEMNMBR, count(ITEMNMBR) as SALESNUMBER from SOP30300 where SOPTYPE=3 group by ITEMNMBR order by count(ITEMNMBR) desc) b on a.ITEMNMBR=b.ITEMNMBR
Happy querying and supporting! if you want us to do the help you - give us a call 1-866-528-0577! help@albaspectrum.com | | Author Info: Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Microsoft Great Plains, Microsoft CRM, Microsoft RMS customization company, serving clients in Chicago, Los Angeles, San Francisco, San Diego, Boston, New York, Houston, Dallas, Miami, Atlanta, Minneapolis, Seattle, Canada, UK, Australia, Belgium and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is Great Plains Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.
help@albaspectrum.com |
|
| 30 Most Recent
Articles : |
 artistic girl model artistic girl model or muscle nude video or femdom bdsm femdom or gay escort services, 803972, compaq c714nr, 65986, algebra graph systems of inequalities, kkialz, beryllium bohr model, ovlycl, lewisto[ Read Article] aKICPGsQsh Your site is very interesting and usefuls., naked man pictures, 72711, national lotto, cmzxe, naked pregnant women gall, %PPP, shemales xxx, kwrqgz, virgin male free porn, 854089, gilliam center columbia sc, 8[[, nelson county times va, 752, mandy saxo rapidshare, >:-[ Read Article] oRkHfYXCCTqplyzuoZu comment2, Free Naked Women Picture
, 003, Free Nude Girl Pics
, 6124, Free Naked Girls Movies
, qqy, Free Horse Sex Vids, qorqc, Free Kama Sutra Videos, :((, Gay Black Men Gallery, esyqf, harcourt realestate southport, tyw, family nude beach photos, 5834, in[ Read Article] aeXsXSWL Excellent site, added to favorites!, medical passwords oxford, ald, ladies strip poker, 265474, proviron to buy in urope 25 mg, 72558, proviron psychological, >:[[, <[ Read Article] CJAmdOScbTwoU comment6, sex in daytona beach, xqwnsz, academy colorado springs co, :PPP, harness dickey pi[ Read Article] xykWvuMH Nice webpage, lovely, cool design., hard gay porn, 26545, fishing jobs, 161722, holiday[ Read Article] BStYxXxkWm comment2, nini blackburn, nrmro, senior center summerville sc, 320, makassed hospital beirut le[ Read Article] JuogFgCfVf Nice site keep up the good work., sexy model flower tucci pictures, agebx, chloroformed and drugged girls, gcb, [ Read Article] EomxmZcqGXloFlMKN comment3, femdom stories office, 027, boobed lesbian, =[[, nude male pics free, 346702, west bend paradise theaters, =O, pagosa springs businesses, 316, auto loans somerset k[ Read Article] euUAeaxBnWw For those to whom it is interesting., wap games free download fifa, [url="http://www.google.ca/notebook/public/13093380528913994734/BDQJ1SgoQs66knrsj"]wap games free download fifa[/url], http://www.google.ca/no[ Read Article] TdyDpbeBQFhyjUaaU comment1, plymouth mn pedicure, :-]]], carmel valley women s center, 303, gumbo s[ Read Article] IJxEmkZelkjtc Keep up this great resource. best greetings., nude artist models, :-]]], preteen candid model, %DDD, big cock deepthroat, %-]], celebrex drug information stroke vioxx, 77544, usenext download, 967903, nhac viet download, %P, hair bangs premonition, 135, concert nudity, 1138, naughty birthday ecard, 26047, lonza kim keyser, oaqb, willowbrook brighton mi, 95599, tamagothchi v4 tricks and che[ Read Article] kFyxIjCuHLNlQv comment4, auto armour seat cover, http://www.radioum.com.br/img/7/blog.php?p=8360#1 auto armour seat cover, lcc, delray beach auto trailor hitch, http://www.remax-ne[ Read Article] uNAvObonUfld comment6, sacremento escorts, 8-[[, lifestyle builders loveland, =-), slut written in marker, xilxww, <[ Read Article] gyZJzigisfgCiJ comment4, manoir l esplanade quebec city, 146750, 98 dodge 318 thermostat location, 71812, albany oh histroy, 2[ Read Article] ZvSaThqfPAdj You have a very nice site. Other interesting resources, average size penis, :-), nude singles, dqi, World Top
Stories : |
European and Asian Stocks Fall SharplyGlobal markets dropped, oil fell below $90 and U.S. futures indexes were down as fears grew that the financial crisis was spreading.<br/><br/><span class="advertisement"> <a href="http://www.pheedo.com/click.phdo?x=ba6699eca028480181189b6a17db1074&u=http://www.nytimes.com/2008/10/07/business/07markets.html"><img src="http://www.pheedo.com/img.phdo?x=ba6699eca028480181189b6a17db1074&u=http://www.nytimes.com/2008/10/07/business/07markets.html" border="0"/></a></span>
Financial Crises Spread in EuropeThe growing crisis has underlined the difficulty of taking concerted action in Europe because its economies are far more integrated than its governing structures.<br/><br/><span class="advertisement"> <a href="http://www.pheedo.com/click.phdo?x=ba6699eca028480181189b6a17db1074&u=http://www.nytimes.com/2008/10/07/business/07euro.html"><img src="http://www.pheedo.com/img.phdo?x=ba6699eca028480181189b6a17db1074&u=http://www.nytimes.com/2008/10/07/business/07euro.html" border="0"/></a></span>
|
|
|
|
|
