SQL: Querying Microsoft Great Plains – Overview for Database Administrator/Developer

SQL: Querying Microsoft Great Plains – Overview for Database Administrator/Developer

Seems to be like Microsoft Great Plains becomes far more and additional preferred, partly since of Microsoft muscle groups at the rear of it. Now it is specific to the complete spectrum of horizontal and vertical sector clientele. Tiny companies use Tiny Organization Manager (which is centered on the exact technology – Fantastic Plains Dexterity dictionary and runtime), Terrific Plains Common on MSDE is for smaller to midsize purchasers, and then Excellent Plains serves the rest of the current market up to huge organizations. There are several reporting applications out there and you undoubtedly require to know which a single to use for different types of experiences.

If you are database administrator who is asked to import some facts to Fantastic Plains or repair service or duplicate information from 1 enterprise to an additional – examine this and you will have the clues on exactly where to seem further.

1. Microsoft Wonderful Plains Tables Construction – Launch Terrific Plains and go to Instruments->Resource Description->Tables. Come across the desk in the appropriate collection. If you are looking for the buyers – it must be RM00101 – shopper learn file.

2. DEX_ROW_ID. This is id column and each individual Great Plains table has it – this is because of to the Terrific Plains Dexterity technological know-how. This column is hardly ever applied as a important field – so will not try to link your tables on DEX_ROW_ID. In circumstance if you will need to transfer the desk from one firm to a different you should use these queries:

Decide on * into GL00100_BAK from TWO.dbo.GL00100
go
change table GL00100_BAK drop column DEX_ROW_ID
go
insert into GL00100 choose * from GL00100_BAK
go
fall table GL00100_BAK

The set of queries above will transfer GL00100 (Account Master table) from TWO organization into your present-day enterprise. Then you need to have to operate Checklinks – refer to GP Manual – in buy to recreate the rest of the account learn similar tables.

3. Do not modify the desk – at times it would seem to be wonderful if you just append couple of more columns to the desk – like in IV00101 – inventory grasp file why would not you just include couple of further descriptions. If you do this – Good Plains Dexterity engine will fail examining all your things – owing to the reality that DYNAMICS.DIC (major Terrific Plains Dictionary file) has actual description of all the tables and Dexterity takes advantage of it for studying and creating into the precise table

4. Feel absolutely free to make SQL views or saved procs. If you are helping your inside developers to create Crystal Reports – superior SQL sights are authentic aid to them. Enable me give you instance, the watch down below will present do the job and historical SOP Invoices – then Crystal can just use it

Build watch SOP_Get the job done_HIST
as
pick SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 where by SOPTYPE=3 and VOIDSTTS=
union
find SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 where SOPTYPE=3 and VOIDSTTS=

The higher than view will demonstrate all the work and historic non-voided invoices (SOP Style = 3 stays for bill)

5. Some repair service / unlocking guidelines:

If you run query above towards DYNAMICS databases – it will unlock the person, who unintentionally shut down the personal computer without logging off Good Plains:

delete Exercise where by USERID=’JOHN’

Next one particular will unlock hanging batch:

update SY00500 established BCHSTTUS= where by BACHNUMB = ‘JULYINVOICES04’

Happy querying! If you want us to do the job – give us a get in touch with 1-866-528-0577! [email protected]