DynDeveloper.com

DynDeveloper.com

GuestLog Out
You are accessing the Members Only area for DynDeveloper.com using guest access. The member experience is slightly different
Joel 4/26/2012 8:56:55 AM

eConnect - How to use the Post and Pre stored procedures to expand an integration

* This article, and all our great .NET Development documentation, Is available on the .NET Development menu

One of the more frequent questions that we get here is how to take a standard integration and do something a little extra. Maybe add lines to the MDA or Extender tables, or to update fields that are not covered by the integration.

This article will show you how to do that using the Post and Pre stored procedures that ship with eConnect

The text is largely out of the documentation, but I've added some points for clarity. The main benefit of having this text here is that we can easily refer to it when answering questions.

Version: GP 2010
Section: eConnect

To modify eConnect’s business logic, place custom SQL code in the pre or post procedures. The custom code in the pre and post procedures allow you to modify or extend the behavior of the core eConnect stored procedure.

Understand that each node in your eConnect integration (taPOHdr, taPOline, taUpdateCreateItemRcd) corresponds to a stored procedure with the exact same name, and that there are two others that fire immediately before and after the integration with the same name but with 'Pre' or 'Post' appended.

So, for taPoHdr, the sequence would be:
taPoHdrPre
taPoHdr
taPoHdrPost

Got that?

To expand on that point, the various parts of an integration are (generally) integrated in the order that they're listed in the documentation. That means that the lines generally go in first. So, for a PO with two lines, you'd get this:

taPoLinePre
taPoLine
taPoLinePost
taPoLinePre
taPoLine
taPoLinePost
taPoHdrPre
taPoHdr
taPoHdrPost

To customize a pre or post stored procedure, complete the following steps:

Open the .sql file for the stored procedure.
eConnect supplies a file for each pre and post stored procedure you can modify. To find a specific file, open the folder C:\Program Files (x86)\Microsoft Dynamics\eConnect 11\Custom Procedures. Earlier versions have a slightly different path. This folder contains a subfolder for each transaction type schema. Open the subfolder that contains the stored procedure you want to modify.

As an example, assume you want to modify the taUpdateCreateCustomerRcdPost stored procedure. Open the C:\Program Files\Microsoft Dynamics\eConnect 11\Custom Procedures\Receivables folder. Next, open the taUpdateCreateCustomerRcdPost.sql file. You may edit the file using any text editor or Microsoft SQL Server Management Studio.

Add your custom SQL code.
With the .sql file open, you can add custom SQL code to the file. The only parts of the document you should change are the Revision History and the section of the file specified for custom business logic. Your SQL code should be added between the following comments:

/* Create Custom Business Logic */
  
  
/* End Create Custom Business Logic */

 


To avoid errors or unexpected results, do not modify any of the other statements in the file. After adding your custom business logic, save the file.

Run the .sql file in Microsoft SQL Server Management Studio.
Open the modified file with Microsoft SQL Server Management Studio. Use the drop-down list from the toolbar to specify the Microsoft Dynamics GP database that contains the target stored procedure. Click the Execute button. The Query Messages window displays whether the stored procedure was successfully updated. If it succeeded, the stored procedure now includes your custom SQL code.

The following SQL example shows a customized taCreateTerritoryPre stored procedure. The example overrides the value in the Territory Description (SLTERDSC) parameter to reflect that the sales territory was created using eConnect:

USRDEFND

Last, note that the last 5 fields in every integration are the USRDEFND fields. These are not the same as the user defined fields that are in some tables, they are here specificaly for integration. Any value that you put in these fields is available in the Pre and Post procedures, and is then discarded. If you want to pass in some information to use, this is the place to do it.

/* Begin_Procs taCreateTerritoryPre */
if exists (select * from sysobjects where id = object_id('dbo.taCreateTerritoryPre')and type = 'P')
begin
    drop procedure dbo.taCreateTerritoryPre
end
go
  
create procedure dbo.taCreateTerritoryPre
/*
**********************************************************************
* (c) 2004 Microsoft Business Solutions, Inc.
**********************************************************************
*
* PROCEDURE NAME: taCreateTerritoryPre
*
* SANSCRIPT NAME: NA
*
* PARAMETERS:
*
* DESCRIPTION: taCreateSalespersonPost Integration Stored Procedure
*
* TABLES:
*           Table Name Access
*           ========== ======
*
* PROCEDURES CALLED:
*
* DATABASE: Company
*
* RETURN VALUE:
*           0   = Successful
*           non-0 = Not successful
*
* REVISION HISTORY:
*
*   Date    Who     Comments
*   ----------------------------------------------------------------
*
*
********************************************************************
*
********************************************************************
*/
@I_vSALSTERR  char(15) output, /*Territory ID <Required>*/
@I_vSLTERDSC  char(30) output, /*Territory Description <Optional>*/
@I_vSLPRSNID  char(15) output, /*Salesperson ID <Optional>*/
@I_vSTMGRFNM  char(15) output, /*Sales Terr Managers First Name <Optional>*/
@I_vSTMGRMNM  char(15) output, /*Sales Terr Managers Middle Name <Optional>*/
@I_vSTMGRLNM  char(20) output, /*Sales Terr Managers Last Name <Optional>*/
@I_vCOUNTRY  char(60) output,  /*Country <Optional>*/
@I_vCOSTTODT  numeric(19,5) output, /*Cost to Date <Optional>*/
@I_vTTLCOMTD  numeric(19,5) output, /*Total Commissions to Date <Optional>*/
@I_vTTLCOMLY  numeric(19,5) output, /*Total Commissions Last Year <Optional>*/
@I_vNCOMSLYR  numeric(19,5) output, /*Non-Comm Sales Last Year <Optional>*/
@I_vCOMSLLYR  numeric(19,5) output, /*Comm Sales Last Year <Optional>*/
@I_vCSTLSTYR  numeric(19,5) output, /*Cost Last Year <Optional>*/
@I_vCOMSLTDT  numeric(19,5) output, /*Commissioned Sales To Date <Optional>*/
@I_vNCOMSLTD  numeric(19,5) output, /*Non-Comm Sales To Date <Optional>*/
@I_vKPCALHST  tinyint output,/*Keep Calendar History - 0=No 1=Yes <Optional>*/
@I_vKPERHIST  tinyint output,/*Keep Period History - 0=No 1=Yes <Optional>*/
@I_vMODIFDT datetime output, /*Modified Date <Optional>*/
@I_vCREATDDT datetime output,  /*Create Date <Optional>*/
@I_vUSRDEFND1  char(50) output, /*User Defined field-developer use only*/
@I_vUSRDEFND2  char(50) output, /*User Defined field-developer use only*/
@I_vUSRDEFND3  char(50) output, /*User Defined field-developer use only*/
@I_vUSRDEFND4  varchar(8000) output, /*User Defined field-developer use only*/
@I_vUSRDEFND5  varchar(8000) output, /*User Defined field-developer use only */
@O_iErrorState int output,/* Return value: 0=No Errors, 1=Error Occurred*/
@oErrString  varchar(255) output /* Return Error Code List*/
  
as
  
set nocount on
  
select @O_iErrorState = 0
  
/* Create Custom Business Logic */
  
set @I_vSLTERDSC = 'Created by eConnect'
  
/* End Create Custom Business Logic */
  
return (@O_iErrorState)
go
  
grant execute on dbo.taCreateTerritoryPre to DYNGRP
go
  
/* End_Procs taCreateTerritoryPre */

 

 

Add a Comment



Not Subscribed. You will not receive emails on article changes or comment additions

Comments

body header
No records to display.