Force serialised stock to use FIFO and not prompt for serial

[Mike.Sheen]

Here's a little breakout to force serialised stock to just use FIFO to get stock, and not prompt the user to select the serial numbers, when selling serialised stock on the sales order entry form.

Code: Select all

   
' Sales Order Line Changed Breakout
' =================================
   SalesOrderLineObject.SalesOrderSerialStockSelectionType = 1 ' e_SalesOrderSerialStockSelectionFIFO
End Sub

' Sales Order Line Added Breakout
' =================================
    SalesOrderLineObject.SalesOrderSerialStockSelectionType = 1 ' e_SalesOrderSerialStockSelectionFIFO
End Sub

' Sales Order Line Added Before Quantity Set Breakout
' =============================================
    SalesOrderLineObject.SalesOrderSerialStockSelectionType = 1 ' e_SalesOrderSerialStockSelectionFIFO
End Sub



EDIT : Altered the above code to include the "Sales Order Line Added Before Quantity Set Breakout" - this is required for the above to work in 06.05.13. We changed the order in which some breakouts were fired in 06.05.13 to fix a bug to do with discounting (Bug 7138) - and this altered the behavior - so the introduction of the Sales Order Line Added Before Quantity Set Breakout was to keep the existing breakouts working.

[SBarnes]

Hi Mike,

Is there any easy way to get Jiwa to pick up the next SOH line that might match a given value and still follow along as FIFO, this given value might be in a custom field in Jiwa on In_Main?

[Mike.Sheen]

Is there any easy way to get Jiwa to pick up the next SOH line that might match a given value and still follow along as FIFO, this given value might be in a custom field in Jiwa on In_Main?

I believe so - it is possible, via breakout, to customise which SOH lines are used - is that what you are wanting ?

If so, then as general guidance, what you need to do is introduce some breakout script to populate the LineDetails collection of the sales order line. I can post some specific code demonstrating this - just post back the name of the custom field and your criteria for choosing which SOH lines to allocate.

[SBarnes]

Hi Mike,

Below is the script that will hold the custom field for batch to use, as you will see the contents will actaully be the Text of the serial number to use, let me know if you have any further questions, any help as always is greatly appreciated:

INSERT INTO IN_CustomSetting VALUES(NewID(), '01 Jan 2002','Default Batch', 'Default Batch',9,7,'','Dim Temp
Dim WorkStr
Dim AOption

With JiwaSearchObject
.Clear
.Caption = "Serial Numbers"
.SetDefaultSearch 107
.SetDefaultSearch2 107
.FilterNo = 700
UserCancelled = True
.Show

End With

If JiwaSearchObject.Results.Count <> 0 Then
Contents = JiwaSearchObject.Fields(2)
DisplayContents = JiwaSearchObject.Fields(2)
UserCancelled = False
End If
End Sub'
,' DisplayContents = Contents
End Sub')
GO

[Mike.Sheen]

Ok, here's a rough solution - there are some situations that aren't handled - such as when the same item is on the same order twice - the code needs to check if another line has taken a SOH item on an unsaved order, etc.

Code: Select all

' Sales Order Line Changed Breakout
' =================================
Dim rHwnd
Dim rHwndMany
Dim SQL
Dim QuantityThisDelivery
Dim Found
Dim ExclusionSerialCollection()
Dim CandidateSerialCollection()
Dim DefaultBatchNo
Dim QuantityTaken

   SalesOrderLineObject.SalesOrderSerialStockSelectionType = 1 ' e_SalesOrderSerialStockSelectionFIFO
   
   If FieldName = "QuantityThisDelivery" And SalesOrderLineObject.UseSerials = True Then
   
      With SalesOrderObject.Database         
         rHwnd = .StatementOpen(.ConnectionJiwaRead2,,,1)
         SQL = "SELECT COALESCE(Contents, '') " _
               & "FROM IN_CustomSettingValues " _
               & "JOIN IN_CustomSetting ON  IN_CustomSetting.SettingID = IN_CustomSettingValues.SettingID " _
               & "WHERE InventoryID = " & .FormatChar(SalesOrderLineObject.InventoryID) _
               & "AND IN_CustomSetting.SettingName = " & .FormatChar("Default Batch")
                              
         If .ExecuteSelect(CInt(rHwnd), SQL, True) = True Then                  
            If .FetchRow(CInt(rHwnd)) = True Then
               DefaultBatchNo = .GetData(CInt(rHwnd), 1)
            End If
         End If
            
         .StatementClose(CInt(rHwnd))
      End With
      
      If DefaultBatchNo <> "" Then
      
         For Each LineDetail In SalesOrderLineObject.LineDetails
            SalesOrderLineObject.LineDetails.Remove LineDetail.Key
         Next
         
         QuantityTaken = SalesOrderLineObject.QuantityThisDelivery
      
         ' Get a list of serials used by other lines on this order for the same part, which
         ' don't have a line detail id yet (ie: it hasn't been saved and thus has not updated IN_SOH).
         For Each lSalesOrderLine In SalesOrderObject.SalesOrderLines
               If lSalesOrderLine.Key <> SalesOrderLineObject.Key Then
                   If lSalesOrderLine.InventoryID = SalesOrderObject.SalesOrderLines(SalesOrderLineObject.Key).InventoryID Then
                       For Each lLineDetail In lSalesOrderLine.LineDetails
                           If lLineDetail.Quantity > 0 And Trim(lLineDetail.LineDetailID = "") Then
                               Set lSerialLineDetail = CreateObject("JiwaSalesOrder.clsLineDetail")
                              
                               lSerialLineDetail.SOHID = lLineDetail.SOHID
                               lSerialLineDetail.LineDetailID = lLineDetail.LineDetailID
                               lSerialLineDetail.Quantity = lLineDetail.Quantity
                               lSerialLineDetail.SerialNo = lLineDetail.SerialNo
                               lSerialLineDetail.ExpiryDate = lLineDetail.ExpiryDate
                               lSerialLineDetail.BinLocation = lLineDetail.BinLocation
                               lSerialLineDetail.DateIn = lLineDetail.DateIn
                               lSerialLineDetail.SpecialPrice = lLineDetail.SpecialPrice
                               lSerialLineDetail.Cost = lLineDetail.Cost
                              
                               ReDim Preserve ExclusionSerialCollection(UBound(ExclusionSerialCollection) + 1)
                               ExclusionSerialCollection(UBound(ExclusionSerialCollection)) = lSerialLineDetail
                           End If
                       Next
                   End If
               End If
           Next
      
         With SalesOrderObject.Database               
            rHwndMany = .StatementOpen(.ConnectionJiwaRead2,,,0)
   
            ' Gets a list of serials in preferred order - ie: serials matching custom setting "Default Batch No" first, then date order
            SQL = "SELECT IN_SOH.LinkID, IN_SOH.DateIn, IN_SOH.SpecialPrice, IN_SOH.QuantityLeft, IN_SOH.SerialNo, IN_SOH.LCostIn, " _
                  & "IN_SOH.ExpiryDate, IN_SOH.BinLocationDesc " _
                  & "FROM IN_SOH " _
                  & "JOIN IN_Main ON IN_Main.InventoryID = IN_SOH.InventoryID " _
                  & "WHERE IN_SOH.InventoryID = " & .FormatChar(SalesOrderLineObject.InventoryID) _
                  & "AND IN_SOH.QuantityLeft > 0 " _
                  & "And IN_SOH.IN_LogicalID = " & .FormatChar(SalesOrderObject.LogicalID) _
                  & "AND SerialNo = " &.FormatChar(DefaultBatchNo) _
                  & "ORDER BY IN_SOH.DateIn "
                                 
            If .ExecuteSelect(CInt(rHwndMany), SQL, True) = True Then                  
               Do While .FetchRow(CInt(rHwndMany)) = True                        
                  Set lSerialLineDetail = CreateObject("JiwaSalesOrder.clsLineDetail")                           
                  
                  lSerialLineDetail.SOHID = .GetData(rHwndMany, 1)
                  lSerialLineDetail.DateIn = .GetData(rHwndMany, 2)
                  lSerialLineDetail.SpecialPrice = .GetData(rHwndMany, 3)
                  lSerialLineDetail.Quantity = .GetData(rHwndMany, 4)
                       lSerialLineDetail.SerialNo = .GetData(rHwndMany, 5)
                       lSerialLineDetail.Cost = .GetData(rHwndMany, 6)
                       lSerialLineDetail.ExpiryDate = .GetData(rHwndMany, 7)
                       lSerialLineDetail.BinLocation = .GetData(rHwndMany, 8)
                      
                       If QuantityTaken + lSerialLineDetail.Quantity > SalesOrderLineObject.QuantityOrdered Then
                          lSerialLineDetail.Quantity = SalesOrderLineObject.QuantityOrdered - QuantityTaken
                       End If
                      
                       QuantityTaken = QuantityTaken + lSerialLineDetail.Quantity
                                             
                       SalesOrderLineObject.AddLineDetail lSerialLineDetail.SOHID, lSerialLineDetail.DateIn, lSerialLineDetail.Cost, lSerialLineDetail.Quantity, lSerialLineDetail.ExpiryDate, lSerialLineDetail.BinLocation, lSerialLineDetail.SpecialPrice, SalesOrderObject.LogicalID
                       SalesOrderLineObject.LineDetails(SalesOrderObject.SalesOrderLines(SalesOrderLineObject.Key).LineDetails.Count).SerialNo = lSerialLineDetail.SerialNo
                           
               Loop
               
               SalesOrderLineObject.ReAdjustDelivered SalesOrderLineObject.QuantityOrdered
            Else
               MsgBox "Error : " & .ErrorMessage
            End If
            .StatementRenew(CInt(rHwndMany))
            
            ' Gets a list of serials in preferred order - ie: serials matching custom setting "Default Batch No" first, then date order
            SQL = "SELECT IN_SOH.LinkID, IN_SOH.DateIn, IN_SOH.SpecialPrice, IN_SOH.QuantityLeft, IN_SOH.SerialNo, IN_SOH.LCostIn, " _
                  & "IN_SOH.ExpiryDate, IN_SOH.BinLocationDesc " _
                  & "FROM IN_SOH " _
                  & "JOIN IN_Main ON IN_Main.InventoryID = IN_SOH.InventoryID " _
                  & "WHERE IN_SOH.InventoryID = " & .FormatChar(SalesOrderLineObject.InventoryID) _
                  & "AND IN_SOH.QuantityLeft > 0 " _
                  & "And IN_SOH.IN_LogicalID = " & .FormatChar(SalesOrderObject.LogicalID) _
                  & "AND SerialNo <> " &.FormatChar(DefaultBatchNo) _
                  & "ORDER BY IN_SOH.DateIn "
                                 
            If .ExecuteSelect(CInt(rHwndMany), SQL, True) = True Then                  
               Do While .FetchRow(CInt(rHwndMany)) = True                        
                  Set lSerialLineDetail = CreateObject("JiwaSalesOrder.clsLineDetail")                           
                  
                  lSerialLineDetail.SOHID = .GetData(rHwndMany, 1)
                  lSerialLineDetail.DateIn = .GetData(rHwndMany, 2)
                  lSerialLineDetail.SpecialPrice = .GetData(rHwndMany, 3)
                  lSerialLineDetail.Quantity = .GetData(rHwndMany, 4)
                       lSerialLineDetail.SerialNo = .GetData(rHwndMany, 5)
                       lSerialLineDetail.Cost = .GetData(rHwndMany, 6)
                       lSerialLineDetail.ExpiryDate = .GetData(rHwndMany, 7)
                       lSerialLineDetail.BinLocation = .GetData(rHwndMany, 8)
                      
                       If QuantityTaken + lSerialLineDetail.Quantity > SalesOrderLineObject.QuantityOrdered Then
                          lSerialLineDetail.Quantity = SalesOrderLineObject.QuantityOrdered - QuantityTaken
                       End If
                      
                       QuantityTaken = QuantityTaken + lSerialLineDetail.Quantity
                                             
                       SalesOrderLineObject.AddLineDetail lSerialLineDetail.SOHID, lSerialLineDetail.DateIn, lSerialLineDetail.Cost, lSerialLineDetail.Quantity, lSerialLineDetail.ExpiryDate, lSerialLineDetail.BinLocation, lSerialLineDetail.SpecialPrice, SalesOrderObject.LogicalID
                       SalesOrderLineObject.LineDetails(SalesOrderObject.SalesOrderLines(SalesOrderLineObject.Key).LineDetails.Count).SerialNo = lSerialLineDetail.SerialNo
                           
               Loop
               
               SalesOrderLineObject.ReAdjustDelivered SalesOrderLineObject.QuantityOrdered
            Else
               MsgBox "Error : " & .ErrorMessage
            End If
            
            .StatementClose(CInt(rHwndMany))
                              
            
         End With
      End If
   End If
End Sub




[SBarnes]

Thanks Mike.

[Danny C]

Mike,

Along the lines of this thread, when importing a sales order via XML is there a way that serialised stock can automatically use FIFO instead of going to backorder?

cheers

Danny Costa

[Mike.Sheen]

Mike,

Along the lines of this thread, when importing a sales order via XML is there a way that serialised stock can automatically use FIFO instead of going to backorder?

cheers

Danny Costa

Danny,

Yes, in the sales order XML for each line, add a tag named "SalesOrderSerialStockSelectionTypes" and set it to 1.

Eg:

Code: Select all

<?xml version="1.0"?>
<JiwaDocument Type="SalesOrder">   
   <CreditNote>False</CreditNote>
   <InvoiceInitDate>2010-07-26</InvoiceInitDate>
   <OrderNo></OrderNo>
   <SOReference></SOReference>
   <Debtor>
      <AccountNo>1001</AccountNo>
   </Debtor>
   <Lines Count="1">
      <Line>
                        <SalesOrderSerialStockSelectionTypes>1</SalesOrderSerialStockSelectionTypes>
         <PartNo>1170</PartNo>
         <QuantityOrdered>2</QuantityOrdered>
         <PriceExGst>187.58</PriceExGst>
      </Line>
   </Lines>
</JiwaDocument>


[SBarnes]

Hi Mike

Similarly is there a way to tell the system what batch to use through the use of XML tags in an import? And if so what is the behavior if that bach is not found?

Secondly is there a way to bring up the batch selection screen as each line imports, rather than having to unbackorder everything as Danny asked?

[Mike.Sheen]

Hi Mike

Similarly is there a way to tell the system what batch to use through the use of XML tags in an import? And if so what is the behavior if that bach is not found?

Yes, you can provide the LineDetails. Inside each Line tag, you just need to supply the LineDetails tag and the appropriate sub-tags, as seen below.

Code: Select all

   <LineDetails>
      <LineDetail>
         <LineDetailID>31AC653EA0354237846D</LineDetailID>
         <Quantity>1</Quantity>

         <!-- If the SOHID is supplied, then we try to read the first IN_SOH record with that LinkID,
            and we set the Serial No., Cost, DateIn, Expiry and Special price from that record -->
         <SOHID>D60B705A10714A6CA623</SOHID>

         <!-- Otherwise, if the serial no. is supplied, then we try to read the first IN_SOH record with that serial no,
            and we set the Cost, DateIn, Expiry and Special price from that record -->

         <SerialNo>4562342</SerialNo>

         <!-- The bin location can be provided ONLY IF the SOHID OR the SerialNo was provided, and when BinLocation is provided       we fetch only the SOH record WITH the supplied bin location and the appropriate serial no. OR SOHID
         <BinLocation>abc</BinLocation>
      </LineDetail>
   </LineDetails>


Secondly is there a way to bring up the batch selection screen as each line imports, rather than having to unbackorder everything as Danny asked?

Is this XML Import occurring from the sales order entry form ?

The XML import routines were designed primarily to be used unattended (or "headless"). If you are requiring user interaction, and you are importing via the sales order entry form, I suggest writing some script to parse the XML and interact with the business logic yourself... that way you can properly control the import process.