Compartir a través de

Using pre and post stored procedures

  • Artículo

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:

  1. 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\Microsoft Dynamics\eConnect 12.0\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 12.0\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.

  2. 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.

  1. 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:

/* 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 */