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