Backorder Process Automation

[Nathan]

Hi,

I'm looking for away to automate our backorder processing and incorporate that within a Jiwa Scheduled Script. We're using Jiwa 6.5.12.

I've found a few objects referenced in other code samples and it looks like the JiwaProcessSOBatch.clsBatch automation object can be used for what we need to do from a sales order perspective, JiwaPrintReport.clsJiwaPrintReport can be used to print out the picking sheets and SocketTools.InternetMail can be used for emailing our customers.

The part that I'm not sure on is the backorder process. Using OLE/COM Object Viewer I have found several other Jiwa objects but I don't really know where to start.

Is there any documentation or code samples available that outline the use of these objects to process a backorder to get me started?

It would be much appreciated.

[Mike.Sheen]

Hi,

I'm looking for away to automate our backorder processing and incorporate that within a Jiwa Scheduled Script. We're using Jiwa 6.5.12.

I've found a few objects referenced in other code samples and it looks like the JiwaProcessSOBatch.clsBatch automation object can be used for what we need to do from a sales order perspective, JiwaPrintReport.clsJiwaPrintReport can be used to print out the picking sheets and SocketTools.InternetMail can be used for emailing our customers.

The part that I'm not sure on is the backorder process. Using OLE/COM Object Viewer I have found several other Jiwa objects but I don't really know where to start.

Is there any documentation or code samples available that outline the use of these objects to process a backorder to get me started?

It would be much appreciated.

I don't have anything handy right now that is isolated enough to run as a scheduled script, but it would be a nice scheduled script to have, so I'll make one and post it here over the next week or so.

Mike

[Nathan]

I don't have anything handy right now that is isolated enough to run as a scheduled script, but it would be a nice scheduled script to have, so I'll make one and post it here over the next week or so.

Thanks Mike, I'll keep an eye out.

[Nathan]

Hi Mike,

Did you ever get around to digging up some code samples or documentation on the backorder object?

Even an object name and list of methods/properties would give me a better starting point than what I have now

[Mike.Sheen]

Did you ever get around to digging up some code samples or documentation on the backorder object?

Shameful admission here : I completely missed this. I've added a task to my calendar to action this this week - Check back later.

I sincerely apologise for the delay!

[Mike.Sheen]

Ok, as promised - below is a scheduled script which processes backorders for sales orders.

Which backorders to process is decided by calling the stored procedure configured in the system to list all backorders (by default it is "usp_JIWA_GetBackOrderProcessList"). If that doesn't suit, just replace the call to your own stored proc, or even a straight query.

Dim Ok
Dim BOProcessObject
Dim SystemProfile
Dim WriteHandle
Dim OrdersOnBackID()
Dim InvoiceID()
Dim InvLineID()
Dim InventoryID()
Dim PartNo()
Dim Quantity()
Dim WarehouseID()

Ok = True

If Ok = True Then
Set SystemProfile = CreateObject("JiwaSysProfile.clsSysProfile")
Set myFSO = CreateObject("Scripting.FileSystemObject")

If myFSO.FileExists(JiwaDatabaseObject.IniFile) = True Then
If SystemProfile.Load(JiwaDatabaseObject.IniFile) = False Then
rtnErrorModule = SystemProfile.ErrorModule
rtnErrorString = SystemProfile.ErrorMessage
Ok = False
Else
JiwaDatabaseObject.CurrentLogicalWarehouseID = SystemProfile.GetValue("JiwaDocument/Parameters/InventoryParams/LastLogicalWarehouseID")
End If
End If

Set MyFSO = nothing
End If

If Ok = True Then
StoredProcName = JiwaDatabaseObject.ReadSysData("System", "StoredProcForProcessBackOrders", "usp_JIWA_GetBackOrderProcessList")

SQLStr = "EXEC " & StoredProcName & " 1, 0, 0, 0, 0, 0, 0, 0, 'Main / New South Wales', '', ''"

With JiwaDatabaseObject
hRead1 = .StatementOpen(.ConnectionJiwaRead1)
If .ExecuteSelect(hRead1, SQLStr, True) = True Then

ReDim OrdersOnBackID(0)
ReDim InvoiceID(0)
ReDim InvLineID(0)
ReDim InventoryID(0)
ReDim PartNo(0)
ReDim Quantity(0)
ReDim WarehouseID(0)

Do While .FetchRow(hRead1) = True
ReDim Preserve OrdersOnBackID(UBound(OrdersOnBackID) + 1)
ReDim Preserve InvoiceID(UBound(InvoiceID) + 1)
ReDim Preserve InvLineID(UBound(InvLineID) + 1)
ReDim Preserve InventoryID(UBound(InventoryID) + 1)
ReDim Preserve PartNo(UBound(PartNo) + 1)
ReDim Preserve Quantity(UBound(Quantity) + 1)
ReDim Preserve WarehouseID(UBound(WarehouseID) + 1)

OrdersOnBackID(UBound(OrdersOnBackID)) = .GetData(hRead1, 2)
InvoiceID(UBound(InvoiceID)) = Trim(.GetData(hRead1, 4))
InvLineID(UBound(InvLineID)) = Trim(.GetData(hRead1, 5))
InventoryID(UBound(InventoryID)) = .GetData(hRead1, 6)
PartNo(UBound(PartNo)) = Trim(.GetData(hRead1, 7))
Quantity(UBound(Quantity)) = .GetData(hRead1, 10)
WarehouseID(UBound(WarehouseID)) = .GetData(hRead1, 13)

Loop
Else
rtnErrorModule = SQLStr
rtnErrorString = .ErrorMessage
Ok = False
End If
.StatementClose hRead1
End With
End If

If Ok = True Then
Set BOProcessObject = CreateObject("JiwaBOProcess.StdFunctions")
Set BOProcessObject.Database = JiwaDatabaseObject
Set BOProcessObject.SystemProfile = SystemProfile
Set BOProcessObject.MDIParent = Nothing

BOProcessObject.ReadsAreUnCommitted = True

If BOProcessObject.SetupProcessBackOrdersUnattended = False Then
rtnErrorModule = "BOProcessObject.SetupProcessBackOrdersUnattended"
rtnErrorString = "unkown error"
Ok = False
End If
End If

If Ok = True Then

WriteHandle = JiwaDatabaseObject.StatementOpen(JiwaDatabaseObject.ConnectionWrite, , , 0)

For ItemIndex = 1 To UBound(OrdersOnBackID)
If BOProcessObject.ProcessBackOrdersForSingleSalesOrderUnattended(WriteHandle, OrdersOnBackID(ItemIndex), InvoiceID(ItemIndex), InvLineID(ItemIndex), Quantity(ItemIndex), InventoryID(ItemIndex), PartNo(ItemIndex), WarehouseID(ItemIndex)) = False Then
Ok = False
m_ErrorModule = "BOProcessObject.ProcessBackOrdersForSingleSalesOrderUnattended"
m_ErrorString = "Error processing backorder : " & BOProcessObject.ErrorMessage
End If

If Ok = False Then
Exit For
End If
Next

If Ok = True Then
JiwaDatabaseObject.Commit
Else
JiwaDatabaseObject.RollBack
End If

JiwaDatabaseObject.StatementClose CInt(WriteHandle)
End If

End Sub

[Nathan]

Hi Mike,

Thanks for the reply. I have a few projects at the moment, but I will tackle this in the coming weeks and see how things go.

Cheers,

Nathan