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. To customize a pre or post stored procedure, complete the following steps:
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\Microsoft Dynamics\eConnect 11\Custom Procedures. 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.
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.
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:
/* 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 */