“ta” eConnect stored procedures & upgrades a thought…

SQL Server Tree View Showing Pre and Post Procedures

eConnect presents a “pre” and “post” procedure for the various document transactions. This allows custom SQL scripts to be ran before and after eConnect transactions.

Beware, as anyone who works with eConnect will tell you, these procedures are DROPPED during version upgrades. This understandably makes developers wary of using them, for fear of problems after upgrades.

My current practice with these procedures is to have a single line call to another stored procedure that acts as a proxy. The proxy contains all the script I need to run. This means if someone ever forgets to take a copy before upgrade, it is very simple to restore without having lost any IP from the body of the script as it is only the pointer to the proxy that is lost.

I understand why for simplicity of upgrade scripts this is done this way. It also forces the developer to consider that the calling parameters may have changed if the table definition changes. However in every one of the scripts I have like this, they only consume the primary keys or other primary fields that are unlikely to grow or change data type.

It would be great to have a convention where anything between tags named something like this,

<!—User Script start –>

and

<!—User Script end –>

is stripped out by the upgrade script and injected into the new version of the stored procedures when they are created. I’m tempted to put something together like this for ourselves to automate the upgrade process. I could also script a search for stored procedures containing theses tags to iterate though them all making the changes pre and post upgrade.

Perhaps the pre upgrade run of this could also store the parameter names and types to compare after upgrade and this raise a warning where they have changed.   

Solution to eConnect Requester SOP Transaction trigger not creating output records

eConnect uses a Document Requester system to allow database activity in GP to be tracked, say for the creation of sales orders. It also allows the database to be queried via the eConnect API, without any logging to output tables.
Steve Endow has written up here on his blog, 8 year old bug in eConnect Requester SOP Transaction trigger an issue. Essentially the eConnect_Out table was not receiving any records when new sales orders were created, even though the  SO_Trans Insert trigger was enabled.

image

The issue boiled down to the auto-generated code for the trigger generated by the default requester that is “supplied with” eConnect, inappropriately making CUSTNMR a required conditional field on order record insert. Steve correctly identified that as the CUSTNMBR field is initially empty in the SQL table and is actually updated after the record is inserted. As the CUSTNMR is updated rather than inserted this is why the requester fails to insert a record in the output table. See his post for full details and a better explanation.

I’ve worked with eConnect requesters a for a long time and can offer a suggested way I would solve this issue.

eConnect provides a executable utility shown in the screenshot above, the Requester Setup Tool that allows administration of the provided output document types. I have the feeling that this utility did not exist when I started using eConnect (back many years ago), or perhaps I never noticed it, so I have always just gone right to table to change and create existing or new requesters. This puts me at an advantage for this issue as I know how requesters work.

The utility is actually merely manipulating rows in [dbo].[eConnect_Out_Setup] table. SQL triggers on this table do the clever task of creating other triggers against the relevant tables that we seek to monitor. Looking at the row for the SO_Trans record, we see the following in the setup record;

image

Note that CUSTNMBR has be defined by GP as a required by having CUSTNMBR in the REQUIRED1 field. This is why it is being set in the trigger as required and so why it fails to create the output record when a initial record is inserted into the database with no CUSTNMBR value.

Solution

To solve the problem we simply create a new output definition record that does not has a that "CUSTNMBR" valued in REQURIED1 the field. The design of eConnect is such that integrating applications can access the setup table directly to subscribe to what they need in GP.

To achieve this, script out the data from the original row setup row, give it a new alias name and remove the field in the REQUIRED1 value.

Insert this record into the [dbo].[eConnect_Out_Setup] table (in production the application can do this on install or start of the application). The insert causes a new trigger to be created against the sales table SOP10100, this time without that required field. Do a test, now creating a new sales order will correctly create a record in the output table.

Below is the row to insert using  Sales_Transaction_All as the requester name and SO_Trans_All as the alias (change these names to something that won't be used by any other application in real production deployment:

INSERT [dbo].[eConnect_Out_Setup] (
[QUEUEPATH]
,[QUEUE_ENABLED]
,[DOCTYPE]
,[INSERT_ENABLED]
,[UPDATE_ENABLED]
,[DELETE_ENABLED]
,[TABLENAME]
,[ALIAS]
,[MAIN]
,[PARENTLEVEL]
,[ORDERBY]
,[USERDEF1]
,[USERDEF2]
,[USERDEF3]
,[USERDEF4]
,[USERDEF5]
,[REQUIRED1]
,[INDEX1]
,[INDEX2]
,[INDEX3]
,[INDEX4]
,[INDEX5]
,[INDEX6]
,[INDEX7]
,[INDEX8]
,[INDEX9]
,[INDEX10]
,[INDEX11]
,[INDEX12]
,[INDEX13]
,[INDEX14]
,[INDEX15]
,[INDEXCNT]
,[TRIGGER1]
,[TRIGGER2]
,[TRIGGER3]
,[TRIGGER4]
,[TRIGGER5]
,[TRIGGER6]
,[TRIGGER7]
,[TRIGGER8]
,[TRIGGER9]
,[TRIGGER10]
,[TRIGGER11]
,[TRIGGER12]
,[TRIGGER13]
,[TRIGGER14]
,[TRIGGER15]
,[TRIGGERCNT]
,[JOINTABLE]
,[JOIN1]
,[JOIN2]
,[JOIN3]
,[JOIN4]
,[JOIN5]
,[JOIN6]
,[JOIN7]
,[JOIN8]
,[JOIN9]
,[JOIN10]
,[JOINTO1]
,[JOINTO2]
,[JOINTO3]
,[JOINTO4]
,[JOINTO5]
,[JOINTO6]
,[JOINTO7]
,[JOINTO8]
,[JOINTO9]
,[JOINTO10]
,[DATACNT]
,[DATA1]
,[DATA2]
,[DATA3]
,[DATA4]
,[DATA5]
,[DATA6]
,[DATA7]
,[DATA8]
,[DATA9]
,[DATA10]
,[DATA11]
,[DATA12]
,[DATA13]
,[DATA14]
,[DATA15]
,[DATA16]
,[DATA17]
,[DATA18]
,[DATA19]
,[DATA20]
,[DATA21]
,[DATA22]
,[DATA23]
,[DATA24]
,[DATA25]
,[DATA26]
,[DATA27]
,[DATA28]
,[DATA29]
,[DATA30]
,[DATA31]
,[DATA32]
,[DATA33]
,[DATA34]
,[DATA35]
,[DATA36]
,[DATA37]
,[DATA38]
,[DATA39]
,[DATA40]
,[DATA41]
,[DATA42]
,[DATA43]
,[DATA44]
,[DATA45]
,[DATA46]
,[DATA47]
,[DATA48]
,[DATA49]
,[DATA50]
,[DATA51]
,[DATA52]
,[DATA53]
,[DATA54]
,[DATA55]
,[DATA56]
,[DATA57]
,[DATA58]
,[DATA59]
,[DATA60]
,[DATA61]
,[DATA62]
,[DATA63]
,[DATA64]
,[DATA65]
,[DATA66]
,[DATA67]
,[DATA68]
,[DATA69]
,[DATA70]
,[DATA71]
,[DATA72]
,[DATA73]
,[DATA74]
,[DATA75]
,[DATA76]
,[DATA77]
,[DATA78]
,[DATA79]
,[DATA80]
,[DATA81]
,[DATA82]
,[DATA83]
,[DATA84]
,[DATA85]
,[DATA86]
,[DATA87]
,[DATA88]
,[DATA89]
,[DATA90]
,[DATA91]
,[DATA92]
,[DATA93]
,[DATA94]
,[DATA95]
,[DATA96]
,[DATA97]
,[DATA98]
,[DATA99]
,[DATA100]
,[DATA101]
,[DATA102]
,[DATA103]
,[DATA104]
,[DATA105]
,[DATA106]
,[DATA107]
,[DATA108]
,[DATA109]
,[DATA110]
,[DATA111]
,[DATA112]
,[DATA113]
,[DATA114]
,[DATA115]
,[DATA116]
,[DATA117]
,[DATA118]
,[DATA119]
,[DATA120]
,[DATA121]
,[DATA122]
,[DATA123]
,[DATA124]
,[DATA125]
,[DATA126]
,[DATA127]
,[DATA128]
,[DATA129]
,[DATA130]
,[DATA131]
,[DATA132]
,[DATA133]
,[DATA134]
,[DATA135]
,[DATA136]
,[DATA137]
,[DATA138]
,[DATA139]
,[DATA140]
,[DATA141]
,[DATA142]
,[DATA143]
,[DATA144]
,[DATA145]
,[DATA146]
,[DATA147]
,[DATA148]
,[DATA149]
,[DATA150]
,[DATA151]
,[DATA152]
,[DATA153]
,[DATA154]
,[DATA155]
,[DATA156]
,[DATA157]
,[DATA158]
,[DATA159]
,[DATA160]
,[DATA161]
,[DATA162]
,[DATA163]
,[DATA164]
,[DATA165]
,[DATA166]
,[DATA167]
,[DATA168]
,[DATA169]
,[DATA170]
,[DATA171]
,[DATA172]
,[DATA173]
,[DATA174]
,[DATA175]
,[DATA176]
,[DATA177]
,[DATA178]
,[DATA179]
,[DATA180]
)
VALUES (
N' '
,0
,N'Sales_Transaction_All'
,1
,0
,0
,N'SOP10100'
,N'SO_Trans_All'
,1
,0
,1
,N' '
,N' '
,N' '
,N' '
,N' '
,N''
,N'SOPNUMBE'
,N'SOPTYPE'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,2
,N'SOPNUMBE'
,N'SOPTYPE'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,2
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,170
,N'ORIGTYPE'
,N'ORIGNUMB'
,N'DOCID'
,N'DOCDATE'
,N'GLPOSTDT'
,N'QUOTEDAT'
,N'QUOEXPDA'
,N'ORDRDATE'
,N'INVODATE'
,N'BACKDATE'
,N'RETUDATE'
,N'ReqShipDate'
,N'FUFILDAT'
,N'ACTLSHIP'
,N'DISCDATE'
,N'DUEDATE'
,N'REPTING'
,N'TRXFREQU'
,N'TIMEREPD'
,N'TIMETREP'
,N'DYSTINCR'
,N'DTLSTREP'
,N'DSTBTCH1'
,N'DSTBTCH2'
,N'USDOCID1'
,N'USDOCID2'
,N'DISCFRGT'
,N'ORDAVFRT'
,N'DISCMISC'
,N'ORDAVMSC'
,N'DISAVAMT'
,N'ORDAVAMT'
,N'DISCRTND'
,N'ORDISRTD'
,N'DISTKNAM'
,N'ORDISTKN'
,N'DSCPCTAM'
,N'DSCDLRAM'
,N'ORDDLRAT'
,N'DISAVTKN'
,N'ORDATKN'
,N'PYMTRMID'
,N'PRCLEVEL'
,N'LOCNCODE'
,N'BCHSOURC'
,N'BACHNUMB'
,N'CUSTNMBR'
,N'CUSTNAME'
,N'CSTPONBR'
,N'PROSPECT'
,N'MSTRNUMB'
,N'PCKSLPNO'
,N'PICTICNU'
,N'MRKDNAMT'
,N'ORMRKDAM'
,N'PRBTADCD'
,N'PRSTADCD'
,N'CNTCPRSN'
,N'ShipToName'
,N'ADDRESS1'
,N'ADDRESS2'
,N'ADDRESS3'
,N'CITY'
,N'STATE'
,N'ZIPCODE'
,N'COUNTRY'
,N'PHNUMBR1'
,N'PHNUMBR2'
,N'PHONE3'
,N'FAXNUMBR'
,N'COMAPPTO'
,N'COMMAMNT'
,N'OCOMMAMT'
,N'CMMSLAMT'
,N'ORCOSAMT'
,N'NCOMAMNT'
,N'ORNCMAMT'
,N'SHIPMTHD'
,N'TRDISAMT'
,N'ORTDISAM'
,N'TRDISPCT'
,N'SUBTOTAL'
,N'ORSUBTOT'
,N'REMSUBTO'
,N'OREMSUBT'
,N'EXTDCOST'
,N'OREXTCST'
,N'FRTAMNT'
,N'ORFRTAMT'
,N'MISCAMNT'
,N'ORMISCAMT'
,N'TXENGCLD'
,N'TAXEXMT1'
,N'TAXEXMT2'
,N'TXRGNNUM'
,N'TAXSCHID'
,N'TXSCHSRC'
,N'BSIVCTTL'
,N'FRTSCHID'
,N'FRTTXAMT'
,N'ORFRTTAX'
,N'FRGTTXBL'
,N'MSCSCHID'
,N'MSCTXAMT'
,N'ORMSCTAX'
,N'MISCTXBL'
,N'BKTFRTAM'
,N'ORBKTFRT'
,N'BKTMSCAM'
,N'ORBKTMSC'
,N'BCKTXAMT'
,N'OBTAXAMT'
,N'TXBTXAMT'
,N'OTAXTAMT'
,N'TAXAMNT'
,N'ORTAXAMT'
,N'ECTRX'
,N'DOCAMNT'
,N'ORDOCAMT'
,N'PYMTRCVD'
,N'ORPMTRVD'
,N'DEPRECVD'
,N'ORDEPRVD'
,N'CODAMNT'
,N'ORCODAMT'
,N'ACCTAMNT'
,N'ORACTAMT'
,N'SALSTERR'
,N'SLPRSNID'
,N'UPSZONE'
,N'TIMESPRT'
,N'PSTGSTUS'
,N'VOIDSTTS'
,N'ALLOCABY'
,N'NOTEINDX'
,N'CURNCYID'
,N'CURRNIDX'
,N'RATETPID'
,N'EXGTBLID'
,N'XCHGRATE'
,N'DENXRATE'
,N'EXCHDATE'
,N'TIME1'
,N'RTCLCMTD'
,N'MCTRXSTT'
,N'TRXSORCE'
,N'COMMNTID'
,N'REFRENCE'
,N'POSTEDDT'
,N'PTDUSRID'
,N'USER2ENT'
,N'CREATDDT'
,N'MODIFDT'
,N'Tax_Date'
,N'APLYWITH'
,N'WITHHAMT'
,N'SHPPGDOC'
,N'CORRCTN'
,N'SIMPLIFD'
,N'CORRNXST'
,N'DOCNCORR'
,N'SEQNCORR'
,N'SALEDATE'
,N'EXCEPTIONALDEMAND'
,N'Flags'
,N'BackoutTradeDisc'
,N'OrigBackoutTradeDisc'
,N'GPSFOINTEGRATIONID'
,N'INTEGRATIONSOURCE'
,N'INTEGRATIONID'
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
)

The new requester row creates the associated insert trigger against SOP10100 as shown below.

image

The trigger contains the following SQL, note now no required field any more!



/****** Object: Trigger [dbo].[eConnect_Sales_Transaction_AllSOP10100_InsertTrigger] Script Date: 02/03/2016 15:11:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER [dbo].[eConnect_Sales_Transaction_AllSOP10100_InsertTrigger] ON [dbo].[SOP10100]
FOR INSERT
AS
DECLARE @required CHAR(50)
,@DRI INT
,@SOPNUMBE VARCHAR(50)
,@SOPTYPE VARCHAR(50)

DECLARE eConnect_Sales_Transaction_AllSOP10100_InsertTrigger CURSOR
FOR
SELECT convert(VARCHAR(50), SOPNUMBE, 121)
,convert(VARCHAR(50), SOPTYPE, 121)
FROM inserted a

SET NOCOUNT ON

OPEN eConnect_Sales_Transaction_AllSOP10100_InsertTrigger

FETCH NEXT
FROM eConnect_Sales_Transaction_AllSOP10100_InsertTrigger
INTO @SOPNUMBE
,@SOPTYPE

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
IF (
NOT EXISTS (
SELECT 1
FROM eConnectOutTemp(NOLOCK)
WHERE DOCTYPE = 'Sales_Transaction_All'
AND INDEX1 = @SOPNUMBE
AND INDEX2 = @SOPTYPE
)
)
BEGIN
SELECT @DRI = 0

SELECT @DRI = isnull(DEX_ROW_ID, 0)
FROM SOP10100(NOLOCK)
WHERE SOPNUMBE = @SOPNUMBE
AND SOPTYPE = @SOPTYPE

IF (@DRI > 0)
BEGIN
DELETE eConnect_Out
FROM eConnect_Out b(UPDLOCK)
WHERE (
b.DOCTYPE = 'Sales_Transaction_All'
AND ACTION = 1
AND INDEX1 = @SOPNUMBE
AND INDEX2 = @SOPTYPE
)

INSERT INTO eConnect_Out (
DOCTYPE
,TABLENAME
,DEX_ROW_ID
,INDEX1
,INDEX2
,INDEX3
,INDEX4
,INDEX5
,INDEX6
,INDEX7
,INDEX8
,INDEX9
,INDEX10
,INDEX11
,INDEX12
,INDEX13
,INDEX14
,INDEX15
,ACTION
)
SELECT 'Sales_Transaction_All'
,'SOP10100'
,@DRI
,@SOPNUMBE
,@SOPTYPE
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,1
END
END

FETCH NEXT
FROM eConnect_Sales_Transaction_AllSOP10100_InsertTrigger
INTO @SOPNUMBE
,@SOPTYPE
END

DEALLOCATE eConnect_Sales_Transaction_AllSOP10100_InsertTrigger
GO

The new requester type also now shows up in the setup tool as expected for future editing (might need to reconnect to refresh).

image

Finally here we have it, the output record in the output table,

image

Note that this record in the output table is created immediately that the order is created, but before it is saved. Beware acting on this until the integrating application can see the key fields of the order are complete, perhaps check the dexterity lock tables too, thus to ensure the editing is complete. Maybe this was the misguided reason that led the CUSTNMBR to be included as a required field originally?

In reality if it is merely a flag for created orders that is required, for the setup record, only SOPNUMBE and SOPTYPE are worth capturing as data fields, the rest are mostly blank anyway.

Also note that the user could click the [x] and close the order, and the SOPNUMBE will be returned to the stack and could be used again with a future order. The integrating application has to handle that too.

Summary

Although eConnect installs some example requester documents to get users going, additional requesters can be added to meet specific requirements. eConnect requesters are configurable and quite flexible, however this requires direct editing of the setup table. 

eConnect fails to update field ITMVNDTY (IV00103) from field PRIMVNDR (IV00102)

Why does eConnect not update the IV00103 ITMVNDTY field when changing the primary vendor, PRIMVNDR of IV00102?

Table IV00102 item-site

This table relates items to sites, including what stock is at each site and which primary default vendor should be used for that site. The default vendor is held in field PRIMVNDR, Primary Vendor.

Table IV00103 item-vendor

This table relates items to vendors. It has a field ITMVNDTY this is the “item vendor type”. The field would normally only contain 1 or 2 as a value. There are also some reports of corrupt records with a value of 0, but this is not normal.

ITMVNDTY
Value

Meaning
1 This vendor is the prime vendor for this item in one of the sites
2 This vendor is not a prime vendor in any site for this item

Normal operation

When using the GP client, as soon as a vendor is selected in the primary vendor field of the item site window, then the value of ITMVNDTY is set to 1 for that site-vendor combination.

Should a different vendor be set as primary vendor for another site, then that vendor-item combination will also be set with a value of 1. Hence it is normal to have multiple records for single item to have prime vendor type set to a value of 1 in IV00103 if the item is set up in multiple sites.

eConnect taItemSite does not update the primary vendor type

The taItemSite node in Dynamics GP allows the Prime Vendor to be set for an item site. On decryption of the eConnect stored procedure taItemSite, there is no reference to table IV00103. Hence updating the primary vendor using eConnect will corrupt or not set the field ITMVNDTY in IV00103 as one would expect it to.

This problem could be addressed by adding the following SQL into the post eConnect procedure taItemSitePost. This post procedure is called after the taItemSite has executed. The SQL is written to correct the ITMVNDTY in respect of any changes to the IV00102 item site table.

SQL snippet if an example, please test and verify in your own environment before using against a production database.

;

WITH VendorSummary
AS (
SELECT ITEMNMBR
,PRIMVNDR
FROM IV00102
WHERE PRIMVNDR != ''
AND RCRDTYPE = 2
GROUP BY ITEMNMBR
,PRIMVNDR
)
UPDATE IV00103
SET ITMVNDTY = CASE
WHEN VendorSummary.PRIMVNDR IS NULL
THEN 2
ELSE 1
END
FROM IV00103
LEFT JOIN VendorSummary ON IV00103.ITEMNMBR = VendorSummary.ITEMNMBR
AND IV00103.VENDORID = VendorSummary.PRIMVNDR
WHERE IV00103.ITEMNMBR = @I_vITEMNMBR

Custom eConnect XML Node–Procedure or function expects parameter, which was not supplied

Custom XML nodes error with parameters

It is possible to run your own custom stored procedures from eConnect, by passing in parameters via a custom XML nodes. I have explained this technique in a previous post, there is more information on MSDN too:

When developing a new Custom XML node it may be that the following error is encountered

Procedure or function expects parameter,  which was not supplied

This may be due to misconfiguration of the stored procedure parameters or XML node names.
Remember:

  • Prefix the stored procedure parameter names with I_v (Input Variable), so a parameter named “cardnumber” would become @I_Vcardnumber in the stored procedure
  • Pass in the eConnect XML document node like this (no prefix):
    <cardnumber>4929000000006</cardnumber>

Dynamics GP Integration Service

The Dynamics GP Integration Service, a windows service that provides an econnect end point service, if this is how the documents are being pushed through to GP, if using this then read on.

eConnect for Microsoft Dynamics GP 2013 Integration Service

eConnect integration Service for Dynamics GP

Cause

I have seen this behaviour a few times now. The error stated above occurs, using SQL profiler to view the SQL conversation between eConnect and the stored procedure, it looks like the parameters are being passed with the value of “default” rather than the expected values from XML.

Putting a break point on the application and inspecting the source eConnect XML document, it seems the XML looks ok, with all the data in the relevant nodes of the document. 

-So the nodes are not being extracted by eConnect and passed as the SQL parameters – why?

Solution

If this is being experienced, I expect that the stored procedure has been edited and/or the fields in the document have been edited, perhaps adding an extra field, changing a parameter name? What I have found is that the Integration Service seems to cache the profile of the SQL stored procedure parameters. I can guess this would be a good idea for performance reasons, but it means if a change is made to custom XML nodes or procedures there of, then the service must be restarted.

Restarting the service resets the cache and suddenly with no further changes the custom nodes behave correctly again! This cache behaviour can cause some serious head scratching until it is revealed what is happening.

not your issue?

There is another solution from another cause for this error, the post on Steve Endow’s blog explains:
Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!