BOProcessSnapshotCreated doesn't have SalesOrderObject

[DannyC]

Tried the Ctrl-C trick but not all the SQL displayed in the messagebox.

I will write to a txt file but having confirmed the SQL is OK just by duplicating it manually in a SQL query window, not sure it will shed any light anyway
I assume that Do While .FetchRow(CInt(rHwnd)) returns TRUE when rows are returned and FALSE when the query produces no results?

I am wondering if there is a limitation in the characters as it is a pretty long SQL command? Unless you have an idea, my next shot is to break up the SQL into a few smaller chunks and see if that works.

[DannyC]

I have double checked that the SQL query returns results by writing to a file to get the exact command getting executed.

I have spent vast hours trying various tweaks to the code but I still get

Code: Select all

 Do While .FetchRow(CInt(rhwnd))

returning false.

Keen for some feedback,

Cheers

Danny
PS Just to add further complexity, I need to check for Ship Complete. If this snapshot is not yet completing the order, then nothing should get produced. Only if it is Ship & Bill, Bill Complete, or a completed Ship Complete order should a file be generated.

[DannyC]

Mike,

I am onsite at the moment and wondering if you'd be able to assist on this issue ASAP. I also have another issue regarding processing orders via Scheduled Scripts which is in another forum post.
Can you call on mobile?

Cheers

Danny

[Mike.Sheen]

I have double checked that the SQL query returns results by writing to a file to get the exact command getting executed.

I have spent vast hours trying various tweaks to the code but I still get

Code: Select all

 Do While .FetchRow(CInt(rhwnd))

returning false.

Keen for some feedback,

Hi Danny,

I've managed to isolate the cause of your issue. Whilst the query might seem valid, it's not really at the time the BOProcessSnapshotCreated breakout script runs.

The query is using a HistoryID as part of the WHERE clause - the problem is that is the newly created SO_History InvoiceHistoryID and that insert into SO_History by the backorder process has not yet been committed - so your .FetchRow will return 0 rows, as no rows satisfy the where clause.

I shortened your query (removed references to the custom line field) and tested and could reproduce your issue. I then message-boxed the query out after executing but before the .fetchrow - using my shortened query the message box contained the full query and I ran that in query analyzer and found it caused a blocking situation due to the pending commit of the SO_History record insertion. After pressing "ok" on my message box and letting the process continue, running the query again in query analyser this time returned a result - and the history id of the result was the history id of the newly created snapshot.

I tried setting the transaction isolation level for the query to be read uncommitted - but that seemed to make no difference - after looking at the backorder processing code I can see there is already a fair bit of transaction isolation level setting and resetting going on anyway - It seems to me the .fetchrow is returning right away and not blocking because the read connection already has a transaction isolation level of read uncommitted - but for whatever reason it doesn't return the recently inserted so_history record.

The BOProcessComplete might be a better place to do what you want - it is executed after all backorders have been fulfilled and committed - you can get which orders were updated by using the ProcDateTime passed to the breakout - the SO_History.RecordDate should match the ProcDateTime for all orders updated.

Mike

[DannyC]

Understood.
Will try BOProcessComplete.

I guess I will need to query the database for all the sales orders WHERE ProcDateTime = the returned value. And then I can do the same longer SQL command on each sales order.
Will get onto it tonight & let you know how I go.

[Mike.Sheen]

I guess I will need to query the database for all the sales orders WHERE ProcDateTime = the returned value. And then I can do the same longer SQL command on each sales order.
Will get onto it tonight & let you know how I go.

SO_History.RecordDate should match the ProcDateTime for all orders updated.

So, something like:

Code: Select all

"... WHERE SO_History.RecordDate = " & .FormatDateTime(ProcDateTime)

[DannyC]

Before even attempting the

Code: Select all

    "... WHERE SO_History.RecordDate = " & .FormatDateTime(ProcDateTime)

I just put a simple one liner in the BOProcessComplete breakout

Code: Select all

msgbox "AAAAAA"

I don't think that breakout is getting any chance to fire as I got nothing.

Back to the drawing board?

[DannyC]

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

[DannyC]

Mike,

This one seems to be all sussed. Got it working & so far seems to work successfully.
Thanks for you time. Very appreciated.

Cheers

Danny