Execute e Connect stored procedure directly Example

In this example how to execute e Connect stored procedure taCreateEmployee is demonstrated.

The first step in executing the e Connect stored procedure would be to identify the parameters the stored procedure  will accept and the give out as output.  The easiest way to identify the parameters the stored procedure will accept and output is to right click the stored procedure at SSMS and execute it. execute sp

SQL Server will prompt with all parameters the stored procedure will accept or output and also provide information on  data type, size, and direction.sp params

The other option is to decrypt the stored procedure and modify the stored procedure to identify the parameters.

Once the parameters are identified, execute the stored procedures as follows:

DECLARE  @_iErrorState int    
DECLARE @_ErrString varchar(255) 
exec [dbo].[taCreateEmployee] 
@I_vEMPLOYID ="2050",
@I_vEMPLCLAS ="CLASS",       -- Class should exist in GP
@I_vLASTNAME ="Kumar",
@I_vFRSTNAME ="Sanjay",
@I_vSOCSCNUM ="000000003",
@I_vDEPRTMNT = "IT",         -- Dept should exist in GP       
@I_vJOBTITLE = "ERPADMN",    -- Title should exist in GP
@I_vUpdateIfExists = 1,
@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT  @_iErrorState as errstat , @_ErrString as errstring  --  0 errstat means all ok else errstring will give error #

In above example only selective fields which were required to send is used.

Cheers!
Sanjay

e Connect – taCreateEmployee – vb.net example

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization

Public Class createEmployee
    Public Sub eConnectSend()
        'Serialized XML File         
        Dim xmldocument As String
        'Connection String         
        Dim sConnectionString As String
        'Result         
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\test\xmlfile.xml ")
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\test\xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = "data source=[SQLSERVER];initial catalog=two;integrated security=SSPI; persist security info=False;packet size=4096"
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("done")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String)
        Try
            Dim econnect As New eConnectType
            Dim employee As New UPRCreateEmployeeType
            'employee record
            Dim employeerecord As New taCreateEmployee
            With employeerecord
                .EMPLOYID = "2020"
                .FRSTNAME = "Sanjay"
                .LASTNAME = "Kumar"
                .SOCSCNUM = "000000000"
                .DEPRTMNT = "IT"
                .JOBTITLE = "ERPADM"
                .UpdateIfExists = 1
                .EMPLCLAS = "ADMN"
            End With
            employee.taCreateEmployee = employeerecord
            ReDim Preserve econnect.UPRCreateEmployeeType(0)
            econnect.UPRCreateEmployeeType(0) = employee
            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        eConnectSend()
    End Sub
End Class

Adapted from Microsoft Dynamics GP eConnect Samples installed during eConnect installation. Line breaks are issued to fit script within screen. Remove line breaks.

THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE

taCreateEmployee

Element name Data type Length Required Description
EMPLOYID string 15 Y Employee ID
EMPLCLAS string 15 N Employee class
INACTIVE i4 1 N Inactive: 0=False; 1=True
LASTNAME string 20 Y Last name
FRSTNAME string 15 Y First name
MIDLNAME string 15 N Middle name
EMPLSUFF string 4 N Employee suffix
ADRSCODE string 15 N Address code
ADDRESS1 string 30 N Address code 1
ADDRESS2 string 30 N Address code 2
ADDRESS3 string 30 N Address code 3
CITY string 30 N City
STATE string 29 N State
ZIPCODE string 10 N ZIP Code
COUNTY string 20 N County
COUNTRY string 20 N Country
PHONE1 string 21 N Phone number 1
PHONE2 string 21 N Phone number 2
PHONE3 string 21 N Phone number 3
FAX string 21 N Fax number
SOCSCNUM string 15 Y Social Security number
BRTHDATE datetime 16 N Birth date
GENDER i4 2 N Gender:
1=Male;
2=Female;
3=Not applicable
ETHNORGN i4 2 N Ethnic origin:
1=White;
2=Native American or Alaskan Native;
3=Black or African American;
4=Asian;
5=Hispanic or Latino;
6=Two or more races;
7=Not applicable;
8=Native Hawaiian or Pacific Islander
Calc_Min_Wage_Bal i4 1 N Calculate minimum wage balance:
0=False;
1=True
DIVISIONCODE_I string 6 N Division code; HR option only
DEPRTMNT string 6 Y Department
JOBTITLE string 6 Y Job title/position
SUPERVISORCODE_I string 6 N Supervisor Code
LOCATNID string 15 N Location ID
WCACFPAY i4 2 N Cash account from:
0=Checkbook;
1=Employee
AccountNumber string 75 N Account number
WKHRPRYR i4 2 N Work hours per year
STRTDATE datetime 16 N Start date
DEMPINAC datetime 16 N Date employee inactivated; this field is active if INACTIVE=1
RSNEMPIN string 30 N Reason employee inactivated; this field is active if INACTIVE=1
MINETPAY number 21 N Minimum net pay
SUTASTAT string 2 N SUTA state
WRKRCOMP string 6 N Workers’ compensation code
ATACRVAC i4 1 N Auto accrue vacation: 0=False; 1=True
VACCRAMT number 21 N Vacation accrual amount
VACCRMTH i4 2 N Vacation accrual method: 0=Hours worked; 1=Set hours
VACAPRYR number 21 N Vacation hours per year
VACAVLBL number 21 N Vacation available
WRNVCNFLSBLWZR i4 1 N Warn if vacation falls below zero:
0=False;
1=True
ATACRSTM i4 1 N Auto accrue sick time:
0=False
1=True
STMACMTH i4 2 N Sick Time Accrual Method:
0=Hours worked
1=Set hours
SKTMACAM number 21 N Sick time accrual amount
SIKTIMAV number 21 N Sick time available
SKTMHPYR number 21 N Sick time hours per year
WRNSTFLSBLWZR i4 1 N Warn sick time falls below zero
0=False
1=True
USERDEF1 string 20 N User defined 1
USERDEF2 string 20 N User defined 2
EMPLOYMENTTYPE i4 2 N Type of employment:
1=Full-time regular;
2=Full-time temporary;
3=Part-time regular;
4=Part-time temporary;
5=Intern;
6=Other
MARITALSTATUS i4 2 N Marital status:
1=Married;
2=Single;
3=Not applicable
BENADJDATE datetime 16 N Benefit adjustment date
LASTDAYWORKED_I datetime 16 N Last day worked
BIRTHDAY i4 2 N Birthday
BIRTHMONTH i4 2 N Birth month:
1=January;
2=February;
3=March;
4=April;
5=May;
6=June;
7=July;
8=August;
9=September;
10=October;
11=November;
12=December
SPOUSE string 15 N Spouse
SPOUSESSN string 15 N Spouse Social Security number
NICKNAME string 20 N Nickname
ALTERNATENAME string 20 N Alternate name
STATUSCD string 15 N Status code
HRSTATUS i4 2 N HR Status; HR option only:
1=Active;
2=Family leave;
3=Leave of absence;
4=Maternity;
5=Retired;
6=Separated;
7=Suspended;
8=Terminated;
9=Other
DATEOFLASTREVIEW_I datetime 16 N Date of last review; HR option only
DATEOFNEXTREVIEW_I datetime 16 N Date of next review; HR option only
BENEFITEXPIRE_I datetime 16 N Benefit expire
HANDICAPPED i4 1 N Handicapped; HR option only:
0=False;
1=True
VETERAN i4 1 N Veteran; HR option only:
0=False;
1=True
VIETNAMVETERAN i4 1 N Vietnam veteran; HR option only:
0=False;
1=True
DISABLEDVETERAN i4 1 N Disabled veteran; HR option only:
0=False;
1=True
UNIONEMPLOYEE i4 1 N Union employee; HR option only:
0=False;
1=True
SMOKER_I i4 1 N Smoker; HR option only:
0=False;
1=True
CITIZEN i4 1 N Citizen; HR option only:
0=False;
1=True
VERIFIED i4 1 N Verified; HR option only:
0=False;
1=True
I9RENEW datetime 16 N I9 renew; HR option only
Primary_Pay_Record string 6 N Primary pay record; HR option only
CHANGEBY_I string 15 N Change by; HR option only
CHANGEDATE_I datetime 16 N Change date; HR option only
UNIONCD string 6 N Union code; HR option only
RATECLSS string 15 N Rate class; HR option only
FEDCLSSCD string 10 N Federal class; HR option only
OTHERVET i4 1 N Other veterans; HR option only
Military_Discharge_Date datetime 16 N Military discharge date; HR option only
DefaultFromClass i4 1 N Default information from class:
0=False;
1=True
UpdateIfExists i4 1 N Flag to allow customer data to be updated if it exists:
0=No;
1=Yes
RequesterTrx i4 2 N Requester transaction
0=False;
1=True (if True, it populates the requester shadow table)
USRDEFND1 string 50 N User-defined field—developer use only
USRDEFND2 string 50 N User-defined field—developer use only
USRDEFND3 string 50 N User-defined field—developer use only
USRDEFND4 string 8000 N User-defined field—developer use only
USRDEFND5 string 8000 N User-defined field—developer use only

Extracted from eConnect Programmers reference Guide for immediate reference.

XML File created

<?xml version="1.0" encoding="UTF-8"?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <UPRCreateEmployeeType>
      <eConnectProcessInfo xsi:nil="true" />
      <taRequesterTrxDisabler_Items xsi:nil="true" />
      <taCreateEmployee>
         <EMPLOYID>2020</EMPLOYID>
         <EMPLCLAS>ADMN</EMPLCLAS>
         <LASTNAME>Kumar</LASTNAME>
         <FRSTNAME>Sanjay</FRSTNAME>
         <SOCSCNUM>000000000</SOCSCNUM>
         <DEPRTMNT>IT</DEPRTMNT>
         <JOBTITLE>ERPADM</JOBTITLE>
      </taCreateEmployee>
      <taCreateInternetAddresses_Items xsi:nil="true" />
   </UPRCreateEmployeeType>
</eConnect>

Cheers!

Sanjay