Manual Backorder Release, Save Completed

[DannyC]

Mike or Scott,

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

[SBarnes]

Hi Danny

With regards to your phone call yesterday evening to get me to have a look at this, as I said to you I have not used this screen before and on the surface of it the code looks to cover the logic you are trying to create, there are one of two approaches I would use to solve the problem:

1. Write your actual SQL statement out to a text file and then execute it directly again the database to ensure firstly that you are getting all the rows you need from the database that way you will know if the problem is data related or its in the logic writing the file. You may find you either need a left outer join somewhere or you may need to trim join fields in your sql or in the where clause as I have seen space causes problems in the data before.

2. The other option would be to change your logic slightly and use the ManualBackOrderReleaseBatchObject that's handed into the break out, this object has a property called SalesOrders which is a collection of sales orders and each sales order has an invoiceID (I keep a copy of vb 6 installed on my machine with a project with all the jiwa dlls referenced so that I can use the object browser for things like this when I want to see the structure of the object handed into the breakout), you could take the guts of your current code and write a function under the end of the breakout that you could call against each sales order handing in the manual batch id, the invoice id and the database object and restrict the data down to returning from the sql only one order at a time as this would allow you to simplify the code to just write out all rows returned from the sql to each file at a time.

I hope this helps as I know it doesn't immediately fix your problem.

[Mike.Sheen]

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.

Hi Danny,

I'd start with closing the text file when appropriate - I see where you create it, then append to it - and then copy it - but it concerns me that you don't ever seem to close the file (well I see an f.close - but you put it outside your loop!). You should close the file before you try to create a new one for the next invoice.

Move this line

Code: Select all

Set fso = CreateObject("Scripting.FileSystemObject")

to be before your loop
Wrap this line

Code: Select all

Set f = fso.CreateTextFile(FileName, True)

with a check to see if it is nothing, if not close it first - ie:

Code: Select all

If Not f is Nothing Then
    f.close
End If
Set f = fso.CreateTextFile(FileName, True)

Mike

[DannyC]

Thanks Stuart and Mike.

I seem to find whenever the breakout encounters the

Code: Select all

f.Close

it crashes out & doesn't continue the Do While loop. Unsure if this is a bug or a problem in my code.

I am still unclear exactly where I should be putting the check

Code: Select all

If Not f Is Nothing Then
   MsgBox "f is something, so closing file"
   f.close
End If

It makes sense to put it in my second

Code: Select all

   If PreviousInvoiceNo <> Trim(InvoiceNo) Then

check because that is where the file should close & then get copied to an archive folder.
But as mentioned above, as soon as it hits the f.close the whole breakout stops running and the Do While fails to continue. Maybe that is why I have been struggling so much with this...I have fiddled with the SQL code so that is can be copied directly into a JiwaDemo database - no customising required. It just takes a few minutes to create a few sales orders to use.

So after much fiddling around, the actual full code hasn't changed much from my original post. Here it is

Stuart, I tried using the SalesOrders collection but couldn't seem to get that sussed either. Part of my issue is getting the data from SO_LineDetails. Each line which is written to file is at the SO_LineDetails level. I can easily use the JiwaDatabaseObject to read the SQL required, but then I am stumped about looping to write each line to file, then creating a new file for each SalesOrder in the collection.

Code: Select all

Dim HistoryID
Dim InvoiceNo
Dim DBAcc
Dim DBName
Dim Addr1,Addr2,Addr3,Addr4,Postcode
Dim   PartNo
Dim ExpDate
Dim Qty
Dim FirstLine
Dim f
FirstLine = 0
With JiwaDatabaseObject
    ' Open connection to database
    rHwnd = .StatementOpen(.ConnectionRead1, False, "", 200)
SQL = "Select TOP (100) PERCENT BO_ManualReleaseBatchLines.InvoiceNo, BO_ManualReleaseBatchLines.DebtorAccountNo, BO_ManualReleaseBatchLines.DebtorName, "
   SQL = SQL & " BO_ManualReleaseBatchLines.PartNo, BO_ManualReleaseBatchLines.BackOrderQuantity, BO_ManualReleaseBatchLines.ExpectedDeliveryDate, SO_History.DelAddress1, "
   SQL = SQL & " SO_History.DelAddress2, SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, SO_History.DeliveryAddressCountry, SO_LineDetails.Quantity, "
   SQL = SQL & " SO_LineDetails.SerialNo, SO_LineDetails.ExpiryDate, SO_History.InvoiceHistoryID, SO_History.HistoryNo, SO_Main.InvoiceID "
   SQL = SQL & " FROM BO_ManualReleaseBatchLines 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 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) "
   SQL = SQL & "  ORDER BY BO_ManualReleaseBatchLines.InvoiceNo"
            '   FileName = "C:\Danny\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
             Set fso = CreateObject("Scripting.FileSystemObject")
           
             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)
                SubQty =  .GetData(CInt(rHwnd), 13)
                Serial =  .GetData(CInt(rHwnd), 14)
                Expiry = .GetData(CInt(rHwnd), 15)
                HistoryID = .GetData(CInt(rHwnd), 16)
                HistoryNo = .GetData(CInt(rHwnd), 17)
                InvoiceID = .GetData(CInt(rHwnd), 18)
                 HistNo = Right("0" & HistoryNo,2)
                 MsgBox PreviousInvoiceNo & " : " & InvoiceNo   
                 If PreviousInvoiceNo <> Trim(InvoiceNo) Then
                FileName = "C:\Danny\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV"
                    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
                   If Not f Is Nothing Then
                      MsgBox "f is something, so closing file"
                       f.close
                   End If
                    Set Newf = fso.GetFile(FileName)
                    Newf.Copy("C:\Danny\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


[Mike.Sheen]

I still don't like this:

Code: Select all

If PreviousInvoiceNo <> Trim(InvoiceNo) Then
                FileName = "C:\Danny\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV"
                    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

It looks like you are creating a file, but you didn't close the previous one (if this is not the first time through). This is where you should be closing the file object - doing so will flush anything still in the buffer to disk.

[DannyC]

Yes - my thoughts exactly, but when I put in the f is nothing bit, it didn't work. Something about the f.close makes the breakout crap itself & just exit.

I think I have Stuart's technique almost nutted out - using the SalesOrder collection. still needs some thought and further coding. WIll post back here how I get on. Appreciate your time. It helps.

EDIT: So I definitely have confirmed that the f.Close causes issues. When I rem that line, the code continues nicely.
Using the SalesOrder collection, I only have one small issue which I can't get right - it is dropping 1 line per sales order, i.e. if I release 2 lines on the order, I only get 1 line written. If If I release 5 lines on an order I only get 4 lines written.

EDIT (again): OK - so I worked out why I was dropping a line. In my non-programmer knowledge I found that .FetchRow actually fetches another row, so the first line was getting "lost" when I test if I return any rows i.e. the

Code: Select all

              If .FetchRow(CInt(rHwnd)) = True Then


If true then I write the header line and commence writing the lines in the loop

Code: Select all

                    Do While .FetchRow(CInt(rHwnd)) = True


which was actually fetching another row. Duh! So I simply had to assign my variables within the IF .FetchRow test AND write not only the header but also the first line, THEN continue to loop through the rest of the rows.

My incorrect code. This can be copied directly into a JiwaDemo database.

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 f

For Each SO In ManualBackOrderReleaseBatchObject.SalesOrders


        
   With JiwaDatabaseObject
       rHwnd = .StatementOpen(.ConnectionRead1, False, "", 200)
   SQL = "Select TOP (100) PERCENT BO_ManualReleaseBatchLines.InvoiceNo, BO_ManualReleaseBatchLines.DebtorAccountNo, BO_ManualReleaseBatchLines.DebtorName, "
      SQL = SQL & " BO_ManualReleaseBatchLines.PartNo, BO_ManualReleaseBatchLines.BackOrderQuantity, BO_ManualReleaseBatchLines.ExpectedDeliveryDate, SO_History.DelAddress1, "
      SQL = SQL & " SO_History.DelAddress2, SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, SO_History.DeliveryAddressCountry, SO_LineDetails.Quantity, "
      SQL = SQL & " SO_LineDetails.SerialNo, SO_LineDetails.ExpiryDate, SO_History.InvoiceHistoryID, SO_History.HistoryNo, SO_Main.InvoiceID "
      SQL = SQL & " ,BO_ManualReleaseBatchLines.FulfillBackOrderFlag "
      SQL = SQL & " FROM BO_ManualReleaseBatchLines 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 BO_ManualReleaseBatchLines.InventoryID = SO_Lines.InventoryID INNER Join "
      SQL = SQL & " SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID  INNER JOIN "
        SQL = SQL & " BO_ManualReleaseBatch ON BO_ManualReleaseBatchLines.BatchID = BO_ManualReleaseBatch.RecID "
      SQL = SQL & "WHERE(SO_Main.InvoiceID = '" & Trim(SO.InvoiceID) & "') "
      SQL = SQL & "And (BO_ManualReleaseBatchLines.FulfillBackOrderFlag <> 0) "
      SQL = SQL & "AND BO_ManualReleaseBatch.RecID = '" & ManualBackOrderReleaseBatchObject.BatchID & "'"

               'FileName = "C:\Danny\SOTest_SQL_" & SO.InvoiceNo &".TXT"
                'Set fso = CreateObject("Scripting.FileSystemObject")
                'Set f = fso.CreateTextFile(FileName, True)
                'f.WriteLine SQL
                'f.Close


         If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
             .BindMem CInt(rHwnd), 1, vbString           
 
 
          If .FetchRow(CInt(rHwnd)) = True Then
              Set fso = CreateObject("Scripting.FileSystemObject")
             LineNo = 0
            FileName = "C:\Danny\SOTest_" & Trim(SO.InvoiceNo) & "-" & SO.InvoiceHistoryNo & ".CSV"
             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
         
   
                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)
                   SubQty =  .GetData(CInt(rHwnd), 13)
                   Serial =  .GetData(CInt(rHwnd), 14)
                   Expiry = .GetData(CInt(rHwnd), 15)
                   HistoryID = .GetData(CInt(rHwnd), 16)
                   HistoryNo = .GetData(CInt(rHwnd), 17)
                   InvoiceID = .GetData(CInt(rHwnd), 18)
                   Release = .GetData(CInt(rHwnd), 18)
                    HistNo = Right("0" & HistoryNo,2)
                    MsgBox Trim(InvoiceNo) & "LineNo: " & LineNo & ":" & Release
                  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               
                Loop 
         End If    'If FetchRow = True
         Else
               MsgBox "Error in SQL : " & SQL
         End If   'If ExecuteSelect
       .StatementClose CInt(rHwnd)
    End With
'    f.close
    Set Newf = fso.GetFile(FileName)
    Newf.Copy("C:\Danny\Archive\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV")                   
   
Next

End Sub

[Mike.Sheen]

Hi Danny,

Let me know if this works for you:

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 f

Set fso = CreateObject("Scripting.FileSystemObject")

For Each SO In ManualBackOrderReleaseBatchObject.SalesOrders     
   With JiwaDatabaseObject
      rHwnd = .StatementOpen(.ConnectionRead1, False, "", 0)
      
      SQL = "Select BO_ManualReleaseBatchLines.InvoiceNo, BO_ManualReleaseBatchLines.DebtorAccountNo, BO_ManualReleaseBatchLines.DebtorName, "
      SQL = SQL & " BO_ManualReleaseBatchLines.PartNo, BO_ManualReleaseBatchLines.BackOrderQuantity, BO_ManualReleaseBatchLines.ExpectedDeliveryDate, SO_History.DelAddress1, "
      SQL = SQL & " SO_History.DelAddress2, SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, SO_History.DeliveryAddressCountry, SO_LineDetails.Quantity, "
      SQL = SQL & " SO_LineDetails.SerialNo, SO_LineDetails.ExpiryDate, SO_History.InvoiceHistoryID, SO_History.HistoryNo, SO_Main.InvoiceID "
      SQL = SQL & " ,BO_ManualReleaseBatchLines.FulfillBackOrderFlag "
      SQL = SQL & " FROM BO_ManualReleaseBatchLines 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 BO_ManualReleaseBatchLines.InventoryID = SO_Lines.InventoryID INNER Join "
      SQL = SQL & " SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID  INNER JOIN "
      SQL = SQL & " BO_ManualReleaseBatch ON BO_ManualReleaseBatchLines.BatchID = BO_ManualReleaseBatch.RecID "
      SQL = SQL & "WHERE(SO_Main.InvoiceID = '" & Trim(SO.InvoiceID) & "') "
      SQL = SQL & "And (BO_ManualReleaseBatchLines.FulfillBackOrderFlag <> 0) "
      SQL = SQL & "AND BO_ManualReleaseBatch.RecID = '" & ManualBackOrderReleaseBatchObject.BatchID & "'"

      'FileName = "C:\Danny\SOTest_SQL_" & SO.InvoiceNo &".TXT"
      'Set fso = CreateObject("Scripting.FileSystemObject")
      'Set f = fso.CreateTextFile(FileName, True)
      'f.WriteLine SQL
      'f.Close
      
      HeaderCreated = False
      
      If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
         Do While .FetchRow(CInt(rHwnd)) = True

            If Not HeaderCreated Then
               HeaderCreated = True
               LineNo = 0
               FileName = "C:\Danny\SOTest_" & Trim(SO.InvoiceNo) & "-" & SO.InvoiceHistoryNo & ".CSV"
               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               
            End If
         
            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)
            SubQty =  .GetData(CInt(rHwnd), 13)
            Serial =  .GetData(CInt(rHwnd), 14)
            Expiry = .GetData(CInt(rHwnd), 15)
            HistoryID = .GetData(CInt(rHwnd), 16)
            HistoryNo = .GetData(CInt(rHwnd), 17)
            InvoiceID = .GetData(CInt(rHwnd), 18)
            Release = .GetData(CInt(rHwnd), 18)
            HistNo = Right("0" & HistoryNo,2)
            MsgBox Trim(InvoiceNo) & "LineNo: " & LineNo & ":" & Release
            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               
         Loop 
         Else
         MsgBox "Error in SQL : " & SQL
         End If   'If ExecuteSelect
      .StatementClose CInt(rHwnd)
   End With

   If HeaderCreated Then
      f.close
      Set Newf = fso.GetFile(FileName)
      Newf.Copy("C:\Danny\Archive\SOTest_" & Trim(InvoiceNo) & "-" & HistoryNo & ".CSV")                   
   End If
Next

End Sub

Mike

[DannyC]

Mike,

Your code is much neater but using it onsite doesn't work. I seem to have it working with my messier code, but odd - it only works if I un-rem the lines where I dump out the SQL statement for each sales order.
If they are remmed, the breakout fails to work.