DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

Archives

No Posting Date available for SOP Invoices?

I was reviewing the eConnect help file to try and find the Posting Date for a SOP Invoice transaction, and I don't see a posting date field.

Seems odd. Is it buried somewhere else?

Other users are currently using Microsoft Dynamics GP. You cannot update tables while anyone is using Microsoft Dynamics GP

Grrr

Other users are currently using Microsoft Dynamics GP. You cannot update tables while anyone is using Microsoft Dynamics GP

I get this error trying to upgrade Dynamics GP. It then forces me to exit via the Cancel button

The answer wasn't too hard...

All of the following tables should be empty when no one is in GP:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

Grrr. I've battled this little winner for a while, at different clients. To cause it, you write code that loops through a data set of some sort and sends one transaction at a time to Dynamics GP using eConnect. After 100 sends, you get a crash with the above error.

I thought the error was Connection Pooling, so I turned it on with something like this:

Pooling=true;Max Pool Size=100;Min Pool Size=5 ;Data Source=...

However, all these settings are the default so it really shouldn't have made any difference. And it didn't.

To be safe, I made sure that my code was disposing of the objects created like this:

Dim eConnObject As New eConnectMethods

            eConnObject.eConnect_EntryPoint(cnString(server, db), EnumTypes.ConnectionStringType.SqlClient, Doc, EnumTypes.SchemaValidationType.None)
            eConnObject.Dispose()
            eConnObject = Nothing

Sop Integration Freight Calculation Error

Hello!
I'm working with econnect 10, writing an integration for a sop
invoice. So far, the integration goes well, but I have an issue when
the invoice includes a freight amount.
I set the CREATETAXES = 1 and CREATEDIST = 1 and altough the taxes
calculate fine, the account distributions doesn't.

I send the information this way:
ivcHeader.FREIGTBLE = 0
ivcHeader.FRTSCHID = ivcHeader.TAXSCHID
ivcHeader.FREIGHT = FREIGHT_AMOUNT

But, in distributions, the account for FREIGHT doesn't calculate the
tax. I try to send the FRTTXAMT, but I get the error 866: Cannot pass
in tax and markdown amounts when creating taxes on the fly

Even if I remove the flag CREATETAXES, it doesn't calculate well. I compare the invoice created with econnect and other from GP, and everything is the same, except for the BKTFRTAM field. Is there any way to populate it?

Here's the XML code:

<?xml version="1.0" encoding="utf-8" ?>

<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<SOPTransactionType>

<taSopLotAuto_Items>

<taSopLotAuto>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>D000040411</SOPNUMBE>

<LNITMSEQ>16384</LNITMSEQ>

<ITEMNMBR>YR16943</ITEMNMBR>

<LOCNCODE>YR VENTA</LOCNCODE>

<QUANTITY>1</QUANTITY>

<LOTNUMBR>R822</LOTNUMBR>

<EXPNDATE>31/12/2020</EXPNDATE>

</taSopLotAuto>

<taSopLotAuto>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>D000040411</SOPNUMBE>

<LNITMSEQ>32768</LNITMSEQ>

<ITEMNMBR>YR04076</ITEMNMBR>

<LOCNCODE>YR VENTA</LOCNCODE>

<QUANTITY>1</QUANTITY>

<LOTNUMBR>02D8</LOTNUMBR>

<EXPNDATE>01/03/2011</EXPNDATE>

</taSopLotAuto>

<taSopLotAuto xsi:nil="true"/>

</taSopLotAuto_Items>

<taSopLineIvcInsert_Items>

<taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>D000040411</SOPNUMBE>

<CUSTNMBR>X01078</CUSTNMBR>

<DOCDATE>09/06/2010</DOCDATE>

<LOCNCODE>YR VENTA</LOCNCODE>

<ITEMNMBR>YR16943</ITEMNMBR>

<UNITPRCE>95.00000</UNITPRCE>

<XTNDPRCE>95.00000</XTNDPRCE>

<QUANTITY>1</QUANTITY>

<LNITMSEQ>16384</LNITMSEQ>

<DOCID>D</DOCID>

<TAXSCHID>YR IVA INCLUIDO</TAXSCHID>

<AUTOALLOCATELOT>1</AUTOALLOCATELOT>

<UOFM>UNIDAD</UOFM>

</taSopLineIvcInsert>

<taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>D000040411</SOPNUMBE>

<CUSTNMBR>X01078</CUSTNMBR>

<DOCDATE>09/06/2010</DOCDATE>

<LOCNCODE>YR VENTA</LOCNCODE>

<ITEMNMBR>YR04076</ITEMNMBR>

<UNITPRCE>140.00000</UNITPRCE>

<XTNDPRCE>140.00000</XTNDPRCE>

<QUANTITY>1</QUANTITY>

<LNITMSEQ>32768</LNITMSEQ>

<DOCID>D</DOCID>

<TAXSCHID>YR IVA INCLUIDO</TAXSCHID>

<AUTOALLOCATELOT>1</AUTOALLOCATELOT>

<UOFM>UNIDAD</UOFM>

</taSopLineIvcInsert>

</taSopLineIvcInsert_Items>

<taCreateSopPaymentInsertRecord_Items>

<taCreateSopPaymentInsertRecord xsi:nil="true"/>

</taCreateSopPaymentInsertRecord_Items>

<taSopHdrIvcInsert>

<SOPTYPE>3</SOPTYPE>

<DOCID>D</DOCID>

<SOPNUMBE>D000040411</SOPNUMBE>

<LOCNCODE>YR VENTA</LOCNCODE>

<DOCDATE>09/06/2010</DOCDATE>

<FREIGHT>30</FREIGHT>

<CUSTNMBR>X01078</CUSTNMBR>

<SUBTOTAL>235</SUBTOTAL>

<USER2ENT>admin</USER2ENT>

<BACHNUMB>CT09/06/2010</BACHNUMB>

<PRSTADCD>ENVIO</PRSTADCD>

<CREATECOMM>1</CREATECOMM>

<CREATETAXES>1</CREATETAXES>

<DEFTAXSCHDS>1</DEFTAXSCHDS>

<DEFPRICING>1</DEFPRICING>

</taSopHdrIvcInsert>

</SOPTransactionType>

</eConnect>

Hope someone could help me on this :)

Could not load external 'PEOpenEngine@CRPE32.DLL'.

In Dynamics GP 9 with a VS Tools install:

Unhandled script exception:
Could not load external 'PEOpenEngine@CRPE32.DLL'.
EXCEPTION_CLASS_SCRIPT_MEMORY
SCRIPT_CMD_EXTERN

and it's brother

Could not load external 'PECloseEngine@CRPE32.DLL'.

The system cannot find the file specified

This is going to be a long entry, because I want to add all the error messages that I got.

My environment is a newly installed Visual Studio 2010

The initial error was ‘gpMods.dll: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information'

*** Assembly Binder Log Entry (6/14/2010 @ 3:27:30 PM) ***

The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.

Assembly manager loaded from: C:\WINNT\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable C:\Program Files\Microsoft Dynamics\GP\Dynamics.exe
--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = NORTHGABRICK\sgray
LOG: DisplayName = Interop.Dynamics, Version=9.0.63.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
(Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft Dynamics/GP/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = NULL
Calling assembly : Microsoft.Dexterity.Bridge, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.
===
LOG: Start binding of native image Interop.Dynamics, Version=9.0.63.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.
WRN: No matching native image found.

===================================================================

*** Assembly Binder Log Entry (6/14/2010 @ 3:24:51 PM) ***

The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.

Assembly manager loaded from: C:\WINNT\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable C:\Program Files\Microsoft Dynamics\GP\Dynamics.exe
--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = NORTHGABRICK\sgray
LOG: DisplayName = Microsoft.Dexterity.Shell.UI, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
(Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft Dynamics/GP/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = NULL
Calling assembly : gpModsTemp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null.
===
WRN: Native image will not be probed in LoadFrom context. Native image will only be probed in default load context, like with Assembly.Load().
LOG: Start binding of native image Microsoft.Dexterity.Shell.UI, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.
LOG: IL assembly loaded from C:\Program Files\Microsoft Dynamics\GP\Microsoft.Dexterity.Shell.UI.dll.
WRN: No matching native image found.
LOG: Bind to native image assembly did not succeed. Use IL image.

===================================================================

*** Assembly Binder Log Entry (6/14/2010 @ 3:21:51 PM) ***

The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.

Assembly manager loaded from: C:\WINNT\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable C:\Program Files\Microsoft Dynamics\GP\Dynamics.exe
--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = NORTHGABRICK\sgray
LOG: DisplayName = msvcm80, Version=8.0.50727.4027, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
(Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft Dynamics/GP/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = NULL
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: C:\Program Files\Microsoft Dynamics\GP\Dynamics.config
LOG: Using machine configuration file from C:\WINNT\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: msvcm80, Version=8.0.50727.4027, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL file:///C:/Program Files/Microsoft Dynamics/GP/msvcm80.DLL.
LOG: Attempting download of new URL file:///C:/Program Files/Microsoft Dynamics/GP/msvcm80/msvcm80.DLL.
LOG: Attempting download of new URL file:///C:/Program Files/Microsoft Dynamics/GP/msvcm80.EXE.
LOG: Attempting download of new URL file:///C:/Program Files/Microsoft Dynamics/GP/msvcm80/msvcm80.EXE.
LOG: All probing URLs attempted and failed.

Error Number 799: Tax Table detail does not equal the tax amount

I hope someone can help with this but I can't seem to save an Invoice in Sales Order. I am using GP v10. We also have AvaTax installed.

The code I have is to create the XML string is below. What am I missing?

Thanks

Christel

private string eConnectOrderXml(short isUpdate)

{

int orderItemCount = this.Items.Count;

short docType = this.DocType;

string docID = (docType == 3) ? "STDINV" : "STDRTN";

// Create a datetime format object

DateTimeFormatInfo dateFormat = new CultureInfo("en-US").DateTimeFormat;

// Create an array that can hold two taSopLineIvcInsert_ItemsTaSopLineIvcInsert objects

taSopLineIvcInsert_ItemsTaSopLineIvcInsert[] LineItems = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert[orderItemCount];

for (int i = 0; i < orderItemCount; i++)

{

// Create a taSopLineIvcInsert_ItemsTaSopLineIvcInsert object and populate its fields

taSopLineIvcInsert_ItemsTaSopLineIvcInsert salesLine = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert();OrderItem item = this.Items[i];

salesLine.ADDRESS1 = ReadProperty(Address1Property);

salesLine.ADDRESS2 = ReadProperty(Address2Property);

salesLine.ADDRESS3 = ReadProperty(Address3Property);

salesLine.CITY = ReadProperty(CityProperty);

salesLine.STATE = ReadProperty(StateProperty);

salesLine.ZIPCODE = ReadProperty(ZipProperty);

salesLine.COUNTRY = ReadProperty(CountryProperty);

salesLine.CNTCPRSN = ReadProperty(ShipToContactProperty);

salesLine.ShipToName = ReadProperty(ShipToNameProperty);

salesLine.CUSTNMBR = ReadProperty(CustomerIdProperty).ToString();

salesLine.SOPNUMBE = ReadProperty(OrderIdProperty).ToString();

salesLine.CITY = ReadProperty(CityProperty);

salesLine.SOPTYPE = docType;

salesLine.DOCID = docID;

salesLine.QUANTITY = item.Quantity;

if (docType == 4)

salesLine.QTYRTRND = item.Quantity;

salesLine.ITEMNMBR = item.Code;

salesLine.ITEMDESC = item.Description;

salesLine.UNITPRCE = item.UnitPrice;

salesLine.XTNDPRCE = item.ExtendedPrice;

salesLine.LOCNCODE = ReadProperty(LocationProperty);

salesLine.DOCDATE = ReadProperty(InvoiceDateProperty).ToString();

salesLine.ReqShipDate = ReadProperty(DueDateProperty).ToShortDateString();

salesLine.ACTLSHIP = ReadProperty(DueDateProperty).ToShortDateString();

salesLine.FUFILDAT = ReadProperty(DueDateProperty).ToShortDateString();

salesLine.TAXSCHID = ReadProperty(TaxScheduleProperty);

salesLine.LNITMSEQ = i;

salesLine.UpdateIfExists = isUpdate;

// Add the SOP line item object to the array

LineItems[i] = salesLine;

}

//Tax

taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert[] LineTaxes = new taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert[1];taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert LineTax = new taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert();

LineTax.SOPTYPE = docType;

LineTax.SOPNUMBE = ReadProperty(OrderIdProperty).ToString();

LineTax.CUSTNMBR = ReadProperty(CustomerIdProperty).ToString();

LineTax.LNITMSEQ = 0;

LineTax.TXDTOTTX = ReadProperty(TaxProperty);

LineTax.TDTTXSLS = ReadProperty(TaxProperty);

LineTax.TDTTXSLSSpecified = true;LineTax.TXDTOTTXSpecified = true;

LineTax.TAXDTLID = ReadProperty(TaxScheduleProperty);

LineTaxes[0] = LineTax;

// Create a taSopHdrIvcInsert object and populate its fields

taSopHdrIvcInsert salesHdr = new taSopHdrIvcInsert();

salesHdr.SOPNUMBE = ReadProperty(OrderIdProperty);

salesHdr.SOPTYPE = docType;

salesHdr.DOCID = docID;

salesHdr.BACHNUMB = ReadProperty(BatchIDProperty);

salesHdr.LOCNCODE = ReadProperty(LocationProperty);

salesHdr.DOCDATE = ReadProperty(InvoiceDateProperty).ToShortDateString();

if (docType == 3)

{ salesHdr.ORDRDATE = ReadProperty(OrderDateProperty).ToShortDateString(); }

salesHdr.CUSTNMBR = ReadProperty(CustomerIdProperty).ToString();

salesHdr.PRSTADCD = "PRIMARY"; // ReadProperty(ShipToAddressCodeProperty);

salesHdr.ShipToName = ReadProperty(ShipToNameProperty);

salesHdr.ADDRESS1 = ReadProperty(Address1Property);

salesHdr.ADDRESS2 = ReadProperty(Address2Property);

salesHdr.ADDRESS3 = ReadProperty(Address3Property);

salesHdr.CNTCPRSN = ReadProperty(ShipToContactProperty);

salesHdr.CITY = ReadProperty(CityProperty);

salesHdr.STATE = ReadProperty(StateProperty);

salesHdr.ZIPCODE = ReadProperty(ZipProperty);

salesHdr.COUNTRY = ReadProperty(CountryProperty);

salesHdr.SUBTOTAL = ReadProperty(SubTotalProperty);

salesHdr.DOCAMNT = ReadProperty(TotalProperty);

salesHdr.TAXAMNT = ReadProperty(TaxProperty);

salesHdr.FRTTXAMT = 0;

salesHdr.MSCTXAMT = 0;

salesHdr.TRDISAMT = ReadProperty(DiscountProperty);

salesHdr.COMMENT_1 = ReadProperty(NotesProperty);

salesHdr.USER2ENT = Csla.ApplicationContext.User.Identity.Name;

salesHdr.ReqShipDate = ReadProperty(DueDateProperty).ToShortDateString();

salesHdr.TAXSCHID = ReadProperty(TaxScheduleProperty);

salesHdr.USINGHEADERLEVELTAXES = 1;

salesHdr.CREATETAXES = 0;

//salesHdr.CREATEDIST = 1;

salesHdr.UpdateExisting = isUpdate;

taSopUpdateCreateProcessHold ProcessHold = new taSopUpdateCreateProcessHold();

//credit orders shouldn't be put on hold

if (!ReadProperty(isNewCreditOrderProperty))

{

if (isUpdate == 0) //inserting a record

{

ProcessHold.SOPTYPE = docType;

ProcessHold.SOPNUMBE = ReadProperty(OrderIdProperty);

ProcessHold.PRCHLDID = "Hold"; //the id for the process hold

ProcessHold.POSTPHOL = 1;

ProcessHold.DELETE1 = 0; //add the hold

ProcessHold.UpdateIfExistsMaster = 0;

ProcessHold.UpdateSopIfExists = 1;

}

taSopUserDefined UserDef = new taSopUserDefined();

UserDef.SOPNUMBE = ReadProperty(OrderIdProperty);

UserDef.SOPTYPE = docType;

UserDef.USERDEF2 = ReadProperty(ReferenceProperty);

UserDef.USRDEF03 = ReadProperty(OrderTypeProperty).ToString();

// Create a eConnect SOPTransactionType schema object

SOPTransactionType salesOrder = new SOPTransactionType();

// Populate the schema object with the SOP header and SOP line item objects

salesOrder.taSopLineIvcInsert_Items = LineItems;

salesOrder.taSopLineIvcTaxInsert_Items = LineTaxes;

salesOrder.taSopUserDefined = UserDef;

salesOrder.taSopHdrIvcInsert = salesHdr;

salesOrder.taSopHdrIvcInsert.UpdateExisting = isUpdate;

if (isUpdate == 0 && !ReadProperty(isNewCreditOrderProperty)) //inserting a record that isn't a Credit Order

salesOrder.taSopUpdateCreateProcessHold = ProcessHold;

// Create an array that holds SOPTransactionType objects

// Populate the array with the SOPTransactionType schema object

SOPTransactionType[] MySopTransactionType = { salesOrder };

// Create an eConnect XML document object and populate it

// with the SOPTransactionType schema object

eConnectType eConnect = new eConnectType();

eConnect.SOPTransactionType = MySopTransactionType;

return Utility.SerializeToString(eConnect);

}

Using an Array/Collection/Dictionary to hold items in Dynamics VBA

I recently had to create an object to hold the items that were marked as 'void' behind the Payables Void Open Payables form. I started with an array to hold the voucher numbers... but that was cumbersome. Next I declared a Collection. That worked... but the Dictionary object had better properties and methods, and the code was simpler.

Billing Entry Integration

Hello all, I am writing an eConnect 9 piece to automate the GP Billing Entry screen (Transactions > Projects > Billing > Billing Entry). I'm having a hard time figuring out which eConnect schema to use. Can anyone help? TIA

Hi,

My name is Somesh Sahu and I am facing an issue while trying to connect GP company database using logged-in user credentials.

I have developed a new window using GP Add-in and it is accessed from Customer Management -> Additional menu. There is a new User table which stores some additional information for Customers. There is a lookup button on this new window for selecting a Customer. When a normal User say 'abc' clicks this look-up button, it opens the 'Customer & Prospects' window but when User selects a Customer and click select button, it displays a message 'Login failed for user 'abc'' as this new Custom table is queried to fetch the data. This message also appears while accessing this new window as there are some 'Select' queries on form load. I have read the articles on this problem and seems that Normal user is not able to connect SQL Server because the password is encrypted in GP which is used in the connection string to connect to database.

dbConn.ConnectionString = "Password=" + userPwd + ";Persist Security Info=True;User ID=" + userID + ";Initial Catalog=" + databaseName + ";Data Source=" + serverName + ";Connection Timeout=" + connectionTimeout;

where userID = Dynamics.Globals.UserId.Value and userPwd = Dynamics.Globals.SqlPassword.Value

This problem does not occur when User is SQL Server System Administrator i.e. 'sa' as GP does not store its encrypted password.

As this new window will be accessed by normal users, they should be able to connect SQL Server and access the new tables

Please let me know the resolution of this issue.

Thanks in advance.

Regards,

Somesh Sahu

Extending business logic - Add XML nodes to an existing schema

Has anyone added their own custom xml node to an eConnect schema? I created my own xml node with two elements in which I want to update a field in the SOP10100 table that is not part of the taSopHdrIvcInsert schema. I followed the directions and named the custom schema the same as the stored procedure I created and the two elements are the same as the two parameters in the sp. But it's not working and I suspect it's because in my stored proc I do an update and since eConnect wraps the whole SOPTransactionType into a sql transaction - the record has not been commited yet and therefore does not exist in the SOP10100 table so updates will not work!

Anyone tried to do their own custom xml nodes with updates?