Hi,
This code is working for 1st JE perfectlly but when it will try to add 2nd journal entry it will give me one Error :
-5002 'You cannot post both debit and credit amounts in one row
[JDT1.Debit][line:2]'.
I am using SAP 2005 B PL 42.
below is the code.
when I will again run the code it will work well for 1st JE but for the 2nd JE it will again give the same error.Kindly help me.
Private Sub PostJEForOtherTran(ByVal TCode As String)
Dim oEdit As SAPbouiCOM.EditText
Dim oCmb As SAPbouiCOM.ComboBox
Dim ors, ors1 As SAPbobsCOM.Recordset
Dim selectStr, updateStr As String
Dim err, k, m, NoOfRows As Integer
Dim errMsg, SysDate, LastObjKey, TranType As String
Try
Dim JE As SAPbobsCOM.JournalEntries
JE = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)
selectStr = " select Id,TranId,RDelNo,RdelDate "
selectStr = selectStr & " from JournalEntryHeader "
selectStr = selectStr & " where TranImport = 'False' and TranId='" & TCode & "'"
cmd = New SqlCommand(selectStr, cn)
If cn.State = ConnectionState.Open Then
cn.Close()
End If
cn.Open()
cmd.ExecuteNonQuery()
rdr = cmd.ExecuteReader
While rdr.Read
'ors = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
'ors.DoQuery("Select convert(nvarchar, DATEPART(dd, GETDATE())) + '/' + convert(nvarchar, DATEPART(mm, GETDATE())) + '/' + convert(nvarchar, DATEPART(yy, GETDATE()))")
'SysDate = ConvertToSAPDt(ors.Fields.Item(0).Value)
ors = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
selectStr = "select Name from [@VC_TRNM] where Code = '" & rdr.Item("TranId").ToString & "'"
ors.DoQuery(selectStr)
JE.TaxDate = rdr.Item("RdelDate").ToString
JE.DueDate = rdr.Item("RdelDate").ToString
'JE.VatDate = Now
JE.ReferenceDate = Now 'SysDate
'JE.UserFields.Fields.Item("U_RGRNNo").Value = rdr.Item("RDelNo").ToString
'JE.UserFields.Fields.Item("U_GRNDt").Value = rdr.Item("RGRNDate").ToString
JE.UserFields.Fields.Item("U_SaDocNo").Value = rdr.Item("RDelNo").ToString
'JE.UserFields.Fields.Item("U_VBillD").Value = rdr.Item("VBDate").ToString
'JE.UserFields.Fields.Item("U_VATTot").Value = rdr.Item("VATTot").ToString
'JE.UserFields.Fields.Item("U_CSTTot").Value = rdr.Item("CSTTot").ToString
'JE.UserFields.Fields.Item("U_PVTot").Value = rdr.Item("PrvDTot").ToString
'JE.UserFields.Fields.Item("U_PsVTot").Value = rdr.Item("PsVDTot").ToString
'JE.UserFields.Fields.Item("U_OthTot").Value = rdr.Item("OthTot").ToString
'JE.UserFields.Fields.Item("U_PONo").Value = rdr.Item("PONo").ToString
JE.Memo = ors.Fields.Item(0).Value
TranType = ors.Fields.Item(0).Value
selectStr = " select AcctCode,AcctName,DebCr,DAmt,CAmt,PCode from JournalEntryRow "
selectStr = selectStr & " where TranImport = 'False' and id = '" & rdr.Item(0).ToString & "'"
cmd1 = New SqlCommand(selectStr, cn1)
If cn1.State = ConnectionState.Open Then
cn1.Close()
End If
cn1.Open()
cmd1.ExecuteNonQuery()
'cmd1.
rdr1 = cmd1.ExecuteReader
'rdr1.
selectStr = " select Count(*) as [RCount] from JournalEntryRow "
selectStr = selectStr & " where TranImport = 'False' and id = '" & rdr.Item(0).ToString & "'"
cmd4 = New SqlCommand(selectStr, cn4)
If cn4.State = ConnectionState.Open Then
cn4.Close()
End If
cn4.Open()
rdr4 = cmd4.ExecuteReader()
While rdr4.Read
NoOfRows = Convert.ToInt16(rdr4.Item("RCount").ToString())
End While
k = 0
While rdr1.Read
'JE.TaxDate = Now
ors = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
selectStr = "select U_SAcc,U_SADes from [@VC_ACCM] where U_RTCe = '" & rdr1.Item("AcctCode").ToString & "'"
ors.DoQuery(selectStr)
If ors.RecordCount > 0 Then
JE.Lines.AccountCode = ors.Fields.Item(0).Value
JE.Lines.ShortName = ors.Fields.Item(0).Value
End If
ors = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
selectStr = "select U_SPCode from [@VC_PRJM] where cast(U_RPCode as nvarchar(50)) = '" & rdr1.Item("PCode").ToString & "'"
ors.DoQuery(selectStr)
If ors.RecordCount > 0 Then
JE.Lines.ProjectCode = ors.Fields.Item(0).Value
End If
'JE.Lines.ContraAccount = rdr1.Item("AcctName").ToString
'JE.Lines.ProjectCode = rdr1.Item("PCode").ToString
If rdr1.Item("DebCr").ToString = "1" Then
JE.Lines.Debit = rdr1.Item("DAmt").ToString
'SBO_Application.MessageBox("Debit Amount : " & rdr1.Item("DAmt").ToString & " K : " & k)
ElseIf rdr1.Item("DebCr").ToString = "2" Then
JE.Lines.Credit = rdr1.Item("CAmt").ToString
'SBO_Application.MessageBox("Credit Amount : " & rdr1.Item("CAmt").ToString & " K : " & k)
End If
'if rdr.
'If k = NoOfRows - 1 Then
'Else
k = k + 1
Call JE.Lines.Add()
'Call JE.Lines.SetCurrentLine(k)
'End If
End While
'If (0 <> JE.Add()) Then
' MsgBox("failed to add a journal entry")
'End If
errMsg = ""
m = JE.Add
Dim FPath As String
Dim SysDate1 As String
ors = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
ors.DoQuery("Select convert(nvarchar, DATEPART(dd, GETDATE())) + '/' + convert(nvarchar, DATEPART(mm, GETDATE())) + '/' + convert(nvarchar, DATEPART(yy, GETDATE()))")
SysDate1 = ConvertToSAPDt(ors.Fields.Item(0).Value)
If m = 0 Then
LastObjKey = oCompany.GetNewObjectKey()
If Directory.Exists("C:\JCRetail\Log\SalesrelatedJE\") = False Then
Directory.CreateDirectory("C:\JCRetail\Log\SalesrelatedJE\").Create()
End If
If System.IO.File.Exists("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt") = True Then
'System.IO.File.Delete("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt")
Dim w As StreamWriter
w = File.AppendText("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt")
w.WriteLine("JE No. " & LastObjKey & " For " & TranType & " Imported Successfully")
w.Close()
Else
FPath = "C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt"
objWOtherTJE = New System.IO.StreamWriter(FPath)
objWOtherTJE.WriteLine("JE No. " & LastObjKey & " For " & TranType & " Imported Successfully")
objWOtherTJE.Close()
End If
updateStr = "update JournalEntryHeader set TranImport = 'True' where "
updateStr = updateStr & " Id = '" & rdr.Item("Id").ToString & "'"
cmd2 = New SqlCommand(updateStr, cn2)
If cn2.State = ConnectionState.Open Then
cn2.Close()
End If
cn2.Open()
cmd2.ExecuteNonQuery()
Else
Call oCompany.GetLastError(err, errMsg)
If System.IO.File.Exists("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt") = True Then
'System.IO.File.Delete("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt")
Dim w As StreamWriter
w = File.AppendText("C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt")
w.WriteLine(errMsg)
w.Close()
Else
FPath = "C:\JCRetail\Log\SalesrelatedJE\" & SysDate1 & ".txt"
objWOtherTJE = New System.IO.StreamWriter(FPath)
objWOtherTJE.WriteLine(errMsg)
objWOtherTJE.Close()
End If
End If
'Check Error
'MsgBox("Found error:" + Str(err) + "," + errMsg)
End While
SBO_Application.SetStatusBarMessage("Sales Transaction JE Add Successfully, See the Log File For Details", SAPbouiCOM.BoMessageTime.bmt_Short, False)
Catch ex As Exception
SBO_Application.MessageBox(ex.Message)
End Try
End Sub