Supplier return credit activation problem

[pricerc]

I'm trying to figure out a problem with activating a supplier return credit: customer is getting a concurrency exception on IN_SOH.

This is a part of the system that I've not worked in before, so it's taking me some time to get my head around what might be going wrong.

The credit is being created from an existing shipping record, but it seems that the IN_SOH_LinkID is not being populated in RA_CreditLineDetails when the shipping is added.

Any suggestions on where to start looking?

[Mike.Sheen]

I'm trying to figure out a problem with activating a supplier return credit: customer is getting a concurrency exception on IN_SOH.

This is a part of the system that I've not worked in before, so it's taking me some time to get my head around what might be going wrong.

The credit is being created from an existing shipping record, but it seems that the IN_SOH_LinkID is not being populated in RA_CreditLineDetails when the shipping is added.

Any suggestions on where to start looking?

A concurrency exception should only occur when something has updated a row in a table between the read and save. In this case it sounds like you might have the same IN_SOH record selected twice - so as it's progressing through the lines on the credit, it updates a particular IN_SOH record and then on the same credit another line is trying to update the same IN_SOH record.

Check to see if the same part is on the shipping or credit document twice.

Failing that I'd run SQL profiler to see which update statement it's failing on - that'll give you a clue where to look next.

[pricerc]

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?

[Mike.Sheen]

even on an update, if LinkID is currently null/empty, you're assigning a new id.

I'm not seeing that... there is a test to decide if we are to generate a new id - and only if it's currently null or blank:

Code: Select all

If (Me.IN_SOH_LinkID Is Nothing OrElse Me.IN_SOH_LinkID.Trim().Length = 0) Then

So not sure what you mean there.

Could there be something we need to fix with the upgraded data?

Quite possibly - I'd create a support ticket and expect to be asked to upload a copy of the data. We'll be able to give you a work-around then and also adjust the software to stop this from happening to others.

[pricerc]

there is a test to decide if we are to generate a new id - and only if it's currently null or blank

But that code is outside of the if/else blocks for building the INSERT/UPDATE query.

A new LinkID is only valid for an INSERT. If you're doing an UPDATE, then a null or blank LinkID is an error - as I read the code, and based on what I saw in the SQL trace, it seems that it is seeing a null LinkID, assigning a new one, and then trying to update IN_SOH using the new LinkID, which doesn't exist, causing 'records affected' to return 0, and the concurrency exception.

[Mike.Sheen]

itrubsthelotiononitsskin.jpg (5.85 KiB) Viewed 306 times

It logs the ticket on the helpdesk...

[pricerc]

itrubsthelotiononitsskin.jpg

It logs the ticket on the helpdesk...

I'm planning on it