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.
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;
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.
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).
Finally here we have it, the output record in the output table,
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.