Dynamics Community Forum Thread Details

NAV 2009 ExcelBuffer Unposted Invoice Deletion Error - Sales/Purchase Header Does Not Exist

Hi all,

I've encountered a very strange error and could not find any solutions for it on the web. Situation is this:

We are using NAV 2009 R2. Company is huge, there are lots and lots of bulk data in the db and we are trying to clean that up a bit.

There are approx. 4k UNPOSTED Invoice/Cr Memos in the Sales Header and related tables. It also goes for purchase too. These are not used we are certain of that and users can not delete these by themselves cause of permissions.

So, I've asked for an excel file from the accountants which lists No.'s of these invoices to be deleted, and wrote this block:

RecNo := 0;
ExcelBuf.SETFILTER(ExcelBuf."Row No.",'<>1');
TotalRecNo := ExcelBuf.COUNT;
IF ExcelBuf.FINDSET THEN BEGIN
  REPEAT
    RecNo := RecNo + 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

    CASE TRUE OF
      (ExcelBuf.xlColID = 'A'):
      BEGIN
            InvoiceNo := ExcelBuf."Cell Value as Text";
            CRLF[1] := 10;
            InvoiceNo := DELCHR(InvoiceNo,'=',CRLF);

            CRLF[1] := 13;
            InvoiceNo := DELCHR(InvoiceNo,'=',CRLF);

            IF InvoiceType = InvoiceType::"Sales Invoice" THEN BEGIN
              recSalesHeader.RESET;
              recSalesHeader.SETRANGE("Document Type", recSalesHeader."Document Type"::Invoice);
              recSalesHeader.SETFILTER("No.", InvoiceNo);
              recSalesHeader.DELETEALL(TRUE);
              {
              SalesHeaderResult := recSalesHeader.GET(recSalesHeader."Document Type"::Invoice, InvoiceNo);
              IF SalesHeaderResult THEN BEGIN
                recSalesHeader.DELETE(TRUE);
              END;
              }
            END
            ELSE IF InvoiceType = InvoiceType::"Purchase Invoice" THEN BEGIN
              IF recPurchaseHeader.GET(recPurchaseHeader."Document Type"::Invoice, InvoiceNo) THEN BEGIN
                recPurchaseHeader.DELETE(TRUE);
              END;
            END
            ELSE IF InvoiceType = InvoiceType::"Sales Credit Memo" THEN BEGIN
              IF recSalesHeader.GET(recSalesHeader."Document Type"::"Credit Memo", InvoiceNo) THEN BEGIN
                recSalesHeader.DELETE(TRUE);
              END;
            END
            ELSE IF InvoiceType = InvoiceType::"Purchase Credit Memo" THEN BEGIN
              IF recPurchaseHeader.GET(recPurchaseHeader."Document Type"::"Credit Memo", InvoiceNo) THEN BEGIN
                recPurchaseHeader.DELETE(TRUE);
              END;
            END;
      END;
    END;
  UNTIL ExcelBuf.NEXT = 0;
END;
Window.CLOSE;

Imported the Excel, process started, some of Invoices from the top of the excel are deleted. Suddenly, it stops the process, no roleback. (36 has commit after delete.)

The first IF block is altered version. I've tried several versions but first, the error:

Debugger :

I am checking the SH, specified Invoice No in the error is deleted.

I import the exact same excel again, first 20 rows (which are deleted from the table and all related tables when i first imported the excel) gives no error, so code block works, a new group of rows after the first 20 deleted again, and boom again the same error with different Invoice No.

I've tried to setrange it to bypass GET. Because at first i've wrote first IF block as the purchase part of the code. and get the debugger error at GET.

And also as you can see from the closed block, I've created a boolean variable which only holds IF i get the record or not.

ALL 3 ended with this error.

By the way, I've checked the Sales Header/ line tables' codes for any errors, confirms etc. There is none..

It smells like a bug but i can not be sure.

Are there anyone who encountered the same issue?

Regards,

Can

Quick Links

Leaderboard