Share
Report
325I ran into this error recently and wanted to pass along what I found for others (and myself some day in the future when I hit it again and I've completely forgotten that I found the solution).
The Error message is:
SQL Server Message 515
Cannot insert the value NULL into column 'PtdRcpt{XX}', table 'dbo.SlsPerHist'; column does not allow nulls. UPDATE fails.
(NOTE: {XX} is the fiscal period bucket being updated... 00 = period 1, 01 = period 2, 02 = period 3, etc.)
I reverse engineered the issue and traced the problem to be actually a Divide By Zero error that is occurring in a view named: "vp_08400ARbalancesHistSls".
The problem occurs when you are applying a payment to an invoice that originated from a Shipper and where the following are true about that shipper:
1) there are salesperson ID's assigned to one or more of the Shipper line items and the salespersons commission percentage for the line items <> 0
2) the total net amount of "merchandise" line items (i.e. line items with stock or nonstick inventory items on them nets to ZERO (so there are positive and negative line items that all together sum to zero)
3) there are other charges (i.e. Misc. charges) on the shipper that result in the shipper still having a balance in spite of the fact that the inventory line items net to zero.
I report the problem to Microsoft and then reported back to me that this was a known bug since sometime in version 7 of Dynamics SL and that the "workaround" was to use a SQL update statement to set the SOShipHeader.TotMerch field to 0.01 and then release the batch.
This will in fact get the batch the release, because the problem in the view is that there is a calculation that divides by the value in SOShipHeader.TotMerch so forcing that to be something other than zero does in fact get the batch to release.
However, I wasn't really satisfied with that solution so I did a bit more digging and determined that, it appeared to me that the core problem is that the view never should have been dividing by the value in SOShipHeader.TotMerch, but rather should have been dividing by the value in SOShipHeader.TotInv (or possibly the value in the OrigDocAmt field of the ARDoc record that was created from the shipper).
When I started typing this my intention was to actually post the errant code that is distributed with the product along with the proposed correction that I implemented for my client.....
However, now that I'm at that point, I'm concerned about the NDA implications. I'm not sure that I can legally post the source code of the View (even though anyone who owns Dynamics SL can easily access it for themselves).
So..... I'm going to wrap up this post with this.... I think there is an error in the view 'vp_08400ARbalancesHistSls'. In that view, the logic appears to be trying to calculate the amount that individual shipper line items should effect the Receipt totals for sales people associated with the line items. To do, the view appears to be trying to calculation what percentage of the total invoice, a payment is currently being applied so that it can then calculate the corresponding portion of the line items that should be added to the receipt amounts for the salepersons.
So, for example, if the original invoice was for $100.00 total and there was a line item on the invoice for $30.00 and Sales person XYZ is supposed to get 100% of the commission for that one line item, then if a payment application in he amount of $50.00 were to be applied to the invoice, then that would me than 50% of the total invoice was being paid and subsequently the salesperson's receipt total should be increased by 50% of the line item, thus $15.00.
So the math "should" be (with some details omitted for clarity:
((Payment amount / Original Invoice Amount) * (Commissionable line Amount)
((ARAdjust.AdjAmt / SOShipHeader.TotInvc) * (SOShipSplit.TotMerch)
however, the view supplied with the product, does not use the "SOShipHeader.ToTInvc" amount to calculate the percentage of the invoice being paid. Instead it uses a value named "SOShipHeader.TotMerch" which is a value that "can be" zero even if the invoice as a whole is not zero.
So... to avoid possible none disclosure issues, I'll leave to the reader to do their own detailed investigation into the problematic view (vp_08400ARbalancesHistSls) and see if the problem has been correct in a later version or if some intervention might be in order.
If some official spokesperson from Microsoft happens to give me a green light to post the code and the possible fix, I'll come back later to do so.
Regards,
Tom Malia
*This post is locked for comments
All responses (
Answers (
