DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Archives

 
Auto posting of invoices or auto transfer of orders

Our client is evaluating the use of eConnect to help integration GP with web-based order management/warehouse management system. In this web-based system customer will be placing their own orders (and periodically checking the status of their orders), warehouse personnel (multiple warehouses around the world) will be printing pick tickets, fullfilling orders and the system will be auto-generating invoices.

We are considering an integration where the initial order gets moved into GP, the order is fulfilled and then the order is transferred to an invoice.

I am certain that eConnect can pull in an order and it can pull in the invoice but can it duplicate the transfer process (such that the order moves to history)?

Thanks in advance for your help.

 
Exporting SQL Data to a fixed width file

From a recent email: 

Our client needs to extract data from a variety of Dynamics GP payroll tables into a couple of files to send to their new 401 plan administrator.  The required files need to be in a fixed-width file format.  I have been thinking about possible tools that could do this job, such as SmartLists, SmartLists Builder, GP Report Writer, or perhaps a SQL view.

Your thoughts?

 

I’ve done this in the past several ways. Crystal would be the easiest – create one field that that has all the data, powered by a stored procedure. Then export the report to a text file.

 Another possibility would be just to export the data directly from a stored procedure, either via cut and paste from the query window or by running a task that will create a text file; but those options are more techincal. If you create a Crystal report, any end user can do it.

 
Primary key error when inserting a line into GL10001

From a recent email:

Have you ever inserted data into the gl10001 table?  i can't enter multiple rows for each distribution because i keep getting violation of primary key error on the jrnentry field.  i even tried entering a unique value for that field just so i could insert and then tried to update them all to the same and i get the same error.  do you know a way around this?

Answer:

Dynamics GP doesn't use referential integrity, but just for good form enter a line into the GL10000 table first, then enter lines into the GL10001. Be sure that you increment the SQNCLINE field for each line.

 
Error message when you start Microsoft Dynamics GP: "Dynamics

SYMPTOMS

When you start Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains, you may receive the following error message: Dynamics SQL Database has not been setup for this version.

CAUSE

 

Cause 1

This problem occurs because the values in the tables in the DYNAMICS database do not correspond to the version of Microsoft Dynamics GP or of Microsoft Business Solutions - Great Plains that you are using. See Resolution 1 in the "Resolution" section.

Cause 2

This problem occurs because the Client installation of Microsoft Dynamics GP was installed instead of the Server installation. You must install the Server installation before you can update the databases in Utilities. See Resolution 2 in the "Resolution" section.

RESOLUTION

 

Resolution 1

1. Make sure that you have a good backup of the DYNAMICS database.
2. Use Query Analyzer or SQL Management Studio to run the following script against the DYNAMICS database.
 SELECT * FROM DYNAMICS..DU000020
SELECT * FROM DYNAMICS..DB_Upgrade
Notes
• In the results for the DU000020 table, verify that the values in the following columns correspond to the version of Microsoft Dynamics GP or of Microsoft Great Plains that you are using:
• The versionMajor column
• The versionMinor column
For example, if you are using Microsoft Dynamics GP 9.0, you should see the following values:
• The versionMajor column: 9
• The versionMinor column: 0
• In the DB_Upgrade table results, verify that the values in the following columns correspond to the version of Microsoft Dynamics GP or of Microsoft Great Plains that you are using:
• The db_verMajor column
• The db_verMinor column
• The db_verOldMajor column
• The db_verOldMinor column
For example, if you are using Microsoft Dynamics GP 9.0, you should see the following values:
• The db_verMajor column: 9
• The db_verMinor column: 0
• The db_verOldMajor column: 9
• The db_verOldMinor column: 0
3. If the values in the columns that are listed in step 2 do not correspond to the version of Microsoft Dynamics GP that you are using, run the following scripts in Query Analyzer or in SQL Server Management Studio.

Script 1

 
update DYNAMICS..DU000020 
  set versionMajor = VERSIONMAJOR, versionMinor = VERSIONMINOR
  where companyID = COMPANYID
Notes
• Replace the VERSIONMAJOR placeholder and the VERSIONMINOR placeholder with the numbers of the version that you are currently using. For example, if you are using Microsoft Dynamics GP 9.0, the VERSIONMAJOR placeholder should be 9 and the VERSIONMINOR placeholder should be 0.
• Replace the COMPANYID placeholder with the company ID of the company that has the incorrect version information. To determine the company ID, run the following statement.
 
SELECT * FROM DYNAMICS..SY01500

Script 2

 
update DYNAMICS..DB_Upgrade 
  set db_verMajor = VERSIONMAJOR, db_verMinor = VERSIONMINOR
  where db_name = DATABASENAME
Notes
• Replace the VERSIONMAJOR placeholder and the VERSIONMINOR placeholder with the numbers of the version that you are currently using. For example, if you are using Microsoft Dynamics GP 9.0, the VERSIONMAJOR placeholder should be 9 and the VERSIONMINOR placeholder should be 0.
• Replace the DATABASENAME placeholder with the name of the database for the company.

Resolution 2

 

Microsoft Dynamics GP

If you are using Microsoft Dynamics GP, remove the Client installation and reinstall the Server installation. To do this, follow these steps:

1. Click Start, click Control Panel, and then click Add or Remove Programs.
2. On the list, select Microsoft Dynamics GP, and then click Change.
3. Click Remove to remove the Client installation of Microsoft Dynamics GP.
4. When you reinstall Microsoft Dynamics GP, click Server in the Installation Type window, and then click Next.
5. Finish the installation.

Microsoft Business Solutions - Great Plains

If you are using Microsoft Business Solutions - Great Plains, convert the Client installation to a Server installation. To do this, follow these steps:

1. Start the installation by using CD 1 of the Microsoft Business Solutions - Great Plains installation CD set. To do this, double-click the CDSetup.exe file.
2. Click Install Great Plains, and then accept the same default settings that you used for the Client installation except for the following settings:
a. At the Select to Install Server or Client window, click Server and Client Installation.
b. At the Select Great Plains Components window, click SQL Server Objects.
3. Finish the installation.
Creating invoices using econnect

I'm trying to create an invoice using econnect but before I jump into it I have a few queries that I can't find the answer to in the user documentation.

1. Is there a specific order that you need to build the xml up. i.e.

taPMTransactionInsert

taPMTransactionTaxInsert

taPMDistribution

2. Can you build up an XML file with more than one invoice? Or is it one XML file per invoice?

3. If answer to question 2 is that you can have more than one invoice per XML file then how do you re-read in the XML and append all the details for subsequent invoices.

Regards

Glyn
Checking Inventory Levels of Product....

I have a eConnect vb 2005 application which takes products shipped from a shipping program and then puts invoices into great plains based on that info. My problem is that I need to check sufficient inventory levels before putting in a invoice which may cause a shortage...

 Accounting is sometimes late getting new inventory in the system, therefore a shipped product could come through with no inventory to cover it, then when the inventory is added after the invoice the costing gets screwed up...

 QUESTION: How can I check inventory levels of a product before integrating the invoice for the product?

 Doug Crowe
Error integrating a invoice...any ideas why?

pasted below are the XML document I am trying to integrate and below that is pasted the error I get kicked back, any ideas what would cause this error because apparently its something company specific, because my great plains partner, says it validates for them perfectly...

Error Number = 735 Stored Procedure taSopHdrIvcInsert Error Description = Discount Available amount cannot be greater then document amount
 Node Identifier Parameters: taSopHdrIvcInsert
SOPNUMBE = TMP003115
SOPTYPE = 3
Related Error Code Parameters for Node : taSopHdrIvcInsert
DISAVAMT = Note: This parameter was not passed in, no value for the parameter will be returned.
DOCAMNT = Note: This parameter was not passed in, no value for the parameter will be returned.

<eConnect xmlns:dt="urn:schemas-microsoft-com:datatypes">

       <SOPTransactionType>

              <taSopLineIvcInsert>

                     <SOPTYPE>3</SOPTYPE>

                     <SOPNUMBE>TMP003115</SOPNUMBE>

                     <CUSTNMBR>122</CUSTNMBR>

                     <DOCDATE>9/25/2007</DOCDATE>

                     <ACTLSHIP>9/25/2007</ACTLSHIP>

                     <ITEMNMBR>SS-1</ITEMNMBR>

                     <UOFM>GALLON</UOFM>

                     <UNITPRCE>2.45</UNITPRCE>

                     <QUANTITY>138</QUANTITY>

                     <CMMTTEXT>BOL #:0000000952</CMMTTEXT>

                     <USRDEFND3>0000000952</USRDEFND3>

                     <ITEMDESC>SS-1 Emulsion Asphalt</ITEMDESC>

                     <QTYRTRND>0</QTYRTRND>

                     <DROPSHIP>0</DROPSHIP>

                     <QtyShrtOpt>5</QtyShrtOpt>

                     <DEFEXTPRICE>1</DEFEXTPRICE>

              </taSopLineIvcInsert>

              <taSopLineIvcInsert>

                     <SOPTYPE>3</SOPTYPE>

                     <SOPNUMBE>TMP003115</SOPNUMBE>

                     <CUSTNMBR>122</CUSTNMBR>

                     <DOCDATE>9/25/2007</DOCDATE>

                     <ACTLSHIP>9/25/2007</ACTLSHIP>

                     <ITEMNMBR>WQG</ITEMNMBR>

                     <UOFM>GALLON</UOFM>

                     <UNITPRCE>0.002</UNITPRCE>

                     <QUANTITY>138</QUANTITY>

                     <CMMTTEXT></CMMTTEXT>

                     <USRDEFND3>0000000952</USRDEFND3>

                     <ITEMDESC>Water Quality Tax</ITEMDESC>

                     <QTYRTRND>0</QTYRTRND>

                     <DROPSHIP>0</DROPSHIP>

                     <QtyShrtOpt>5</QtyShrtOpt>

                     <DEFEXTPRICE>1</DEFEXTPRICE>

              </taSopLineIvcInsert>

              <taSopHdrIvcInsert>

                     <SOPTYPE>3</SOPTYPE>

                     <ORDRDATE>9/25/2007</ORDRDATE>

                     <DOCID>STANDARD</DOCID>

                     <DOCDATE>9/25/2007</DOCDATE>

                     <SOPNUMBE>TMP003115</SOPNUMBE>

                     <ORIGTYPE>0</ORIGTYPE>

                     <ReqShipDate>9/25/2007</ReqShipDate>

                     <FREIGHT>0</FREIGHT>

                     <MISCAMNT>0</MISCAMNT>

                     <TRDISAMT>0</TRDISAMT>

                     <DISTKNAM>0</DISTKNAM>

                     <MRKDNAMT>0</MRKDNAMT>

                     <CUSTNMBR>122</CUSTNMBR>

                     <CSTPONBR></CSTPONBR>

                     <DEFPRICING>1</DEFPRICING>

                     <CREATETAXES>1</CREATETAXES>

                     <DEFTAXSCHDS>1</DEFTAXSCHDS>

                     <BACHNUMB>DCROWE_9252007</BACHNUMB>

                     <PRSTADCD>PRIMARY</PRSTADCD>

                     <DUEDATE>10/25/2007</DUEDATE>

                     <DISCDATE>10/15/2007</DISCDATE>

                     <DSCDLRAM>6.76</DSCDLRAM>

              </taSopHdrIvcInsert>

       </SOPTransactionType>

</eConnect>

 

 

 

 

Thanks

 

Doug Crowe

 
Automatically posting transactions in GP

Hi,

I have been doing research on this subject. We are hooking up GP with ecommerce and would like to have posing done automatically and but still keeping it manually in GP.
Is there a way to do it? I heard that there is an order of procedures to call to have transactions posted.
Does any one have a clue of those procedures?

Thank you in advance.

Tony
eConnect User Defined fields

Friends:
I see that every eConnect schema has 'user defined' fields... but I don't see how to use them. A search of the help doc for 'user defined' turns up nothing but the fields themselvs. Can anyone help?
I'm an experienced developer, be as technical as you like.
Thanks!
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Gold Star Locksmith
3