Sunday, November 29, 2015

How to modify the Sales Invoice Report to print Serial No./Lot No.


question-686336_1920
There are several requests on the Microsoft Dynamics Community forum to get an example or for the logic to print the serial no. or lot no. on the sales invoice report. In this post I will explain how to modify the standard sales invoice report (Report 10074: NAV 2015) to print the serial no. or lot no. on the report.
For this purpose I have added a new function in the report 10074, called GetSerialLotNo
LOCAL PROCEDURE GetSerialLotNo@1240060002();
    VAR
      ItemTrackingMgt@1240060000 : Codeunit 6500;
      TempItemLedgEntry@1240060001 : TEMPORARY Record 32;
      InvoiceRowID@1240060002 : Text[150];
      LineNo@1240060003 : Integer;
      Inserted@1240060004 : Boolean;
      SerialLot@1240060005 : Code[60];
      ValueEntryRelation@1240060006 : Record 6508;
      ValueEntry@1240060007 : Record 5802;
      ItemLedgEntry@1240060008 : Record 32;
    BEGIN
      
      IF TempSalesInvoiceLine.Type <> TempSalesInvoiceLine.Type::Item THEN
        EXIT;
      TempItemLedgEntry.RESET;
      TempItemLedgEntry.DELETEALL;
      InvoiceRowID := TempSalesInvoiceLine.RowID1;
      ValueEntryRelation.RESET;
      ValueEntryRelation.SETCURRENTKEY("Source RowId");
      ValueEntryRelation.SETRANGE("Source RowId",InvoiceRowID);
      IF ValueEntryRelation.FIND('-') THEN BEGIN
        REPEAT
          ValueEntry.GET(ValueEntryRelation."Value Entry No.");
          ItemLedgEntry.GET(ValueEntry."Item Ledger Entry No.");
          TempItemLedgEntry := ItemLedgEntry;
          TempItemLedgEntry.Quantity := ValueEntry."Invoiced Quantity";
          IF TempItemLedgEntry."Entry Type" IN [TempItemLedgEntry."Entry Type"::Purchase,TempItemLedgEntry."Entry Type"::Sale] THEN
            IF TempItemLedgEntry.Quantity <> 0 THEN
              TempItemLedgEntry.INSERT;
        UNTIL ValueEntryRelation.NEXT = 0;
      END;
      IF TempItemLedgEntry.FINDFIRST THEN
        REPEAT
          SerialLot := TempItemLedgEntry."Serial No." + ' ' + TempItemLedgEntry."Lot No.";
          WITH TempSalesInvoiceLine DO BEGIN
            INIT;
            "Document No." := "Sales Invoice Header"."No.";
            "Line No." := HighestLineNo + 10;
            HighestLineNo := "Line No.";
          END;
          IF STRLEN(SerialLot) + 1 <= MAXSTRLEN(TempSalesInvoiceLine.Description) THEN BEGIN
            TempSalesInvoiceLine.Description := SerialLot;
            TempSalesInvoiceLine."Description 2" := '';
          END ELSE BEGIN
            SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            WHILE (SpacePointer > 1) AND (SerialLot[SpacePointer] <> ' ') DO
              SpacePointer := SpacePointer - 1;
            IF SpacePointer = 1 THEN
              SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            TempSalesInvoiceLine.Description := COPYSTR(SerialLot,1,SpacePointer - 1);
            TempSalesInvoiceLine."Description 2" :=
              COPYSTR(COPYSTR(SerialLot,SpacePointer + 1),1,MAXSTRLEN(TempSalesInvoiceLine."Description 2"));
          END;
          TempSalesInvoiceLine.INSERT;
        UNTIL TempItemLedgEntry.NEXT = 0;
      
    END;



The Serial No. and Lot No. information is stored in the item ledger entry table, so the above function is used to retrieve all the item ledger entries associated to a sales invoice line, and then store those into a temporary ledger entry table, by using the temporary ledger entry table we populate TempSalesInvoiceline table, which will take care of displaying the values on the report without modifying/formatting RTC report.

To print the serial no./ lot no. we just call the new function GetSerialLotNo on the OnAfterGetRecord of the Sales Invoice Line DataItem.

Below is an example of the invoice report printing serial no.

image














Download the object from this link Sales Invoice Report

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.
Share:

10 comments:

Unknown said...

Great article.

Is it possible to use this in NAV 2016 as well?

Suresh Kulla said...

Yes, you can use the same logic in NAV 2016.

Unknown said...

Hi Suresh,

We tried to import your report 50001 into our NAV2016 environment, unfortunaly without any luck. It reports an error "TableData 10011 does not exist." when we try to compile it.

Suresh Kulla said...

Henrik,

I have imported this in my NAV 2016 DB without any issues, are you using North American NAV version or some other, if you are using other country version you just need to copy the function specified in the blog in your sales invoice report and call the function as described.

Please let me know if I can be of any help.

Thanks
Suresh

Unknown said...

We are using a Danish NAV version, so that might be the problem (We do not have report 10074)

But thanks for your time and feedback.

samia87 said...

Awesome post and informative too.
You can try the printing version of invoice books for your small business to cover up the selling and buying details.

Mudassir Iqbal said...

From the starting line of the blog, I came to recognize that it is going to be merit the time to comprehend your post. It kept me immersed the whole time. I never knew this blog can be of my concern. It was fascinating and enticing. I will without a doubt share your blog with all my acquaintances via email.

Printing Services Australia.

saivenkat said...

This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
best quoting software

Amusan said...

Hi, I am unable to download the object using the link provided. Kindly help me reupload and check the link. The suggested approach will be really helpful to me. Thanks in advance

InvoiceToo said...

This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post.! Best free invoicing service provider.