Thanks Mike.
The reported error is actually caused by IN_SOH being null when SOH.iSave is called. It took me a while to find, because SQL trace was reporting LinkID's that didn't exist.
Decompiling reveals this:
- Code: Select all
If (Not Me.SOHInsertFlag OrElse Me.DeleteFlag) Then
str = "UPDATE IN_SOH SET LastSavedDateTime = GETDATE() , QuantityLeft = QuantityLeft + @QuantityAdjustmentAmount "
If (MyBase.ChangedPropertyList.Contains("QuantityIn")) Then
str = String.Concat(str, ", QuantityIn = QuantityIn + @QuantityAdjustmentAmount")
End If
...
Else
str = "INSERT INTO IN_SOH (LinkID, LastSavedDateTime, InventoryID, DateIn, QuantityIn, LCostIn, SCostIn, SpecialPrice, QuantityLeft, SerialNo, TaxPaid, Ref, SourceID, HistoryText, QuantityAllocated, IN_LogicalID, BinLocationDesc, ExpiryDate, SY_Forms_ClassName) VALUES (@LinkID, GETDATE(), @InventoryID, @DateIn, @QuantityIn, @LCostIn, @SCostIn, @SpecialPrice, @QuantityLeft, @SerialNo, @TaxPaid, @Ref, @SourceID, @HistoryText, @QuantityAllocated, @IN_LogicalID, @BinLocationDesc, @ExpiryDate, @SY_Forms_ClassName)"
End If
Dim quantity As Decimal = Me.Quantity
If (Not Me.DeleteFlag) Then
...
End If
Using sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(str, database.SQLConnection, database.SQLTransaction)
sqlParameter = New System.Data.SqlClient.SqlParameter("@LinkID", SqlDbType.[Char])
If (Me.IN_SOH_LinkID Is Nothing OrElse Me.IN_SOH_LinkID.Trim().Length = 0) Then
Dim guid As System.Guid = System.Guid.NewGuid()
Dim str1 As String = Strings.Left(guid.ToString().Replace("-", ""), 20)
Me._IN_SOH_LinkID = str1
End If
sqlParameter.Value = Me.IN_SOH_LinkID
in particular:
- Code: Select all
If (Me.IN_SOH_LinkID Is Nothing OrElse Me.IN_SOH_LinkID.Trim().Length = 0) Then
Dim guid As System.Guid = System.Guid.NewGuid()
Dim str1 As String = Strings.Left(guid.ToString().Replace("-", ""), 20)
Me._IN_SOH_LinkID = str1
End If
even on an update, if LinkID is currently null/empty, you're assigning a new id.
Anyway...
Further digging has revealed that for the offending returns, the transit warehouse LinkID doesn't exist for the Shipping Return. This is how I figured that out:
- Code: Select all
declare @ShippingID varchar(50) = (select recid FROM RA_ShipMain
WHERE ShippingNo = '410')
SELECT *
FROM RA_ShipMain
WHERE RecID = @ShippingID
SELECT *
FROM RA_ShipLines
WHERE RA_ShipMain_RecID = @ShippingID
SELECT ld.*
FROM RA_ShipLineDetails ld
inner join RA_ShipLines l on l.RecID = ld.Parent_RecID
WHERE l.RA_ShipMain_RecID = @ShippingID
SELECT *
FROM IN_SOH
WHERE LinkID IN (select in_soh_linkid FROM RA_ShipLineDetails ld
inner join RA_ShipLines l on l.RecID = ld.Parent_RecID
WHERE l.RA_ShipMain_RecID = @ShippingID)
OR SerialNo in ( select SerialNo FROM RA_ShipLineDetails ld
inner join RA_ShipLines l on l.RecID = ld.Parent_RecID
WHERE l.RA_ShipMain_RecID = @ShippingID)
SELECT InTransit_IN_SOHID
FROM WH_TransferLineDetails
JOIN WH_TransferLines ON WH_TransferLineDetails.WH_TransferLineID = WH_TransferLines.WH_TransferLineID
JOIN WH_Transfer ON WH_TransferLines.WH_TransferID = WH_Transfer.WH_TransferID
WHERE 1=1
AND WH_Transfer.SourceID = @ShippingID;
Since the Shipping Return pre-dates the upgrade from 6 to 7 we did 12 days ago, I decided to do a new one from scratch. It seems to work, i.e. the above query gives me a valid InTransit_IN_SOHID.
Could there be something we need to fix with the upgraded data?