I’m looking for sample code for including branch office information while retrieving invoice data.
Up until now we retrieved all invoicedata for import to another software package.
For the additional environemnt Quantify was setup using two branches for 2 locations/subsidiaries. We would like to differentiate the import in the other software package for these two branches as well. So:
Invoice of a jobsite in branch 1 -> Invoice in other software with category A.
Invoice of a jobsite in branch 2 -> Invoice in other software with category B.
How to find the branch ID or name while retrieving invoice data?
At the moment we retrieve the invoice data like this:
Private Function InvoicesFromQuantify() As Boolean
Dim dblPrice As Double = 0
Dim bOk As Boolean = True
'Dim InvoiceId As Guid
Dim objInvoiceList As InvoiceList = InvoiceList.GetInvoiceList(InvoiceExportStatus.NotExported)
Dim Branch As JobSiteList = JobSiteList.GetJobSiteList(Branch As jobsitelist)
'Get immediate parent Branch or Laydown Yard
For Each invoiceitem As InvoiceListItem In objInvoiceList
Dim inv As Invoice = Invoice.GetInvoice(invoiceitem.InvoiceID, False)
If inv.Customer.AccountingID.Trim.Length > 0 Then
If inv.InvoiceRentProducts.Count > 0 Or inv.InvoiceUnitPrices.Count > 0 Then
'Header eerst, debnr ophalen a.d.h.v. id
strSQL = "Execute _sp_Quantify_InvoiceHeader_Add '" & inv.InvoiceNumber & "','" & inv.InvoiceID.ToString.Trim & "','" & inv.Customer.AccountingID.Trim & "','" & inv.InvoiceDateTime.ToString("yyyy-MM-dd") & "','R'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceHeader_Add (for Renatal and UnitPrices : " & cs.strError)
End If
'Rental regels
If inv.InvoiceRentProducts.Count > 0 Then
For Each regel As InvoiceRentProduct In inv.InvoiceRentProducts
If regel.LineTypeText = "Rent" And regel.Then Then
'Rental items
dblPrice = 0
dblPrice = regel.TotalRent ' ((1 + DateDiff(DateInterval.Day, DateTime.Parse(regel.StartDate), DateTime.Parse(regel.EndDate))) / regel.RateDays) * regel.RentRate
strSQL = "Execute _sp_Quantify_InvoiceLine_Add '" & inv.InvoiceNumber & "'," & regel.Quantity.ToString.Replace(",", ".") & ",'" & regel.Description.Trim.Replace("'", "''") & "','" & regel.PartNumber.Trim & "'," & dblPrice.ToString.Replace(",", ".") & ",'20'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceLine_Add (for rental) : " & cs.strError)
End If
ElseIf regel.LineTypeText = "Credit" Then
'Credit on Rental items (e.g. return)
dblPrice = 0
dblPrice = regel.TotalRent ' ((1 + DateDiff(DateInterval.Day, DateTime.Parse(regel.StartDate), DateTime.Parse(regel.EndDate))) / regel.RateDays) * regel.RentRate
strSQL = "Execute _sp_Quantify_InvoiceLine_Add '" & inv.InvoiceNumber & "'," & regel.Quantity.ToString.Replace(",", ".") * -1 & ",'" & regel.Description.Trim.Replace("'", "''") & "','" & regel.PartNumber.Trim & "'," & dblPrice.ToString.Replace(",", ".") & ",'20'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceLine_Add (for rental) : " & cs.strError)
End If
End If
Next
End If
'Unit prices (kortingen ?)
If inv.InvoiceUnitPrices.Count > 0 Then
For Each regel As InvoiceUnitPrice In inv.InvoiceUnitPrices
strSQL = "Execute _sp_Quantify_InvoiceLine_Add '" & inv.InvoiceNumber & "'," & regel.NumberOfUnits.ToString.Replace(",", ".") & ",'" & regel.Description.Trim.Replace("'", "''") & "','X00001016'," & regel.InvoicePricePerUnit.ToString.Replace(",", ".") & ",'20'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceLine_Add (for unit prices) : " & cs.strError)
End If
Next
End If
End If
'Product Charges, verkoop
If inv.InvoiceProductCharges.Count > 0 Then
'Header eerst, debnr ophalen a.d.h.v. id
strSQL = "Execute _sp_Quantify_InvoiceHeader_Add 'V" & inv.InvoiceNumber & "','" & inv.InvoiceID.ToString.Trim & "','" & inv.Customer.AccountingID.Trim & "','" & inv.InvoiceDateTime.ToString("yyyy-MM-dd") & "','S'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceHeader_Add (for product charges) : " & cs.strError)
End If
For Each regel As InvoiceProductCharge In inv.InvoiceProductCharges
strSQL = "Execute _sp_Quantify_InvoiceLine_Add 'V" & inv.InvoiceNumber & "'," & regel.Quantity.ToString.Replace(",", ".") & ",'" & regel.Description.Trim.Replace("'", "''") & "','" & regel.PartNumber.Trim & "'," & regel.Charge.ToString.Replace(",", ".") & ",'" & If(regel.InvoiceProductChargeTypeText = "Consumable Sell" Or regel.InvoiceProductChargeTypeText = "Sell Consumable", "30", "20") & "'"
If Not cs.SQLExecuteQuery(My.Settings.GlobeServer.Trim, My.Settings.GlobeDatabase, strSQL) Then
'Melden dat er iets fout is gegaan..
WriteEventLogEntry(cs.strError, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on _sp_Quantify_InvoiceLine_Add (for product charges) : " & cs.strError)
End If
Next
End If
inv.InvoiceExportStatus = InvoiceExportStatus.Exported
'Opslaan
Try
If inv.IsValid Then
inv.Save()
Else
For Each rul As Avontus.Core.Validation.BrokenRule In inv.BrokenRulesCollection
WriteEventLogEntry(rul.Description, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on flagging Invoice as exported : " & rul.Description)
Next
End If
Catch ex As Exception
WriteEventLogEntry(ex.Message.ToString.Trim, EventLogEntryType.Error)
bMail = True
MailLogEntry("Error on flagging Invoice as exported : " & ex.Message.Trim)
End Try
Else
'Geen Accounting id gevonden
WriteEventLogEntry("Invoice : " & inv.InvoiceNumber.Trim & ",client " & inv.Customer.PartnerNumber.Trim & " ,has no Exact Debtor linked (AcountingID on the client in Quantify)", EventLogEntryType.Error)
bMail = True
MailLogEntry("Invoice : " & inv.InvoiceNumber.Trim & ",client " & inv.Customer.PartnerNumber.Trim & " ,has no Exact Debtor linked (AcountingID on the client in Quantify)")
End If
Next
Return bOk
End Function