Electronic Bank Reconcile – Dates won’t import properly

06/17/2016 – Today was setting up configuration for electronic bank reconcile and every time  the download of the statement was done the date imported into the system was either 1/1/1900 or 12/31/9999.  After several un successful attempts to fix the issue finally  the eureka moment…  The excel source file had a hidden column and the copy paste to text file added an invisible column which shifted all columns to the right messing the mapping!  The lesson learnt here is, check the source file in text editor …

Cheers!
Sanjay

 

 

Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table The project number is

Hi Sanjay,

I am providing you a copy of our scope agreement for your issue.

Issue Definition:

Customer is getting error message “Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table
The project number is <NONE>” when sending a time sheet with eConnect.
Scope Agreement:

The scope of this case is to advise customer on the error message “Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table The project number is <NONE>” when sending a time sheet with eConnect.

Assessment:
You had a phone call with Mike yesterday and are going to first attempt to mark the option Allow Add Access on the fly per below in your test company.

In Dynamics GP, go to Tools > Setup > Project > Timesheets. Click the Options button. The last line in Timesheet Setup Options is “Allow Add Access on the fly”. Be sure this is check marked and please verify you no longer receive the error.

If you do not agree with the scope defined above, or would like to amend it, please let me know as soon as possible.  Please let me know if you have any questions or concerns in the meantime. I will follow up with you in a few days if I don’t hear back. Have a great afternoon!

Best Regards,

The solution worked – however need to understand the consequence.

Follow ups:

 
1)      When the time sheet with none project was sent access to the none project was to be provided to user.
2)      The only way access to none project can be provided was to let permission be provided on fly.
3)      Because we gave the permission on for access release on fly for none projects, this is having allowing provisioning of access to all projects on the fly which is not what we want.
 
Can we first enable on the fly – get all user access to Non and then take away on the fly – this should solve the problem for now correct ?
Your analysis is correct and I understand in your 3rd remark that they do not want to allow access to on all projects by using the grant access on the fly.  This is actually a known issue (TFS#86337) that is marked to be fixed in the next release of GP16 later this year. 

Cheers!
Sanjay

VAST to GP intergration

VAST staging area:

SELECT     TOP (200) IDENTITYFIELD, COMPANY_NUMBER, GL_NUMBER, TRANSACTION_DATE, DOCUMENT_NUMBER, CUSTOMER_NAME, DEBIT, CREDIT, SOURCE, 
                      GL_TYPE, STATUS, WDATE, IDATE, WRITEROUTINE, CUSTOMER_TYPE, COMPOUND_GL_NUMBER, SEND_TO_THIRDPARTY, JOURNAL_NUM, GUID, COMMENT, 
                      WORKORDER, WORKORDER_LINE_ID, Goodyear_Claim_No, REF_Doc_ID, BIG_Invoice_No, REF_Order_Type, PARENT_GUID, Eod_Status, Eod_DateStamp, 
                      DO_NOT_SEND_VENDOR
FROM         GLTRANSACTION

 

VAST Staging area to GP Resend:

UPDATE GLTRANSACTION SET SEND_TO_THIRDPARTY = 1 WHERE  (TRANSACTION_DATE = CONVERT(DATETIME, '2016-05-05 00:00:00', 102))

Cheers!
Sanjay

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!