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

OLE DB provider "SQLNCLI11" for linked server "[my server]" returned message "Query timeout expired".

I'm trying to run a query on a linked server and it's timing out and giving me

OLE DB provider "SQLNCLI11" for linked server "[my server]" returned message "Query timeout expired".

I looked at the linked server properties and the Connection Timeout setting is 0, which usually means 'unlimited'

Solution below.

Create (and restore a database to) a TEST server for app testing

If you're just testing GP code, you can use a TEST database. No problem.

But in this case there are other linked dbs (documents, security) and I need to test on a separate server.

So, this is code that will backup up SQL database to a separate server.

taPMTransactionInsert

Full text below. This is from GP2018, NOT SUITABLE TO USE IN PRODUCTION, but it is useful for finding error messages

taPMDistributionCreate

Full text below. This is from GP2018, NOT SUITABLE TO USE IN PRODUCTION, but it is useful for finding error messages

How to decrypt GP (or any, really) Stored Procedures

Short article on how to decrypt GP stored procedures (for free)

At least one input variable contains a null value in the taPMDistributionCreate

I have what I thought was the simplest of payable with distributions but I'm getting the below error. There is a fix for the same error here but it doesn't apply to me.

Microsoft.Dynamics.GP.eConnect.eConnectException: 'Sql procedure error codes returned:

Error Number = 349 Stored Procedure= taPMTransactionInsert Error Description = Unable to update/create distributions
Error Number = 700 Stored Procedure= taPMDistributionCreate Error Description = At least one input variable contains a null value in the taPMDistributionCreate
Node Identifier Parameters: taPMDistributionCreate
DOCTYPE = 1
VCHNUMWK = 00000000227458635
VENDORID = TheVendorId

<taPMTransactionInsert>
<BACHNUMB>10027</BACHNUMB>
<VCHNUMWK>00000000227458635</VCHNUMWK>
<VENDORID>TheVendorId</VENDORID>
<DOCNUMBR>10027</DOCNUMBR>
<DOCTYPE>1</DOCTYPE>
<DOCAMNT>151.430000</DOCAMNT>
<DOCDATE>2023-07-15</DOCDATE>
<PRCHAMNT>151.430000</PRCHAMNT>
<CHRGAMNT>151.430000</CHRGAMNT>
<CREATEDIST>0</CREATEDIST>
</taPMTransactionInsert>
'

The full XML is as follows:

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

<PMTransactionType>

<eConnectProcessInfo xsi:nil="true" />

<taRequesterTrxDisabler_Items xsi:nil="true" />

<taUpdateCreateVendorRcd xsi:nil="true" />

<taCreateVendorAddress_Items xsi:nil="true" />

<taPMTransactionTaxInsert_Items xsi:nil="true" />

<taPMDistribution_Items>

<taPMDistribution>

<DOCTYPE>1</DOCTYPE>

<VCHRNMBR>00000000227458635</VCHRNMBR>

<VENDORID>TheVendorId</VENDORID>

<DISTTYPE>6</DISTTYPE>

<ACTNUMST>40-033-51000</ACTNUMST>

<DEBITAMT>151.430000</DEBITAMT>

</taPMDistribution>

<taPMDistribution>

<DOCTYPE>1</DOCTYPE>

<VCHRNMBR>00000000227458635</VCHRNMBR>

<VENDORID>TheVendorId</VENDORID>

<DISTTYPE>2</DISTTYPE>

<ACTNUMST>00-000-20000</ACTNUMST>

<CRDTAMNT>151.430000</CRDTAMNT>

</taPMDistribution>

</taPMDistribution_Items>

<taAnalyticsDistribution_Items xsi:nil="true" />

<taPMTransactionInsert>

<BACHNUMB>10027</BACHNUMB>

<VCHNUMWK>00000000227458635</VCHNUMWK>

<VENDORID>TheVendorId</VENDORID>

<DOCNUMBR>10027</DOCNUMBR>

<DOCTYPE>1</DOCTYPE>

<DOCAMNT>151.430000</DOCAMNT>

<DOCDATE>2023-07-15</DOCDATE>

<PRCHAMNT>151.430000</PRCHAMNT>

<CHRGAMNT>151.430000</CHRGAMNT>

<CREATEDIST>0</CREATEDIST>

</taPMTransactionInsert>

<taMdaUpdate_Items xsi:nil="true" />

</PMTransactionType>

</eConnect>

Is there something obvious I am missing? I assume that the problem is some value on one of the distributions but in all the documentation I can find this meets the required elements. Maybe I shouldn't be specifying the batch number? I'm also using our own document number, should I do a look up like I'm doing for the voucher number (taGetPMNextVoucherNumber)? The rest seems straight-forward.

Thanks,
Brian

document number not unique from GetNextIVNumber on bin to bin transfer

Hello, I am doing a bin to bin transfer (taIVMultibinBinToBinTransfer) and using the following to get the next document number.

nextDocNo = getNextDocNumbers.GetNextIVNumber(IncrementDecrement.Increment, IVDocType.IVTransfer, ConnectionString);

Not all the time, we occasionally get an issue where eConnect errors out telling that the document number has already been used. Also, we are noticing that the record comes with a TRF as the prefix instead of BTR as setup in the "Next Document Number" in GP. Is there a different call to get a next document number? or is there a stored procedure we can use instead of calling the API call in eConnect?

I notice in the table IV40100, that there is a field Next_Bin_XFer_Doc_Number. Would it be possible to just increment from there manually?

SQL Latest Collection Note

Greetings I need to SQL Query the CN00100 Table (Collection Module - Notes) and find the latest date that a customer note has been created.

This query works (kind of)

select CN.CUSTNMBR, CN.DATE1
from CN00100 CN
where CN.DATE1 = (select max(CN2.DATE1)
from CN00100 CN2
where CN.CUSTNMBR = CN2.CUSTNMBR
)

the issue is if the customer has multiple notes on the same day - query returns multiple rows (I only want one row).

How would I incorporate a TOP 1 or do I use DEX_ROW_ID instead of DATE1? Your thoughts?

Here's a sample of the dataset. YGLPR93752 has 3 notes on May 15th - I want a single row returned instead of three rows.