Then new Job Invoice feature in NAV 2013

As a part of the Punch and Time Sheet system that I have developed for NAV 2009 I wrote some added features to the Jobs module.  One of the features was to be able to use the copy document functionality to reverse a job invoice.

The new table 1022, Job Planning Line Invoice and the changed functionality means that I needed to rewrite this functionality in NAV 2013.  Today I saw two things that I think Microsoft should do to improve the use of this table.

The first improvement is to add a key to the table for “Document Type,Document No.,Line No.”.  All these fields are a part of the primary key but my experience suggests that the SQL server will perform better with this key added.  After adding this key I suggest a change in the function DeleteSalesLine in function 1002, Job Create-Invoice to utilize this new key.

[code]

DeleteSalesLine(SalesLine : Record "Sales Line")
WITH JobPlanningLineInvoice DO BEGIN
//#Dynamics.is-
SETCURRENTKEY("Document Type","Document No.","Line No.");
//#Dynamics.is+
CASE SalesLine."Document Type" OF
SalesLine."Document Type"::Invoice:[/code]

The later improvement is in function PostInvoiceContractLine in codeunit 1001, Job Post-Line. Here Microsoft is using the RENAME function for the Job Planning Line Invoice table. I would suggest a DELETE and an INSERT to replace the RENAME function.

[code]
CASE SalesHeader."Document Type" OF
SalesHeader."Document Type"::Invoice:
IF JobPlanningLineInvoice.GET(JobPlanningLine."Job No.",JobPlanningLine."Job Task No.",JobPlanningLine."Line No.",
JobPlanningLineInvoice."Document Type"::Invoice,SalesHeader."No.",SalesLine."Line No.")
THEN BEGIN
//#Dynamics.is-
// JobPlanningLineInvoice.RENAME(JobPlanningLine."Job No.",JobPlanningLine."Job Task No.",JobPlanningLine."Line No.",
// JobPlanningLineInvoice."Document Type"::"Posted Invoice",SalesLine."Document No.",SalesLine."Line No.");
JobPlanningLineInvoice.DELETE;
JobPlanningLineInvoice."Document Type" := JobPlanningLineInvoice."Document Type"::"Posted Invoice";
JobPlanningLineInvoice."Document No." := SalesLine."Document No.";
JobPlanningLineInvoice."Line No." := SalesLine."Line No.";
//#Dynamics.is+
JobPlanningLineInvoice."Invoiced Date" := SalesHeader."Posting Date";
JobPlanningLineInvoice."Invoiced Amount (LCY)" :=
CalcLineAmountLCY(JobPlanningLine,JobPlanningLineInvoice."Quantity Transferred");
JobPlanningLineInvoice."Invoiced Cost Amount (LCY)" :=
JobPlanningLineInvoice."Quantity Transferred" * JobPlanningLine."Unit Cost (LCY)";
IF JobLedgEntry.FINDLAST THEN
JobPlanningLineInvoice."Job Ledger Entry No." := JobLedgEntry."Entry No." + 1
ELSE
JobPlanningLineInvoice."Job Ledger Entry No." := 1;
//#Dynamics.is-
// JobPlanningLineInvoice.MODIFY;
JobPlanningLineInvoice.INSERT;
//#Dynamics.is+
END;
SalesHeader."Document Type"::"Credit Memo":
IF JobPlanningLineInvoice.GET(JobPlanningLine."Job No.",JobPlanningLine."Job Task No.",JobPlanningLine."Line No.",
JobPlanningLineInvoice."Document Type"::"Credit Memo",SalesHeader."No.",SalesLine."Line No.")
THEN BEGIN
//#Dynamics.is-
// JobPlanningLineInvoice.RENAME(JobPlanningLine."Job No.",JobPlanningLine."Job Task No.",JobPlanningLine."Line No.",
// JobPlanningLineInvoice."Document Type"::"Posted Credit Memo",SalesLine."Document No.",SalesLine."Line No.");
JobPlanningLineInvoice.DELETE;
JobPlanningLineInvoice."Document Type" := JobPlanningLineInvoice."Document Type"::"Posted Credit Memo";
JobPlanningLineInvoice."Document No." := SalesLine."Document No.";
JobPlanningLineInvoice."Line No." := SalesLine."Line No.";
//#Dynamics.is+
JobPlanningLineInvoice."Invoiced Date" := SalesHeader."Posting Date";
JobPlanningLineInvoice."Invoiced Amount (LCY)" :=
CalcLineAmountLCY(JobPlanningLine,JobPlanningLineInvoice."Quantity Transferred");
JobPlanningLineInvoice."Invoiced Cost Amount (LCY)" :=
JobPlanningLineInvoice."Quantity Transferred" * JobPlanningLine."Unit Cost (LCY)";
IF JobLedgEntry.FINDLAST THEN
JobPlanningLineInvoice."Job Ledger Entry No." := JobLedgEntry."Entry No." + 1
ELSE
JobPlanningLineInvoice."Job Ledger Entry No." := 1;
//#Dynamics.is-
// JobPlanningLineInvoice.MODIFY;
JobPlanningLineInvoice.INSERT;
//#Dynamics.is+
END;
END;[/code]The functionality that I added to the Copy Document function is to create a new line in the Job Planning Line Invoice table if the user is copying a posted sales invoice to a credit memo with header included and identical lines. This also means that I have the Job fields in the credit memo lines populated and the Job Planning Line updated. The function that I use is[code]
ReverseJobInvoice(FromSalesInvLine : Record "Sales Invoice Line";VAR ToSalesLine : Record "Sales Line")
WITH JobInvoice DO BEGIN
SETCURRENTKEY("Document Type","Document No.","Line No.");
SETRANGE("Document Type","Document Type"::"Posted Invoice");
SETRANGE("Document No.",FromSalesInvLine."Document No.");
SETRANGE("Line No.",FromSalesInvLine."Line No.");
IF FINDFIRST THEN BEGIN
JobPlanningLine.GET("Job No.","Job Task No.","Job Planning Line No.");
CreditJobInvoice := JobInvoice;
CreditJobInvoice."Document Type" := CreditJobInvoice."Document Type"::"Credit Memo";
CreditJobInvoice."Document No." := ToSalesLine."Document No.";
CreditJobInvoice."Line No." := ToSalesLine."Line No.";
CreditJobInvoice."Quantity Transferred" := -ToSalesLine.Quantity;
CreditJobInvoice."Transferred Date" := TODAY;
CreditJobInvoice."Invoiced Date" := 0D;
CreditJobInvoice."Invoiced Amount (LCY)" := 0;
CreditJobInvoice."Invoiced Cost Amount (LCY)" := 0;
CreditJobInvoice."Job Ledger Entry No." := 0;
CreditJobInvoice.INSERT;
ToSalesLine."Job No." := JobPlanningLine."Job No.";
ToSalesLine."Job Task No." := JobPlanningLine."Job Task No.";
ToSalesLine."Job Contract Entry No." := JobPlanningLine."Job Contract Entry No.";
ToSalesLine."Job Credit Invoice Line" := TRUE;
ToSalesLine.MODIFY;
JobPlanningLine.UpdateQtyToTransfer;
JobPlanningLine.MODIFY;
END;
END;[/code]

Creating Web Services in NAV 2009

I have been working on NAV Time Registration and the solution is almost ready.  The last step was to create a web service that supports stand-alone punching clocks, both in .net c# and also as a NAV client.

The web service is a standard codeunit with functions.  I make sure the functions that are not to be published have the Local property set to Yes.

Using XMLport in web services requires a few parameter changes.  First I include them in the function parameters.  I use a boolean parameter in Return Value as a success flag.

Then in XMLport properties I change direction to export, change format to XML and select to use default namespace.

The C/Side code to answer this web service is.
[code htmlscript=”false”]IF NOT ValidateClockID(ClockID,ResponseMessage) THEN BEGIN
InsertLog(ClockID,Log.GetEmployeeList,FALSE,ResponseMessage,”);
EXIT(FALSE);
END;

IF NOT CreateEmployeeBuffer(EmployeeBuffer,ResponseMessage) THEN BEGIN
InsertLog(ClockID,Log.GetEmployeeList,FALSE,ResponseMessage,”);
EXIT(FALSE);
END;

EmployeeList.SetEmployeeList(PunchClock.Code,EmployeeBuffer);
ResponseMessage := Text033;
InsertLog(ClockID,Log.GetEmployeeList,TRUE,”,”);
EXIT(TRUE);[/code]
Then finally I run form 860, Web Services and add a line for this web service to be published.

Next post is a demonstration on how to use this web service.  I used Freddys multiple service tier post in my developement enviroment to setup NAV services.

 

Data Upgrade Method and Code

I am in the process of upgrading a 5.0 SP1 database to 2009 R2.  That includes updated addon solutions from Skýrr.  To help me with this process I created a table, form and a codeunit to handle the upgrade and provide the user a visual process information.

This code will store information on processed tables in the log table and make sure that if you execute the upgrade process only the remaining processes will be executed.  You can simply add another processing codeunit and execute the process again and again.

UPGRADE TOOL

Renumbering tool for Dynamics NAV Objects

I needed to renumber objects for Skyrr Payroll addon and found on Mibuso an old renumbering tool.  I downloaded it and installed.  I corrected the bug and created an import function for Excel worksheet.

The Excel worksheet has the object type in column A, object ID in column B, object name in column C, object caption in column D and the object new ID in column E.  The first row is the header row.  I simply copied my object list from Object Designer and worked out my new ids in Excel.

After importing this Excel worksheet I have a list of new and old IDs in NAV.

If any of the new IDs is an old ID, you will need to renumber first from the old ID to temporary ID and then do another renumbering to the new ID.

I create two sheets in my Excel workbook to solve this.

This renumbering tool does not support Pages and I did not need that support in this case.

Renumbering Tool Source Code

Transferring small amount of data between databases

Most of us have needed to copy data from one Dynamics NAV database to another.  For example the posting setup tables, payment terms, currency, etc.

If the databases are not identical you will not be able to copy and paste the data and you have to solve this problem with dataports or XMLPorts.

I offer a solution to this problem.  I have created a form that is able to export and import data based on table and field numbers.  It will import data to a table even if some fields are missing in the destination database.

Here is the source code

SQL Maintenance from Dynamics NAV

When running Dynamics NAV on a MS-SQL database you need to execute some maintenance tasks.  They include defrag and rebuild for indexes and statistics update.  By using SQL Native Client, the Table Information from NAV and Job Queue you can automate this maintenance with Dynamics NAV Application Server.

First you must make sure that automatic update statistics and auto shrink are set to false.

Then you create codeunit for each task.  Here is an example.

Variables
[code]TableInformation@1100409000 : Record 2000000028;
ADOConnection@1100409004 :
Automation "’Microsoft ActiveX Data Objects 2.8 Library’.Connection";
SQLSetup@1100409006 : Record 10017176;
Window@1100409003 : Dialog;
WindowLastUpdated@1100409002 : DateTime;
Counter@1100409001 : Integer;
Total@1100409000 : Integer;
Text001@1100409005 :
TextConst ‘ENU=Microsoft ADO not found.;ISL=Microsoft ADO finnst ekki.’;[/code]
Code for Rebuild Indexes using “DBCC DBREINDEX”
[code]SQLSetup.GET;
SQLSetup.TESTFIELD("SQL Server Name");
SQLSetup.TESTFIELD("SQL Database name");
IF GUIALLOWED THEN
Window.OPEN(‘#1####################################\\’ +
‘@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@’);
WindowLastUpdated := CURRENTDATETIME;

IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
STRSUBSTNO(
‘Driver={SQL Native Client};Server=%1;’ +
‘Database=%2;Trusted_Connection=yes;’,
SQLSetup."SQL Server Name",
SQLSetup."SQL Database name"));
ADOConnection.CommandTimeout(0);

WITH TableInformation DO BEGIN
SETFILTER("No. of Records",’>0′);
SETFILTER("Table No.",'<>150014&<2000000002′);
Total := COUNTAPPROX;
Counter := 0;
IF FINDSET THEN REPEAT
Counter := Counter + 1;
IF GUIALLOWED THEN
IF (CURRENTDATETIME – WindowLastUpdated) > 1000 THEN BEGIN
Window.UPDATE(1,"Table Name");
Window.UPDATE(2,ROUND(Counter / Total * 10000,1));
WindowLastUpdated := CURRENTDATETIME;
END;

IF "Company Name" <> ” THEN
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1$%2],””,90);’,
CONVERTSTR("Company Name",’."\/”’,’_____’),
CONVERTSTR("Table Name",’."\/”’,’_____’)))
ELSE
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1],””,90);’,
CONVERTSTR("Table Name",’."\/”’,’_____’)))

UNTIL NEXT = 0;
END;

ADOConnection.Close;[/code]
This example is using trusted connection to the SQL Server, that is a domain user account.  Grant that user the processadmin server role.

To defragment indexes, use “DBCC INDEXDEFRAG”.

Skýrr has an addon solution that includes all these functions and more with granule 10,017,160