I am having trouble with this breakout on the Save Completed breakout of manual backorder release.
My code is supposed to write a file for each invoice getting released.
The problem is that it only writes one PartNo for each invoice. There may be several lines getting released but I have been trying to adjust the code to write multiple lines for each invoice. I am not having any luck.
After the full file has been created with all lines, then I am creating a copy of the file to another folder.
As per usual, this is quite urgent as I thought it was working onsite fine. If you could check my code and advise where it is incorrect?
- Code: Select all
' This breakout creates 1 file per invoiceno
Dim HistoryID
Dim InvoiceNo
Dim DBAcc
Dim DBName
Dim Addr1,Addr2,Addr3,Addr4,Postcode
Dim PartNo
Dim ExpDate
Dim Qty
Dim FirstLine
FirstLine = 0
With JiwaDatabaseObject
' Open connection to database
rHwnd = .StatementOpen(.ConnectionRead1, False, "", 200)
SQL = "SELECT BO_ManualReleaseBatchLines.InvoiceNo, BO_ManualReleaseBatchLines.DebtorAccountNo, BO_ManualReleaseBatchLines.DebtorName, "
SQL = SQL & "BO_ManualReleaseBatchLines.PartNo, BO_ManualReleaseBatchLines.BackOrderQuantity, BO_ManualReleaseBatchLines.ExpectedDeliveryDate, "
SQL = SQL & "SO_History.DelAddress1, SO_History.DelAddress2, SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, "
SQL = SQL & "SO_History.DeliveryAddressCountry, SO_CustomSettingValues.Contents AS CALLTAG, SO_LineDetails.Quantity, SO_LineDetails.SerialNo, "
SQL = SQL & "SO_LineDetails.ExpiryDate, SO_History.InvoiceHistoryID,SO_History.HistoryNo,SO_Main.InvoiceID "
SQL = SQL & "FROM BO_ManualReleaseBatchLines INNER Join "
SQL = SQL & "SO_CustomSettingValues ON BO_ManualReleaseBatchLines.InvoiceID = SO_CustomSettingValues.InvoiceID INNER Join "
SQL = SQL & "SO_CustomSetting ON SO_CustomSettingValues.SettingID = SO_CustomSetting.SettingID INNER Join "
SQL = SQL & "SO_Main ON BO_ManualReleaseBatchLines.InvoiceID = SO_Main.InvoiceID INNER Join "
SQL = SQL & "SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID And SO_Main.CurrentHistoryNo = SO_History.HistoryNo INNER Join "
SQL = SQL & "SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID And "
SQL = SQL & "BO_ManualReleaseBatchLines.InventoryID = SO_Lines.InventoryID INNER Join "
SQL = SQL & "SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID "
SQL = SQL & "WHERE(BO_ManualReleaseBatchLines.BatchID = '" & ManualBackOrderReleaseBatchObject.BatchID & "') "
SQL = SQL & "And (BO_ManualReleaseBatchLines.FulfillBackOrderFlag <> 0) And "
SQL = SQL & "(SO_CustomSetting.SettingName = 'CALLTAG') ORDER BY BO_ManualReleaseBatchLines.InvoiceNo"
' FileName = "\\sssql03\TOSEQOS$\AU\SOTest_SQL.CSV"
' Set fso = CreateObject("Scripting.FileSystemObject")
' Set f = fso.CreateTextFile(FileName, True)
' f.WriteLine SQL
If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
.BindMem CInt(rHwnd), 1, vbString
Do While .FetchRow(CInt(rHwnd)) = True
FirstLine = FirstLine + 1
InvoiceNo = .GetData(CInt(rHwnd), 1)
DBAcc = .GetData(CInt(rHwnd), 2)
DBName = .GetData(CInt(rHwnd), 3)
PartNo = .GetData(CInt(rHwnd), 4)
Qty = .GetData(CInt(rHwnd), 5)
ExpDate = .GetData(CInt(rHwnd), 6)
Addr1 = .GetData(CInt(rHwnd), 7)
Addr2 = .GetData(CInt(rHwnd), 8)
Addr3 = .GetData(CInt(rHwnd), 9)
Addr4 = .GetData(CInt(rHwnd), 10)
PostCode = .GetData(CInt(rHwnd), 11)
Country = .GetData(CInt(rHwnd), 12)
CALLTAG = .GetData(CInt(rHwnd), 13)
SubQty = .GetData(CInt(rHwnd), 14)
Serial = .GetData(CInt(rHwnd), 15)
Expiry = .GetData(CInt(rHwnd), 16)
HistoryID = .GetData(CInt(rHwnd), 17)
HistoryNo = .GetData(CInt(rHwnd), 18)
InvoiceID = .GetData(CInt(rHwnd), 19)
HistNo = Right("0" & HistoryNo,2)
MsgBox PreviousInvoiceNo & " : " & InvoiceNo
If PreviousInvoiceNo <> Trim(InvoiceNo) Then
FileName = "\\sssql03\TOSEQOS$\AU\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.CreateTextFile(FileName, True)
HeaderRow = "InvoiceNo|CustomerCode|CustomerName|LineNo|PartNo|QtyOrdered|DelDate|DelAddr1|DelAddr2|City|State|DelPostcode|Country|CALLTAG|ID|SubQty|SerialNo|ExpiryDate"
f.WriteLine HeaderRow
LineNo = 0
End If
LineNo = LineNo + 1
TextOut = Trim(InvoiceNo) & "-" & HistNo & "|" & DBAcc & "|" & DBName & "|" & LineNo & "|" & PartNo & "|" & Qty & "|" & ExpDate & "|" & Addr1 & "|" & Addr2 & "|" & Addr3 & "|" & Addr4
TextOut = TextOut & "|" & PostCode & "|" & Country & "|" & CALLTAG & "|" & HistoryID & "|" & SubQty & "|" & Serial & "|" & Expiry
f.WriteLine TextOut
If PreviousInvoiceNo <> Trim(InvoiceNo) And FirstLine > 1 Then
Set Newf = fso.GetFile(FileName)
Newf.Copy("\\sssql03\TOSEQOS$\AU\SO\Archive\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV")
End If
PreviousInvoiceNo = Trim(InvoiceNo)
Loop
Else
MsgBox "Error in SQL : " & SQL
End If
.StatementClose CInt(rHwnd)
End With
f.close
End Sub
Sorry for the urgency - must get this sorted asap.
Cheers
Danny