eConnect – BRBankTransactionType – increase and decrease adjustment – 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
Imports System.Data.SqlClient
'  
Public Class Form1
    Public Sub eConnectSend(DR As DataRow, CMTrxType As Int16)
        Dim xmldocument As String
        Dim sConnectionString As String
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\test\xmlfile.xml ", DR, CMTrxType)
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\test\xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = "data source=sanjayvm-pc;initial catalog=sanjay;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, DR As DataRow, CMTrxType As Int16)
        'ACTNUMBR_1 ACTNUMBR_2 JRNENTRY SOURCDOC TRXDATE DEBITAMT  TRXSORCE  GPGL  CHECKBOOKID DT COMPANY
        Try
            Dim econnect As New eConnectType
            Dim dep As New BRBankTransactionType
            Dim dh As New taBRBankTransactionHeader
            With dh
                .Option = 1 'Enter Transaction
                .CMTrxType = CMTrxType
                .RcpType = 1 'Check
                .TRXDATE = CStr(DR("TRXDATE"))
                .CHEKBKID = CStr(DR("CHECKBOOKID"))
                .CMTrxNum = "JRN:" & CStr(DR("JRNENTRY")) & "-Store:" & Trim(CStr(DR("ACTNUMBR_2")))
                .paidtorcvdfrom = CStr(DR("SOURCDOC")) & "-" & CStr(DR("JRNENTRY"))
                .paidtorcvdfrom = Trim(CStr(DR("ACTDESCR")))
                .DSCRIPTN = CStr(DR("TRXSORCE"))
                .TRXAMNT = DR("DEBITAMT") + DR("CRDTAMNT")
            End With
            dep.taBRBankTransactionHeader = dh
            Dim DL(0) As taBRBankTransactionDist_ItemsTaBRBankTransactionDist
            Dim DL1 As New taBRBankTransactionDist_ItemsTaBRBankTransactionDist
            With DL1
                .Option = 1 'Enter Transaction
                .ACTNUMST = Trim(CStr(DR("ACTNUMBR_1"))) & "-" & Trim(CStr(DR("ACTNUMBR_2")))
                .DEBITAMT = DR("CRDTAMNT")
                .CRDTAMNT =DR("DEBITAMT")
                .DistRef = "DistRef"
            End With
            DL(0) = DL1
            dep.taBRBankTransactionDist_Items = DL
            ReDim Preserve econnect.BRBankTransactionType(0)
            econnect.BRBankTransactionType(0) = dep
            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
        Dim SQLStr As String = "Server=localhost;Database=sanjayInterFace;Integrated Security=SSPI;"
        Dim SQLConn As SqlConnection = New SqlConnection(SQLStr)
        Dim CMTrxType As Int16
        SQLConn.Open()
        Dim cmd As SqlCommand = New SqlCommand("GetDeposit", SQLConn)
        CMTrxType = 5 'increase adjustment
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@COMPANY", "100")
        cmd.Parameters.AddWithValue("@DT", "2016-05-05 00:00:00")
        Dim adapter As New SqlDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300
        Dim DS As New DataSet
        adapter.Fill(DS)
        For Each DR As DataRow In DS.Tables(0).Rows
            eConnectSend(DR, CMTrxType)
        Next
        adapter.Dispose()

        CMTrxType = 4 'decrease adjustment
        cmd = New SqlCommand("GetWithdraw", SQLConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@COMPANY", "100")
        cmd.Parameters.AddWithValue("@DT", "2016-05-05 00:00:00")
        adapter = New SqlDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300
        DS = New DataSet
        adapter.Fill(DS)
        SQLConn.Close()
        For Each DR As DataRow In DS.Tables(0).Rows
            eConnectSend(DR, CMTrxType)
        Next
        adapter.Dispose()
    End Sub
End Class
CREATE procedure GetWithdraw @COMPANY NVARCHAR(10)
	,@DT DATETIME
AS
BEGIN
SELECT			 [SANJAY].dbo.GL00105.ACTNUMBR_1 
				,[SANJAY].dbo.GL00105.ACTNUMBR_2 
				,[SANJAY].dbo.GL00100.ACTDESCR 
				,[SANJAY].dbo.GL20000.JRNENTRY 
				,[SANJAY].dbo.GL20000.SOURCDOC 
				,[SANJAY].dbo.GL20000.TRXDATE  
				,[SANJAY].dbo.GL20000.DEBITAMT 
				,[SANJAY].dbo.GL20000.CRDTAMNT
				,[SANJAY].dbo.GL20000.TRXSORCE
				,GPGL = (
					SELECT TOP 1 GPGL  FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY  AND VASTGL =  [SANJAY].dbo.GL00105.ACTNUMBR_1
				)
				,CHECKBOOKID = (
					SELECT TOP 1 CHECKBOOKID  FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY  AND VASTGL =  [SANJAY].dbo.GL00105.ACTNUMBR_1
				)
				,DT = @DT
				,COMPANY = @COMPANY
FROM            [SANJAY].dbo.GL00100  WITH (NOLOCK)  INNER JOIN
                [SANJAY].dbo.GL00105  WITH (NOLOCK)  ON [SANJAY].dbo.GL00100.ACTINDX = [SANJAY].dbo.GL00105.ACTINDX INNER JOIN
                [SANJAY].dbo.GL20000  WITH (NOLOCK)  ON [SANJAY].dbo.GL00100.ACTINDX = [SANJAY].dbo.GL20000.ACTINDX
WHERE			[SANJAY].dbo.GL20000.JRNENTRY IN (
												SELECT		[SANJAY].dbo.GL20000.JRNENTRY
												FROM        [SANJAY].dbo.GL20000 WITH (NOLOCK) INNER JOIN	
															[SANJAY].dbo.GL00105 WITH (NOLOCK) ON [SANJAY].dbo.GL20000.ACTINDX = [SANJAY].dbo.GL00105.ACTINDX
												WHERE		([SANJAY].dbo.GL20000.TRXDATE= CONVERT(DATETIME, @DT, 102)) AND [SANJAY].dbo.GL00105.ACTNUMBR_1 IN (SELECT VASTGL FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY)
												GROUP BY	[SANJAY].dbo.GL20000.SOURCDOC, [SANJAY].dbo.GL20000.JRNENTRY, [SANJAY].dbo.GL20000.TRXDATE
												HAVING		([SANJAY].dbo.GL20000.SOURCDOC = 'GPGateway')
				) AND  [SANJAY].dbo.GL00105.ACTNUMBR_1 IN ( SELECT VASTGL FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY) AND CRDTAMNT > 0

END


* FOR GetDeposit change where CRDTAMT = 0

 

 

Cheers!

A get/change operation on table ‘pm1099PeriodDtl’ failed accessing SQL Data – Invalid column name ‘TEN99FATCAFILEREQ’

This message occurred during check batch posting process after the GP was upgraded from GP 2010 to GP2015.  There were no upgrade errors.  Looks like the PM00204 table was not upgraded, hence was missing the field.    Adopted below process to add the new field :

1.Backup your company database

2. Run this query against company database

Select * into PM00204_Safe from PM00204

3. Navigate to Dynamics GP >> Maintenance >> SQL

4. Select company database and select purchase 1099 period detail table.

5. Tick mark drop table and drop auto procedure and process

6. Tick mark create table and create auto procedure and process

7. Run this query against the company database

insert into [dbo].[PM00204]
      ([VENDORID],[TEN99TYPE],[YEAR1],[PERIODID],[TEN99BOXNUMBER],[TEN99AMNT],[TEN99FRNORUSDTL],[TEN99STATECD],[TEN99STATIDNUM],[TEN99TAXEXMTCUSIPNUM],[TEN99DIRSALECB],[TEN99STATNMBR]) 
select [VENDORID],[TEN99TYPE],[YEAR1],[PERIODID],[TEN99BOXNUMBER],[TEN99AMNT],[TEN99FRNORUSDTL],[TEN99STATECD],[TEN99STATIDNUM],[TEN99TAXEXMTCUSIPNUM],[TEN99DIRSALECB],[TEN99STATNMBR] from PM00204_SAFE

Cheers!
Sanjay

 

Create Journal Entries using e Connect Store Procs

Obtain Next Journal Number:
USE [GP Company Database]
GO
DECLARE @return_value int,
@O_vJournalEntryNumber char(13),
@O_iErrorState int
EXEC @return_value = [dbo].[taGetNextJournalEntry]
@O_vJournalEntryNumber = @O_vJournalEntryNumber OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT
SELECT @O_vJournalEntryNumber as N’@O_vJournalEntryNumber’,
@O_iErrorState as N’@O_iErrorState’
SELECT ‘Return Value’ = @return_value >> This is the Journal number which should be used below
GO
*****************************************************************************************
Create GL Batch Record
USE [GP Company Database]
GO
DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionHeaderInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344, >> As queried above
@I_vREFRENCE = N’reference’,
@I_vTRXDATE = N’7/6/2015′,
@I_vTRXTYPE = 0,
@I_vSERIES = 2,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value

GO
*****************************************************************************************
Create Debit Line of Journal:
USE [GP Company Database]
GO

DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionLineInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344,
@I_vACTINDX = 101,
@I_vCRDTAMNT = 0,
@I_vDEBITAMT = 100,
@I_vDSCRIPTN = N’desc’,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value
*****************************************************************************************
Create Credit Line of Journal
USE [GP Company Database]
GO

DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionLineInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344,
@I_vACTINDX = 100,
@I_vCRDTAMNT = 100,
@I_vDEBITAMT = 0,
@I_vDSCRIPTN = N’desc’,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value
*****************************************************************************************
This should create a valid journal entry in Dynamics GP.

Check stored proc for full parameter list.

Cheers!
Sanjay

Create SQL CLR Project in VB

Create SQL CLR Project in VB:

File -> New Project
SQL Server
OK
At Project properties select SQLCLR Tab
Change Lanugage to VB
Add new items
SQL CLR VB -> SQL CLR VB User Defined Functions

Cheers!
Sanjay