A bug in codeunit 80 for 2009 sp1? (CreatePrePaymentLines)

Capone

We have a customer that have between 6000-30000 lines on their invoices from different shipments.

After we upgraded them to 2009 sp1 it could take up to 30h to post it.

Now I have finally figured out what is the problem.

It goes into function CreatePrePaymentLines where it runs the following code


IF NOT PrePmtTestRun THEN BEGIN
TestGetShipmentPPmtAmtToDeduct(SalesHeader,SalesLine);

The only problem is that PrePmtTestRun is a local boolean variable but it is never initialized so it runs all the time. This must be bug?!

ara3n

No it's not a bug.

If you look a line below it.

          IF NOT PrePmtTestRun THEN BEGIN
            TestGetShipmentPPmtAmtToDeduct(SalesHeader,SalesLine);
            PrePmtTestRun := TRUE;
          END;

It is set to true, so that it runs only once when looping through the sales line.

Capone

Yeah I noticed it. I was a bit hasty when I reported it #-o

But I realised that the real issue is how the code in TestGetShipmentPPmtAmtToDeduct works.

To me it seems like a real performance killer when you have 3000-12 000 sales lines on a invoice. :-k

There must be a better way to calculate if the prepayments are correct or if prepayments are even used on the sales lines.

Since our customer doesn't use prepayments our quick fix is to not use this function. When they want to use it we have to look into the function and see if we can rewrite the code so it works in a better way.

Edit:
If there are 10 000 sales lines (N) and each sales line corresponds to a sales shipment line (M) it will make salesShipmentLine.get :

N*TestGetShipmentPPmtAmtToDeduct

TestGetShipmentPPmtAmtToDeduct= M+M*M

Total expression will be N(M+M*M) = 10 000(10 000 + 10 000 * 10 000) = BIG NUMBER

And this is even before posting....

ara3n

I suggest to report to MS. I'm sure they will look to improve the process.

Capone

A part of the answer from Microsoft:

"I think we need to change design here. I discussed with my colleagues: only in NAV V8 there are plans to fully redesign this functionality, then in next NAV V7 there will be no changes so we need to ask to fix this."

BardurKnudsen

We (Dynamics NAV) have just made a small hotfix for NAV2009SP1 / R2 that reduces the problem. As several people on this thread have noticed, the function TestGetShipmentPPmtAmtToDeduct is only relevant when you use prepayment and when the invoice is created by retrieving shipment lines. If an order has prepayments, the lines will have a prepayment amount, so we can filter on this as well. Note that the suggested change is not fully tested yet and should be used as is. We do acknowledge that the solution is not very elegant, and will make a proper fix for next release.

Codeunit 80
Before:
PROCEDURE TestGetShipmentPPmtAmtToDeduct(SalesHeader : Record "Sales Header";SalesLine : Record "Sales Line")
TempSalesLine.SETRANGE("Document Type",SalesHeader."Document Type");
TempSalesLine.SETRANGE("Document No.",SalesHeader."No.");
IF NOT TempSalesLine.FIND('+') THEN
EXIT;
TempSalesLine.SETFILTER(Quantity,'>0');
TempSalesLine.SETFILTER("Qty. to Invoice",'>0');
TempSalesLine.SETFILTER("Shipment No.",'<>%1','');

After:
PROCEDURE TestGetShipmentPPmtAmtToDeduct(SalesHeader : Record "Sales Header";SalesLine : Record "Sales Line")
TempSalesLine.SETRANGE("Document Type",SalesHeader."Document Type");
TempSalesLine.SETRANGE("Document No.",SalesHeader."No.");
TempSalesLine.SETFILTER(Quantity,'>0');
TempSalesLine.SETFILTER("Qty. to Invoice",'>0');
TempSalesLine.SETFILTER("Shipment No.",'<>%1','');
TempSalesLine.SETFILTER("Prepmt. Line Amount",'<>0'); <- New filter
IF TempSalesLine.isempty THEN <- moved line
EXIT; <- moved line

Codeunit 90:
Before:
PROCEDURE TestGetRcptPPmtAmtToDeduct(PurchHeader : Record "Purchase Header";PurchLine : Record "Purchase Line")
TempPurchLine.SETRANGE("Document Type",PurchHeader."Document Type");
TempPurchLine.SETRANGE("Document No.",PurchHeader."No.");
IF NOT TempPurchLine.FIND('+') THEN
EXIT;
TempPurchLine.SETFILTER(Quantity,'>0');
TempPurchLine.SETFILTER("Qty. to Invoice",'>0');
TempPurchLine.SETFILTER("Receipt No.",'<>%1','');

After:
PROCEDURE TestGetRcptPPmtAmtToDeduct(PurchHeader : Record "Purchase Header";PurchLine : Record "Purchase Line")
TempPurchLine.SETRANGE("Document Type",PurchHeader."Document Type");
TempPurchLine.SETRANGE("Document No.",PurchHeader."No.");
TempPurchLine.SETFILTER(Quantity,'>0');
TempPurchLine.SETFILTER("Qty. to Invoice",'>0');
TempPurchLine.SETFILTER("Receipt No.",'<>%1','');
TempPurchLine.SETFILTER("Prepmt. Line Amount",'<>0'); <- New filter
IF TempPurchLine.ISEMPTY THEN <- moved line
EXIT; <- moved line

Best regards,
Bardur Knudsen,
SDE, Dynamics NAV.

Sara_Rybøl_[MSFT]

Thanks for the input, as mentioned above we have looked in to this problem.
For further information please have a look at KB2641728