BOProcessSnapshotCreated doesn't have SalesOrderObject

[DannyC]

Hi,

I need to write a breakout in the BOProcessSnapshotCreated event, but the SalesOrderObject properties/objects like the lines but it is not available. How can I get information available in the SalesOrderObject?
I need information like
SalesOrderObject.InvoiceNo
SalesOrderObject.DebtorAccountNo
SalesOrderObject.DebtorName
SalesOrderLine.PartNo
SalesOrderLine.QuantityThisDelivery
SalesOrderObject.SalesOrderHistorys(SalesOrderObject.CurrentHistoryNo).DelAddress1

plus more

Cheers

Danny

[Mike.Sheen]

Hi,

I need to write a breakout in the BOProcessSnapshotCreated event, but the SalesOrderObject properties/objects like the lines but it is not available. How can I get information available in the SalesOrderObject?
I need information like
SalesOrderObject.InvoiceNo
SalesOrderObject.DebtorAccountNo
SalesOrderObject.DebtorName
SalesOrderLine.PartNo
SalesOrderLine.QuantityThisDelivery
SalesOrderObject.SalesOrderHistorys(SalesOrderObject.CurrentHistoryNo).DelAddress1

plus more

Cheers

Danny

Hi Danny,

The only way to do that is to take the InvoiceHistoryID parameter passed to that breakout and then query the database for the information. A sample code snippet on how to do a query is found here.

Mike

[DannyC]

I thought of that immediately after posting but hoped there might be a better way.
Anyway, thanks for the fast reply. Very much appreciated.

The JiwaDatabaseObject is passed in but using

Code: Select all

With JiwaDatabaseObject

causes the breakout to exit. It doesn't run.

Stuck.

cheers

Danny

[Mike.Sheen]

The JiwaDatabaseObject is passed in but using

Code: Select all

With JiwaDatabaseObject

causes the breakout to exit. It doesn't run.

Stuck.

cheers

Danny

Works for me.

I put the following into the BOProcessSnapshotCreated breakout of sales orders:

Code: Select all

With JiwaDatabaseObject
    MsgBox "in here"
End With
End Sub

And then I created a sales order with 1 on backorder, processed it and then ran back order processing and I got the message box.

Does the above work for you?

[DannyC]

That's basically my exact code too but it's not working for me. I am releasing via Manual Backorder Release. Are you?

[Mike.Sheen]

That's basically my exact code too but it's not working for me. I am releasing via Manual Backorder Release. Are you?

No, because you never mentioned that you were using the manual backorder release - I assumed you meant the normal backorder processing. I'll try with a manual backorder release.

EDIT: Ok, it works for me during a manual backorder release also - my version of Jiwa is 06.05.13 - is that what you're testing with?

[DannyC]

Thanks for the feedback Mike. Super appreciative.

I have sussed out my breakout code & almost have it nailed now.
In general, the code to get other database values work when a single value is returned i.e

Code: Select all

SELECT InvoiceNo WHERE InvoiceID = InvoiceID

In my fairly big SELECT statement, I am getting multiple cols which also may return multiple rows. I have done this before & am using similar code but the breakout is failing on the line

Code: Select all

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

Here is my full code. The suss line is down around line 90-95ish. The goal is to dump out a file showing the items to pick.
I need to show a single line for the items which are not serialised as all they need is the QtyThisDelivery. For those serialised items where the QtyThisDelivery > 1, there may be multiple LineDetails, so for those I need to show a separate row with the LineDetails.SerialNo & LineDetails.Quantity. And the expiry date.
For each line returned, it will dump a line to the CSV file.

Code: Select all

Dim rhwnd
Dim SQL
Dim InvoiceNo
Dim ExpectedDeliveryDate
Dim HistoryNo
Dim DelAddress1
Dim DelAddress2
Dim DelAddress3
Dim DelAddress4
Dim PostCode
Dim Country
Dim Contents
Dim PartNo
Dim QuantityThisDel
Dim Quantity
Dim SerialNo
Dim ExpiryDate
Dim UseSerial
Dim DebtorAccountNo
Dim DebtorName

Dim FileName
Dim fso, f
Dim TextOut
Dim LineNo
LineNo = 0


With JiwaDatabaseObject
      ' Open connection to database
      rHwnd = .StatementOpen(.ConnectionRead1, False, "", 1)

      SQL = "SELECT InvoiceNo from SO_Main WHERE InvoiceID = '" & InvoiceID & "'"
      If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
            .BindMem CInt(rHwnd), 1, vbString
            If .FetchRow(CInt(rHwnd)) = True Then
                  InvoiceNo = .GetData(CInt(rHwnd), 1)
            End If
      Else
            MsgBox "Error in SQL : " & SQL
      End If
      .StatementClose CInt(rHwnd)
End With

FileName = "\\sssql03\TOSEQOS$\AU\SO\SO_" & InvoiceNo & ".CSV"


Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.CreateTextFile(FileName, True)

TextOut = "InvoiceNo|CustomerCode|CustomerName|LineNo|PartNo|QtyOrdered|DelDate|DelAddr1|DelAddr2|City|State|DelPostcode|Country|CALLTAG|ID|SubQty|SerialNo|ExpiryDate"
f.WriteLine TextOut
 
With JiwaDatabaseObject
    rhwnd = .StatementOpen(.ConnectionRead1, False, "", 1000)
    SQL = "SELECT SO_Main.InvoiceNo, SO_Main.ExpectedDeliveryDate, SO_History.HistoryNo, SO_History.DelAddress1, SO_History.DelAddress2, "
   SQL = SQL & " SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, SO_History.DeliveryAddressCountry, "
     SQL = SQL & " SO_CustomSettingValues.Contents, SO_Lines.PartNo, SO_Lines.QuantityThisDel, SO_LineDetails.Quantity, "
     SQL = SQL & " SO_LineDetails.SerialNo, SO_LineDetails.ExpiryDate,SO_Lines.UseSerialNo,"
     SQL = SQL & " DB_Main.AccountNo, DB_Main.Name"
   SQL = SQL & " FROM    SO_Main INNER Join"
     SQL = SQL & " DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID 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 INNER Join"
     SQL = SQL & " SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID INNER Join"
     SQL = SQL & " SO_CustomSettingValues ON SO_Main.InvoiceID = SO_CustomSettingValues.InvoiceID INNER Join"
     SQL = SQL & " SO_CustomSetting ON SO_CustomSettingValues.SettingID = SO_CustomSetting.SettingID"
   SQL = SQL & " WHERE (SO_CustomSetting.SettingName = 'CALLTAG') AND (SO_History.InvoiceHistoryID = '" & HistoryID & "')"
           
    If .ExecuteSelect(CInt(rhwnd), SQL) = True Then
        .BindMem CInt(rhwnd), 1, vbString
        .BindMem CInt(rhwnd), 2, vbDate
        .BindMem CInt(rhwnd), 3, vbString
        .BindMem CInt(rhwnd), 4, vbString     'deladdr1
        .BindMem CInt(rhwnd), 5, vbString
        .BindMem CInt(rhwnd), 6, vbString
        .BindMem CInt(rhwnd), 7, vbString
        .BindMem CInt(rhwnd), 8, vbString
        .BindMem CInt(rhwnd), 9, vbString   'country
        .BindMem CInt(rhwnd), 10, vbString
        .BindMem CInt(rhwnd), 11, vbString
        .BindMem CInt(rhwnd), 12, vbDouble
        .BindMem CInt(rhwnd), 13, vbDouble
        .BindMem CInt(rhwnd), 14, vbString
        .BindMem CInt(rhwnd), 15, vbDate
        .BindMem CInt(rhwnd), 16, vbDouble
        .BindMem CInt(rhwnd), 17, vbString
        .BindMem CInt(rhwnd), 18, vbString
MsgBox "here 1"       
        Do While .FetchRow(CInt(rHwnd)) = True
         InvoiceNo = .GetData(CInt(rhwnd), 1)
         ExpectedDeliveryDate = .GetData(CInt(rhwnd), 2)
         HistoryNo = .GetData(CInt(rhwnd), 3)
         DelAddress1 = .GetData(CInt(rhwnd), 4)
         DelAddress2 = .GetData(CInt(rhwnd), 5)
         DelAddress3 = .GetData(CInt(rhwnd), 6)
         DelAddress4 = .GetData(CInt(rhwnd), 7)
         PostCode = .GetData(CInt(rhwnd), 8)
         Country = .GetData(CInt(rhwnd), 9)
         Contents = .GetData(CInt(rhwnd), 10)
         PartNo = .GetData(CInt(rhwnd), 11)
         QuantityThisDel = CSng(.GetData(CInt(rhwnd), 12))
         Quantity = CSng(.GetData(CInt(rhwnd), 13))
         SerialNo = .GetData(CInt(rhwnd), 14)
         ExpiryDate = .GetData(CInt(rhwnd), 15)
         UseSerial = CBool(.GetData(CInt(rhwnd),16))
         DebtorAccountNo = .GetData(CInt(rhwnd), 17)
         DebtorName = .GetData(CInt(rhwnd), 18)
MsgBox "here 2"         
'''''''''''''''''''''''''''''  Do the file output inside this loop
   HistNo = Right("0" & HistoryNo,2)
MsgBox "here 3"
MsgBox "serial?: " & UseSerial
If UseSerial = False Then
      LineNo = LineNo + 1
       TextOut = InvoiceNo & "-" & HistNo & "|"
       TextOut = TextOut & DebtorAccountNo & "|"
       TextOut = TextOut & DebtorName & "|"
       TextOut = TextOut & LineNo & "|"
       TextOut = TextOut & PartNo & "|"
       TextOut = TextOut & QuantityThisDel & "|"
       TextOut = TextOut & ExpectedDeliveryDate & "|"
       TextOut = TextOut & DelAddress1 & "|"
       TextOut = TextOut & DelAddress2 & "|"
       TextOut = TextOut & DelAddress3 & "|"
       TextOut = TextOut & DelAddress4 & "|"
       TextOut = TextOut & PostCode & "|"
       TextOut = TextOut & Country & "|"
       Select Case Contents          
          Case "1"   TextOut = TextOut & "Urgent"
          Case "2"   TextOut = TextOut & "Pickup"
          Case Else    TextOut = TextOut & "Normal"
       End Select   
       TextOut = TextOut & "|" & HistoryID & "|"
       TextOut = TextOut & Quantity & "|"
       TextOut = TextOut & "|"
       f.WriteLine TextOut
Else
   '  Use serials = True
      LineNo = LineNo + 1
       TextOut = InvoiceNo & "-" & HistNo & "|"
       TextOut = TextOut & DebtorAccountNo & "|"
       TextOut = TextOut & DebtorName & "|"
       TextOut = TextOut & LineNo & "|"
       TextOut = TextOut & PartNo & "|"
       TextOut = TextOut & QuantityThisDel & "|"
       TextOut = TextOut & ExpectedDeliveryDate & "|"
       TextOut = TextOut & DelAddress1 & "|"
       TextOut = TextOut & DelAddress2 & "|"
       TextOut = TextOut & DelAddress3 & "|"
       TextOut = TextOut & DelAddress4 & "|"
       TextOut = TextOut & PostCode & "|"
       TextOut = TextOut & Country & "|"
       Select Case Contents          
          Case "1"   TextOut = TextOut & "Urgent"
          Case "2"   TextOut = TextOut & "Pickup"
          Case Else    TextOut = TextOut & "Normal"
       End Select   
       TextOut = TextOut & "|" & HistoryID & "|"
       TextOut = TextOut & Quantity & "|"
       TextOut = TextOut & SerialNo & "|"
       TextOut = TextOut & ExpiryDate & "|"
       f.WriteLine TextOut
End If





'''''''''''''''''''''''''''''    END File Output loop
        Loop
    Else
        MsgBox "Can't get delivery data " & .ErrorMessage
    End If
    .StatementClose CInt(rhwnd)
End With


End Sub

Cheers

[Mike.Sheen]

Hi Danny,

I can't test this myself right now, but upon initial examination 2 things caught my eye:

Code: Select all

.BindMem CInt(rhwnd), 16, vbDouble

Should actually be

Code: Select all

.BindMem CInt(rhwnd), 16, vbBoolean

This is the binding of the UseSerialNo field - this is a BIT in SQL, which is a Boolean in VB - so your use of vbDouble might be causing issues.

The second thing which won't be the cause of your issue, but something to be aware of is:

Code: Select all

QuantityThisDel = CSng(.GetData(CInt(rhwnd), 12))
Quantity = CSng(.GetData(CInt(rhwnd), 13))

Those fields should be double precision, so use CDbl instead of CSng.

I'll take your code and try to reproduce your issue and report back what I find.

Mike

[DannyC]

Mike,

So far I have found that the

Code: Select all

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

is getting a value of False, hence why it doesn't enter the loop. I though maybe the SQL is not returning any rows but I have confirmed it is by re-doing the same SQL in a query window.
Is there a way to display the generated SQL and get it into the clipboard for pasting into a SQL query window exactly as is? msgbox can't do that.

Keen to hear your feedback & really appreciate the help.

Cheers

Danny

[Mike.Sheen]

Is there a way to display the generated SQL and get it into the clipboard for pasting into a SQL query window exactly as is? msgbox can't do that.

Hi Danny,

If you do display a message box, you can get the contents into the clipboard - if the message box has the focus, then ctrl-c will copy the contents into the clipboard. I'm not sure what character limits the message box or clipboard have, but it is worth a shot.

Failing that, write it to a file using the Scripting Filesystem object - ie:

Code: Select all

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test.")
a.Close

Mike