eConnect provides a way to move data into and out of Microsoft Dynamics GP. In this post we’ll construct the database components for an eConnect node. We will not construct the .NET pieces.

The piece we will construct here is sufficient to allow us to use the new node from eOne SmartConnect. We’ll show how to do that in a subsequent post.

eOne Logo

We’ll make a node that allows us to add documents to a bill of material assembly batch in GP.

An Assembly

An Assembly

The database piece has this general shape:

  • Top level stored procedure is taBMTransactionInsert
  • That references stored procedure taBMTransactionInsert_Custom
  • That references stored procedures taBMTransactionInsertPre and taBMTransactionInsertPost

taBMTransactionInsert establishes the parameter names and types for all the stored procedures. This is the stored procedure that is invoked by SmartConnect.

taBMTransactionInsert_Custom is where we will do the work.

taBMTransactionInsertPre and taBMTransactionInsertPost are invoked before and after the taBMTransactionInsert_Custom procedure. This probably isn’t needed for our custom node, but this way we follow the Microsoft convention.

So without further ado, here come the stored procedures. These stored procedures need to be created in each company database where they’ll be used.

[sql]
CREATE procedure [dbo].[taBMTransactionInsert]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) = ” output,
@I_vInventoryAccountOffSet varchar(75) = ” output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int = 0 output,
@oErrString varchar(255) = ” output
as
begin
execute dbo.taBMTransactionInsert_Custom
@I_vBACHNUMB output,
@I_vBMDOCNBR output,
@I_vITEMNMBR output,
@I_vTRXQTY output,
@I_vUOFM output,
@I_vUNITCOST output,
@I_vTRXLOCTN output,
@I_vInventoryAccount output,
@I_vInventoryAccountOffSet output,
@I_vOverrideQty output,
@I_vUSRDEFND1 output,
@I_vUSRDEFND2 output,
@I_vUSRDEFND3 output,
@I_vUSRDEFND4 output,
@O_iErrorState output,
@oErrString output;
end;
[/sql]

[sql]
CREATE procedure [dbo].[taBMTransactionInsert_Custom]
@I_vBACHNUMB char(15) output, /* Batch Number <Required> */
@I_vBMDOCNBR char(17) output, /* BM Document Number <Required> */
@I_vITEMNMBR char(30) output, /* Assembly Number <Required> */
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output, /* Inventory Site <Required> */
@I_vInventoryAccount varchar(75) = ” output, /* Input Inventory Account, no punctuation */
@I_vOutputInventoryAccount varchar(75) = ” output, /* Output Inventory Account, no punctuation */
@I_vInventoryAccountOffSet varchar(75) = ” output, /* Inventory Offset Account, no punctuation */
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND2 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND3 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@I_vUSRDEFND4 char(21) = ‘ ‘ output, /* User Defined field – developer use only */
@O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */
@oErrString varchar(255) = ” output /* Return Error Code List */
as
begin

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

execute dbo.taBMTransactionInsertPre
@I_vBACHNUMB,
@I_vBMDOCNBR,
@I_vITEMNMBR,
@I_vTRXQTY,
@I_vUOFM,
@I_vUNITCOST,
@I_vTRXLOCTN,
@I_vInventoryAccount,
@I_vInventoryAccountOffSet,
@I_vOverrideQty,
@I_vUSRDEFND1,
@I_vUSRDEFND2,
@I_vUSRDEFND3,
@I_vUSRDEFND4,
@O_iErrorState,
@oErrString;

declare
@O_oErrorState int,
@iError int,
@iStatus smallint,@iAddCodeErrState int;
/*********************** Initialize locals ******************************/
select
@O_iErrorState = 0,
@oErrString = ”,
@iStatus = 0,
@iAddCodeErrState = 0;
/***************** Custom Procedure edit check validation ***************/
/*If a required variable is ” then we need to add the error code */
/*35010 to the @oErrString output variable.*/
/*The method that eConnect uses to append all error string is the */
/*taUpdateString procedure.*/
/*Error codes can be appended to the @oErrString variable: for example you */
/*could append a 33 44 55 66 to the @oErrString variable */
/*After the error codes have been appended to the @oErrString variable. */
/***********************************************************************/
if ( @I_vBMDOCNBR = ” )
begin
select @O_iErrorState = 35010; /* A required value is blank */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@iAddCodeErrState output;
end;
/* Do some custom business logic */

INSERT INTO BM10200 ([TRX_ID],[BM_Trx_Status],[BCHSOURC],[BACHNUMB],[Batch_ID_Note_Index]
,[Completion_Date],[BM_Start_Date],[TRXDATE],[PSTGDATE]
,[REFRENCE],[Quantity_Shortage_Status],[BM_Trx_Errors],[BM_Component_Errors]
,[NOTEINDX],[MODIFDT],[USER2ENT],[CREATDDT]
,[PTDUSRID],[POSTEDDT],[TRXSORCE]
,[USERDEF1],[USERDEF2],[USRDEF03],[USRDEF04])

VALUES (@I_vBMDOCNBR,3,’ASMENT’,@I_vBACHNUMB,0.00000
,’1/1/1900′,CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101)
,’ ‘,@I_vOverrideQty,0x00000000,0x00000000
,0.00000,CONVERT(char(10),GetDate(),101),’ ‘,CONVERT(char(10),GetDate(),101)
,’ ‘,CONVERT(char(10),GetDate(),101),’ ‘
,@I_vUSRDEFND1,@I_vUSRDEFND2,@I_vUSRDEFND3,@I_vUSRDEFND4);

— To Do: Proper handling for units of measure
— To Do: Check for sufficient quantities on hand
— To Do: Honor the override quantity parameter setting
— To Do: Proper handling for build versus stock flags
— To Do: Detect invalid part numbers and return error code
— To Do: Detect invalid account numbers and return error code
— To Do: Make sure scrap_percentage is cumulative in GP as is assumed below
— To Do: Figure out codes for column [BM_Component_Type] in table BM10300
— To Do: Figure out codes for column [BM_Component_Options] in table BM10300
— To Do: Make account number format general instead of assuming 5-3 format
— To Do: Add optional serial number tracking

WITH CHILDRN(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage_Times_100, IsAssembly, StockMethod, Pseudo_Component_ID, Pseudo_Parent_Component_ID) AS (
SELECT DISTINCT
a.ITEMNMBR,
a.ITEMNMBR,
0,
CAST(1 AS Numeric(19,5)),
CAST(b.SELNGUOM AS CHAR(9)) AS UOFM,
CAST(0 AS BIGINT) AS Scrap_Percentage_Times_100,
1 AS IsAssembly,
CAST(3 AS SMALLINT) /*c.BM_Stock_Method*/,
CAST(0 AS DECIMAL(30,15)) AS Pseudo_Component_ID,
CAST(-1 AS DECIMAL(30,15)) AS Pseudo_Parent_Component_ID
FROM BM00111 a
JOIN IV00101 b
ON a.ITEMNMBR=b.ITEMNMBR
JOIN BM00101 c
ON a.ITEMNMBR=c.ITEMNMBR
UNION ALL
SELECT
p.ITEMNMBR,
c.CMPTITNM,
p.DEPTH + 1,
CAST(c.Design_Qty * p.QTY AS Numeric(19,5)),
CAST(c.UOFM AS CHAR(9)) AS UOFM,
— Accumulate scrap- for example if you have 10% scrap turning A into B, and 10% turning B into C, cumulative total scrap for C is 1-.9*.9=19%
CAST(10000 – (10000 – c.Scrap_Percentage) * (10000 – p.Scrap_Percentage_Times_100) * 0.0001 AS BIGINT) AS Scrap_Percentage_Times_100,
CASE WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101) THEN 1 ELSE 0 END AS IsAssembly,
CAST(
CASE
WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101 WHERE BM_Stock_Method=1) THEN 1
WHEN c.CMPTITNM IN (SELECT ITEMNMBR FROM BM00101 WHERE BM_Stock_Method=3) THEN 3
ELSE 2
END
AS SMALLINT) AS StockMethod,
CAST(p.Pseudo_Component_ID +
case
when p.depth=0 then 1
when p.depth=1 then 0.1
when p.depth=2 then 0.01
when p.depth=3 then 0.001
when p.depth=4 then 0.0001
when p.depth=5 then 0.00001
when p.depth=6 then 0.000001
when p.depth=7 then 0.0000001
when p.depth=8 then 0.00000001
else 0.000000001
end
* ROW_NUMBER() OVER (ORDER BY ORD) AS DECIMAL(30,15)) AS Pseudo_Component_ID,
CAST(p.Pseudo_Component_ID AS DECIMAL(30,15)) AS Pseudo_Parent_Component_ID
FROM BM00111 c
JOIN CHILDRN p
ON c.ITEMNMBR=p.CMPTITNM
),
COMPONENTS1(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage, IsAssembly, StockMethod, Component_ID, Pseudo_Component_ID, Pseudo_Parent_Component_ID) AS (
SELECT
ITEMNMBR,
CMPTITNM,
DEPTH,
QTY,
UOFM,
Scrap_Percentage_Times_100 * 0.01,
IsAssembly,
StockMethod,
16384 * (ROW_NUMBER() OVER (PARTITION BY ITEMNMBR ORDER BY Pseudo_Component_ID) – 1) AS Component_ID,
Pseudo_Component_ID,
Pseudo_Parent_Component_ID
FROM CHILDRN
),
COMPONENTS(ITEMNMBR, CMPTITNM, DEPTH, QTY, UOFM, Scrap_Percentage, IsAssembly, StockMethod, Component_ID, Parent_Component_ID) AS (
SELECT
a.ITEMNMBR,
a.CMPTITNM,
a.DEPTH,
a.QTY,
a.UOFM,
a.Scrap_Percentage,
a.IsAssembly,
a.StockMethod,
CAST(a.Component_ID AS INT),
CAST(COALESCE(b.Component_ID, -1) AS INT)
FROM COMPONENTS1 a
LEFT JOIN COMPONENTS1 b
ON a.ITEMNMBR=b.ITEMNMBR
AND a.Pseudo_Parent_Component_ID=b.Pseudo_Component_ID
)

INSERT INTO BM10300 ([TRX_ID],[Component_ID],[Parent_Component_ID],[ITEMNMBR],[ITEMDESC] –1
,[UOFM],[LOCNCODE],[Location_Code_Note_Index],[Stock_Quantity],[Assemble_Quantity],[ATYALLOC] –2
,[BM_Stock_Method],[Cost_Type],[INVINDX],[Inventory_Account_Reference],[Inventory_Account_Note_I] –3
,[Variance_Index],[Variance_Account_Reference] –4
,[Variance_Account_Note_In],[Design_Quantity] –5
,[Scrap_Percentage],[STNDCOST],[Standard_Quantity],[Extended_Standard_Cost],[Extended_Standard_Quantity] –6
,[Ext_Assemble_Qty_Posting_Cost],[Ext_Stock_Receipt_Cost],[Ext_Stock_Qty_Posting_Cost] –7
,[Ext_Subasm_Posting_Cost],[Ext_Subasm_Receipt_Cost],[BM_Component_Type],[Lvl],[QTYBSUOM] –8
,[Assemble_Serial_Lot_Count],[Stock_Serial_Lot_Count] –9
,[Temp_Allocated_Quantity],[Temp_Assemble_Quantity],[Temp_Extended_Assemble_Q],[Temp_Location_Code] –10
,[Temp_Stock_Quantity],[Temp_Extended_Stock_Quan],[DECPLQTY],[DECPLCUR],[ITMTRKOP],[NOTEINDX] –11
,[BM_Component_Errors] –12
,[TRXSORCE],[EXTQTYAL],[EXTQTYSEL] –13
,[Temp_Extended_Standard_Q],[Temp_Existing_Quantity_A],[Incl] –14
,[BM_Component_Options],[BIN]) –15

SELECT @I_vBMDOCNBR,tbm.Component_ID,tbm.Parent_Component_ID,tbm.CMPTITNM,tiv.ITEMDESC –1
,tbm.UOFM AS SELNGUOM
,@I_vTRXLOCTN
,0 — Location code note index
,CASE WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 WHEN StockMethod=2 THEN tbm.QTY * @I_vTRXQTY ELSE 0 END — Stock quantity
,CASE WHEN StockMethod<>2 THEN tbm.QTY * @I_vTRXQTY ELSE 0 END — Assemble quantity
,CASE
WHEN StockMethod<>2 OR tbm.Parent_Component_ID=-1 THEN 0
WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 — This is probably the wrong logic, but wire should not be allocated for some reason
ELSE tbm.QTY * @I_vTRXQTY
END — 2
,CASE
WHEN tbm.Parent_Component_ID=-1 THEN 3
ELSE COALESCE(toa.BM_Stock_Method, 2)
END AS BM_Stock_Method
,1
,CASE
WHEN tbm.Parent_Component_ID = -1 THEN COALESCE(tgi.ACTINDX, tiv.IVIVINDX, 0)
ELSE COALESCE(tgi.ACTINDX, tiv.IVIVINDX, 0)
END
,”
,CASE
WHEN tbm.Parent_Component_ID = -1 THEN COALESCE(tgi.NOTEINDX, 0)
ELSE COALESCE(tgi.NOTEINDX, igl.NOTEINDX, 0)
END — 3
,CASE
WHEN tbm.IsAssembly = 0 THEN 0
ELSE COALESCE(tgo.ACTINDX, tiv.IVVARIDX, 0)
END
,” –4
,CASE
WHEN tbm.IsAssembly = 0 THEN 0
ELSE COALESCE(tgo.NOTEINDX, igl.NOTEINDX, 0)
END
— GP uses 0 for design quantity of parent
,CASE WHEN tbm.Parent_Component_ID = -1 THEN 0 ELSE tbm.QTY END –5
,tbm.Scrap_Percentage * 100,tiv.STNDCOST
— GP uses transaction quantity for standard quantity of parent
,CASE WHEN tbm.Parent_Component_ID = -1 THEN @I_vTRXQTY ELSE tbm.QTY / (1.0 – tbm.Scrap_Percentage * 0.01) END
,0
— GP is consistent about extended standard quantity
,CASE WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 ELSE @I_vTRXQTY * tbm.QTY / (1.0 – tbm.Scrap_Percentage * 0.01) END –6
,0,0,0 –7
,0,0
,CASE WHEN IsAssembly<>0 THEN 2 ELSE tiv.ITEMTYPE END
,tbm.DEPTH,1 –8
,0,0 –9
,0,0,0,” –10
,0,0,tiv.DECPLQTY,tiv.DECPLCUR,tiv.ITMTRKOP,0 –11
,0x00000000 –12
,”

,CASE
WHEN StockMethod<>2 OR tbm.Parent_Component_ID=-1 THEN 0
–WHEN tbm.UOFM<>’EACH’ AND tbm.Parent_Component_ID>0 THEN 0 — This is probably the wrong logic, but wire should not be allocated for some reason
ELSE tbm.QTY * @I_vTRXQTY
END / (1.0 – tbm.Scrap_Percentage * 0.01) — 2

,0 –13
,0,0,0 –14
,CASE WHEN tbm.Parent_Component_ID=-1 THEN 0x91000000 ELSE 0x81000000 END
,” –15

FROM IV00101 tiv
JOIN COMPONENTS tbm
ON tiv.ITEMNMBR=tbm.CMPTITNM
LEFT JOIN BM00101 toa
ON tbm.CMPTITNM=toa.ITEMNMBR
LEFT JOIN GL00100 igl
ON tiv.IVIVINDX=igl.ACTINDX

LEFT JOIN GL00100 tgi
ON RTRIM(@I_vInventoryAccount)=RTRIM(tgi.ACTNUMBR_1) + RTRIM(tgi.ACTNUMBR_2) + RTRIM(tgi.ACTNUMBR_3) + RTRIM(tgi.ACTNUMBR_4) + RTRIM(tgi.ACTNUMBR_5) + RTRIM(tgi.ACTNUMBR_6)
LEFT JOIN GL00100 tgo
ON RTRIM(@I_vInventoryAccountOffset)=RTRIM(tgo.ACTNUMBR_1) + RTRIM(tgo.ACTNUMBR_2) + RTRIM(tgo.ACTNUMBR_3) + RTRIM(tgo.ACTNUMBR_4) + RTRIM(tgo.ACTNUMBR_5) + RTRIM(tgo.ACTNUMBR_6)
WHERE tbm.ITEMNMBR=@I_vITEMNMBR

— Insert the batch header if it is not present

IF NOT (EXISTS (SELECT * FROM SY00500 WHERE BACHNUMB=@I_vBACHNUMB))
BEGIN
INSERT INTO SY00500
([GLPOSTDT]
,[BCHSOURC]
,[BACHNUMB]
,[SERIES]
,[MKDTOPST]
,[NUMOFTRX]
,[RECPSTGS]
,[DELBACH]
,[MSCBDINC]
,[BACHFREQ]
,[RCLPSTDT]
,[NOFPSTGS]
,[BCHCOMNT]
,[BRKDNALL]
,[CHKSPRTD]
,[RVRSBACH]
,[USERID]
,[CHEKBKID]
,[BCHTOTAL]
,[BCHEMSG1]
,[BCHEMSG2]
,[BACHDATE]
,[BCHSTRG1]
,[BCHSTRG2]
,[POSTTOGL]
,[MODIFDT]
,[CREATDDT]
,[NOTEINDX]
,[CURNCYID]
,[BCHSTTUS]
,[CNTRLTRX]
,[CNTRLTOT]
,[PETRXCNT]
,[APPROVL]
,[APPRVLDT]
,[APRVLUSERID]
,[ORIGIN]
,[ERRSTATE]
,[GLBCHVAL]
,[Computer_Check_Doc_Date]
,[Sort_Checks_By]
,[SEPRMTNC]
,[REPRNTED]
,[CHKFRMTS]
,[TRXSORCE]
,[PmtMethod]
,[EFTFileFormat]
,[Workflow_Approval_Status]
,[Workflow_Priority]
,[TIME1])
VALUES
(CONVERT(char(10),GetDate(),101) /* <GLPOSTDT, datetime,> */
,’ASMENT’ /* <BCHSOURC, char(15),> */
,@I_vBACHNUMB /* <BACHNUMB, char(15),> */
,5 /* ??? <SERIES, smallint,> */
,0 /* <MKDTOPST, tinyint,> */
,0 /* <NUMOFTRX, int,> */
,0 /* <RECPSTGS, smallint,> */
,0 /* <DELBACH, tinyint,> */
,0 /* <MSCBDINC, smallint,> */
,1 /* <BACHFREQ, smallint,> */
,’1900-01-01 00:00′ /* <RCLPSTDT, datetime,> */
,0 /* <NOFPSTGS, smallint,> */
,’ ‘ /* <BCHCOMNT, char(61),> */
,0 /* <BRKDNALL, tinyint,> */
,0 /* <CHKSPRTD, tinyint,> */
,0 /* <RVRSBACH, tinyint,> */
,’ ‘ /* <USERID, char(15),> */
,’ ‘ /* <CHEKBKID, char(15),> */
,0 /* <BCHTOTAL, numeric(19,5),> */
,0x00000000 /* <BCHEMSG1, binary(4),> */
,0x00000000 /* <BCHEMSG2, binary(4),> */
,’1900-01-01 00:00′ /* <BACHDATE, datetime,> */
,’ ‘ /* <BCHSTRG1, char(21),> */
,’ ‘ /* <BCHSTRG2, char(21),> */
,1 /* <POSTTOGL, tinyint,> */
,CONVERT(char(10),GetDate(),101) /* <MODIFDT, datetime,> */
,CONVERT(char(10),GetDate(),101) /* <CREATDDT, datetime,> */
,0.00000 /* <NOTEINDX, numeric(19,5),> */
,’ ‘ /* <CURNCYID, char(15),> */
,0 /* <BCHSTTUS, smallint,> */
,0 /* <CNTRLTRX, int,> */
,0.00000 /* <CNTRLTOT, numeric(19,5),> */
,0 /* <PETRXCNT, smallint,> */
,0 /* <APPROVL, tinyint,> */
,’1900-01-01 00:00′ /* <APPRVLDT, datetime,> */
,’ ‘ /* <APRVLUSERID, char(15),> */
,3 /* <ORIGIN, smallint,> */
,0 /* <ERRSTATE, int,> */
,0x00000000 /* <GLBCHVAL, binary(4),> */
,’1900-01-01 00:00′ /* <Computer_Check_Doc_Date, datetime,> */
,0 /* <Sort_Checks_By, smallint,> */
,0 /* <SEPRMTNC, tinyint,> */
,0 /* <REPRNTED, smallint,> */
,0 /* <CHKFRMTS, smallint,> */
,’ ‘ /* <TRXSORCE, char(13),> */
,0 /* <PmtMethod, smallint,> */
,0 /* <EFTFileFormat, smallint,> */
,0 /* <Workflow_Approval_Status, smallint,> */
,0 /* <Workflow_Priority, smallint,> */
,’1900-01-01 00:00’ /* <TIME1, datetime,> */);
END;

— Update the quantities in the batch header

UPDATE SY00500
SET
NUMOFTRX = NUMOFTRX + 1,
BCHTOTAL = BCHTOTAL + @I_vTRXQTY
WHERE BACHNUMB = @I_vBACHNUMB;

execute dbo.taBMTransactionInsertPost
@I_vBACHNUMB,
@I_vBMDOCNBR,
@I_vITEMNMBR,
@I_vTRXQTY,
@I_vUOFM,
@I_vUNITCOST,
@I_vTRXLOCTN,
@I_vInventoryAccount,
@I_vInventoryAccountOffSet,
@I_vOverrideQty,
@I_vUSRDEFND1,
@I_vUSRDEFND2,
@I_vUSRDEFND3,
@I_vUSRDEFND4,
@O_iErrorState,
@oErrString;

/* End custom business logic */
return (@O_iErrorState);
END;
[/sql]

A worried person

Does this never end???

[sql]
CREATE procedure [dbo].[taBMTransactionInsertPre]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) output,
@I_vInventoryAccountOffSet varchar(75) output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int output,
@oErrString varchar(255) output
as
begin
set nocount on;
select @O_iErrorState = 0;
return (@O_iErrorState);
end;
[/sql]

[sql]
CREATE procedure [dbo].[taBMTransactionInsertPost]
@I_vBACHNUMB char(15) output,
@I_vBMDOCNBR char(17) output,
@I_vITEMNMBR char(30) output,
@I_vTRXQTY numeric(19,5) = 1 output,
@I_vUOFM char(8) = ‘EACH’ output,
@I_vUNITCOST numeric(19,5) = 0 output,
@I_vTRXLOCTN char(10) output,
@I_vInventoryAccount varchar(75) output,
@I_vInventoryAccountOffSet varchar(75) output,
@I_vOverrideQty tinyint = 0 output,
@I_vUSRDEFND1 char(21) = ‘ ‘ output,
@I_vUSRDEFND2 char(21) = ‘ ‘ output,
@I_vUSRDEFND3 char(21) = ‘ ‘ output,
@I_vUSRDEFND4 char(21) = ‘ ‘ output,
@O_iErrorState int output,
@oErrString varchar(255) output
as
begin
set nocount on;
select @O_iErrorState = 0;
return (@O_iErrorState);
end;
[/sql]

In a future post we’ll see how to use this from eOne SmartConnect.

2 Responses to “Adding and Using a Microsoft Dynamics GP eConnect Node, Part 1”
  1. Brad Trevaskis

    Wow! I can’t even tell you how amazing a find this is! Only question I have is, where are you getting the next @I_vBMDOCNBR (TRX_ID)? I’ve used eConnect to grab the next IV docnumber, but to my knowledge, there isn’t a sproc for a BM docnum.

    Huge THANKS!
    Brad Trevaskis

    Reply
    • Rick Kohler

      Brad,

      Thanks, I’m glad someone is getting some use out of this!

      As you probably know, when you type an assembly transaction into GP, you do not have to accept the default document number. You can enter your own number. That’s essentially what we’re doing. The data we’re importing includes a unique ascending integer, and I’m just putting a “C” in front of that (C means something to us). So our TRX_ID’s look like C1, C2, C3, … and we’re ignoring what the BM40100 table says about the next doc number.

      That works for us anyway.

      I’m thrilled someone is actually reading this! :)

      Rick

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available