Mike,
I have given up on using the BOProcessComplete.
Have just discovered the ManualBackorder Release breakouts and am trying the Saved Completed breakout. In hindsight, should've used this from the outset.
Seems to be OK so far but my logic is failing when I want to write one file per InvoiceNo, with all the lines on that order.
If I could just get your help during the DoWhile Loop as the rows are read.
I need to check for a new InvoiceNo & write the HeaderRow once, then TextOut all the lines for that InvoiceNo and write the file. Each line has a incrementing LineNo starting at 1, per InvoiceNo so restart the LineNo from 1.
Then create a new file with a new filename for the next InvoiceNo.
I also need the InvoiceNo in the Filename.
Here is my code so far. It is only dumping out 1 row even though I have released more. I think this might be because I am assigning FileName and fso in the middle of the Do While. Don't know how else to get the FileName to include the InvoiceNo.
I'd be grateful for your assistance again.
- Code: Select all
Dim HistoryID
Dim InvoiceNo
Dim DBAcc
Dim DBName
Dim Addr1,Addr2,Addr3,Addr4,Postcode
Dim PartNo
Dim ExpDate
Dim Qty
With JiwaDatabaseObject
' Open connection to database
rHwnd = .StatementOpen(.ConnectionRead1, False, "", 100)
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 "
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.InvoiceHistoryID = SO_LineDetails.InvoiceHistoryID "
SQL = SQL & "WHERE(BO_ManualReleaseBatchLines.BatchID = '" & ManualBackOrderReleaseBatchObject.BatchID & "') "
SQL = SQL & "And (BO_ManualReleaseBatchLines.FulfillBackOrderFlag <> 0) And "
SQL = SQL & "(SO_CustomSetting.SettingName = 'CALLTAG')"
If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
.BindMem CInt(rHwnd), 1, vbString
Do While .FetchRow(CInt(rHwnd)) = True
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)
LineNo = 0
FileName = "\\sssql03\TOSEQOS$\AU\SOTest_" & InvoiceNo & ".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
TextOut = Trim(InvoiceNo) & "|" & DBAcc & "|" & DBName & "|" & PartNo & "|" & Qty & "|" & Addr1 & "|" & Addr2 & "|" & Addr3 & "|" & Addr4
TextOut = TextOut & "|" & PostCode & "|" & Country & "|" & CALLTAG & "|" & HistoryID & "|" & SubQty & "|" & Serial & "|" & Expiry
f.WriteLine TextOut
Loop
Else
MsgBox "Error in SQL : " & SQL
End If
.StatementClose CInt(rHwnd)
End With
End Sub
Cheers