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]

Access your SQL server through dotnet interop

As the Icelandic localization for NAV 2013 is supposed to land here in April it is now time to start moving the solutions to the new platform.  One of the things that I have used are the automation variables for SQL server connection.  In NAV 2013 I plan to replace all automation variables with dotnet variables.  Here is a code that could help you replacing some of your automation variables.[code]OBJECT Codeunit 50000 SQL Mgt.
{
OBJECT-PROPERTIES
{
Date=07.03.13;
Time=14:58:29;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
BEGIN
END;

}
CODE
{
VAR
ClientFile@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
SQLConnection@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection";
SigningSetup@1200050000 : Record 10017128;
HostSetup@1200050027 : Record 10001061;
FileMgt@1200050026 : Codeunit 419;
SigningTools@1200050006 : Codeunit 10017129;
NoSeriesMgt@1200050011 : Codeunit 396;
HasGotSigningSetup@1200050001 : Boolean;
Text001@1200050004 : TextConst ‘ENU=Table %1 does not exist;ISL=Tafla %1 er ekki fyrir hendi’;

PROCEDURE GetSigningSetup@1200050001();
BEGIN
IF NOT HasGotSigningSetup THEN BEGIN
SigningSetup.GET;
IF SigningSetup."Default Storage" = SigningSetup."Default Storage"::"Web Site" THEN BEGIN
SigningSetup.TESTFIELD("Database Server Name");
SigningSetup.TESTFIELD("Database Name");
SigningSetup.TESTFIELD("Database Table Name");
SigningSetup.TESTFIELD("Database Permission Table Name");
SigningSetup.TESTFIELD("Database Login Name");
SigningSetup.TESTFIELD("Database User Password");
SigningSetup.TESTFIELD("Link Reference Nos.");
END;
SigningTools.TryFindHostSetup(HostSetup);
HasGotSigningSetup := TRUE;
END;
END;

PROCEDURE OpenLinkDB@1200050000();
VAR
ConnectionString@1200050000 : Text[1024];
BEGIN
GetSigningSetup;

IF ISNULL(SQLConnection) THEN BEGIN
ConnectionString :=
STRSUBSTNO(‘Data Source=%1;Initial Catalog=%2;User Id=%3;Password=%4′,
SigningSetup."Database Server Name",
SigningSetup."Database Name",
SigningSetup."Database Login Name",
SigningSetup."Database User Password");

SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;
END;
END;

PROCEDURE CloseLinkDB@1200050004();
BEGIN
IF ISNULL(SQLConnection) THEN
EXIT;

IF SQLConnection.State = 1 THEN
SQLConnection.Close;
END;

PROCEDURE TableExists@1200050006(TableName@1200050000 : Text[50];ShowError@1200050001 : Boolean) : Boolean;
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLReader@1000000001 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Data.SqlClient.SqlDataReader" WITHEVENTS;
Query@1200050002 : Text[1024];
BEGIN
OpenLinkDB;

Query :=
STRSUBSTNO(‘SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ”%1” AND TABLE_TYPE = ”BASE TABLE”’,
TableName);

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLReader := SQLCommand.ExecuteReader;
IF SQLReader.HasRows THEN
EXIT(TRUE)
ELSE
IF ShowError THEN
ERROR(Text001,TableName)
ELSE
EXIT(FALSE);
END;

PROCEDURE CreateLinkTable@1200050020();
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
Guid@1200050000 : Text[40];
Query@1000000001 : Text[1024];
BEGIN
OpenLinkDB;

Guid := CREATEGUID;

Query :=
STRSUBSTNO(‘CREATE TABLE [%1](‘,SigningSetup."Database Table Name") +
‘[Link ID] [varchar](20) NOT NULL,’ +
‘[Table ID] [int] NOT NULL,’ +
‘[Period] [varchar](6) NOT NULL,’ +
‘[User ID] [varchar](20) NOT NULL,’ +
‘[Computer Name] [varchar](50) NOT NULL,’ +
‘[File Name] [varchar](1024) NOT NULL,’ +
‘[Content Type] [varchar](50) NOT NULL,’ +
‘[Object] [image] NULL,’ +
STRSUBSTNO(‘ CONSTRAINT [PK_%1_ID] PRIMARY KEY CLUSTERED’,Guid) +
‘([Link ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,’ +
‘IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]’ +
‘) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];’;

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinately for the command to finish.
SQLCommand.ExecuteNonQuery;
END;

LOCAL PROCEDURE UploadFileToSQL@1200050011(FileName@1200050000 : Text[1024];TableID@1200050007 : Integer) URL : Text[1024];
VAR
SQLCommand@1000000005 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLdbType@1000000003 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlDbType";
TempFileName@1100408001 : Text[1024];
Query@1000000006 : Text[1024];
ComputerName@1200050002 : Code[50];
LinkID@1200050006 : Code[20];
InsertCount@1000000007 : Integer;
BEGIN
TempFileName := FileMgt.UploadFileSilent(FileName);
OpenLinkDB;

SigningSetup.TESTFIELD("Link Web URL");
ComputerName := SigningTools.GetComputerName;

LinkID := NoSeriesMgt.GetNextNo(SigningSetup."Link Reference Nos.",TODAY,TRUE);
URL := SigningSetup."Link Web URL" + STRSUBSTNO(‘&ID=%1’,LinkID);

Query := STRSUBSTNO(
‘INSERT INTO [%1] ‘ +
‘ ([Link ID] ‘ +
‘ ,[Table ID] ‘ +
‘ ,[Period] ‘ +
‘ ,[User ID] ‘ +
‘ ,[Computer Name] ‘ +
‘ ,[File Name] ‘ +
‘ ,[Content Type] ‘ +
‘ ,[Object]) ‘ +
‘ VALUES ‘ +
‘ (@LinkID ‘ + // varchar(20)
‘ ,@TableID ‘ + // int
‘ ,@Period ‘ + // varchar(6)
‘ ,@UserID ‘ + // varchar(20)
‘ ,@ComputerName ‘ + // varchar(50)
‘ ,@FileName ‘ + // varchar(1024)
‘ ,@ContentType ‘ + // varchar(50)
‘ ,@Object) ‘ + // image
”,SigningSetup."Database Table Name");

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLCommand.Parameters.Add(‘@LinkID’,SQLdbType.VarChar,20).Value := LinkID;
SQLCommand.Parameters.Add(‘@TableID’,SQLdbType.Int).Value := TableID;
SQLCommand.Parameters.Add(‘@Period’,SQLdbType.VarChar,6).Value := FORMAT(TODAY,0,'<Year4><Month,2>’);
SQLCommand.Parameters.Add(‘@UserID’,SQLdbType.VarChar,20).Value := USERID;
SQLCommand.Parameters.Add(‘@ComputerName’,SQLdbType.VarChar,50).Value := ComputerName;
SQLCommand.Parameters.Add(‘@FileName’,SQLdbType.VarChar,1024).Value := FileMgt.GetFileName(FileName);
SQLCommand.Parameters.Add(‘@ContentType’,SQLdbType.VarChar,50).Value := SigningTools.ContentType(FileName);
SQLCommand.Parameters.Add(‘@Object’,SQLdbType.Image).Value := ServerFile.ReadAllBytes(TempFileName);
InsertCount := SQLCommand.ExecuteNonQuery;
ServerFile.Delete(TempFileName);
END;

LOCAL PROCEDURE DownloadFileFromWeb@1100408027(URL@1200050000 : Text[1024]) FileName : Text[1024];
VAR
HttpWebRequest@1100408002 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebRequest";
HttpWebResponse@1100408001 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebResponse";
HttpWebHeaderCollection@1100408003 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebHeaderCollection";
Credential@1100408008 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkCredential";
MemoryStream@1100408004 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
ServerFileStream@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.FileStream";
TempBlob@1100408009 : TEMPORARY Record 99008535;
Document@1100408011 : BigText;
OutStr@1100408007 : OutStream;
InStr@1100408010 : InStream;
TempFile@1100408006 : File;
FilePath@1200050001 : Text[1024];
TempPath@1100408000 : Text[1024];
BEGIN
GetSigningSetup;
Credential := Credential.NetworkCredential;
Credential.UserName := SigningSetup."Database Login Name";
Credential.Password := SigningSetup."Database User Password";
Credential.Domain := ‘.’;
HttpWebRequest := HttpWebRequest.Create(URL);
HttpWebRequest.Timeout := 30000;
HttpWebRequest.Credentials := Credential;
HttpWebRequest.Method := ‘GET’;
HttpWebResponse := HttpWebRequest.GetResponse;
HttpWebHeaderCollection := HttpWebResponse.Headers;

FileName := HttpWebHeaderCollection.Get(‘Content-Disposition’);
IF STRPOS(FileName,’filename=’) = 0 THEN
FileName := ”
ELSE BEGIN
FileName := COPYSTR(FileName,STRPOS(FileName,’filename=’) + 9);
TempPath :=
FileMgt.GetDirectoryName(FileMgt.ServerTempFileName(”)) +
FileName;
TempFile.CREATE(TempPath);
TempFile.CREATEOUTSTREAM(OutStr);
COPYSTREAM(OutStr,HttpWebResponse.GetResponseStream);
TempFile.CLOSE;
FileName := FileMgt.DownloadTempFile(TempPath);
END;
END;

PROCEDURE OpenLinksForRecRef@1200050005(VAR RecRef@1200050000 : RecordRef);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
OpenRecordLinks(RecordLinks);
END;
END;

PROCEDURE MailLinksForRecRef@1100408004(VAR RecRef@1200050000 : RecordRef;SuggestedEMailAddr@1100408000 : Text[1024];Body@1100408001 : Text[1024]);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
MailRecordLinks(RecordLinks,SuggestedEMailAddr,Body);
END;
END;

PROCEDURE OpenRecordLinks@1100408000(VAR RecordLinks@1100408000 : Record 2000000068);
VAR
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN BEGIN
HYPERLINK(FileName);
END ELSE
HYPERLINK(URL);
UNTIL NEXT = 0;
END;
END;

PROCEDURE MailRecordLinks@1100408003(VAR RecordLinks@1100408000 : Record 2000000068;SuggestedEMailAddr@1100408011 : Text[1024];Body@1100408012 : Text[1024]);
VAR
Mail@1000000000 : Codeunit 397;
URL@1100408002 : Text[1024];
FileName@1100408001 : Text[1024];
BEGIN
Mail.NewMessage(SuggestedEMailAddr,”,Text031,Body,”,TRUE);

WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN
Mail.AttachFile(FileName)
ELSE
Mail.AttachFile(URL);
UNTIL NEXT = 0;
END;

Mail.Send;
END;

PROCEDURE MailFile@1100408002(VAR FileName@1100408011 : Text[1024];VAR Description@1100408000 : Text[250]);
VAR
Mail@1000000000 : Codeunit 397;
BEGIN
Mail.NewMessage(”,”,Description,”,FileName,TRUE);
Mail.Send;
END;

EVENT SQLConnection@1000000000::InfoMessage@48(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlInfoMessageEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::StateChange@49(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.StateChangeEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::Disposed@50(sender@1000000001 : Variant;e@1000000000 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.EventArgs");
BEGIN
END;

BEGIN
{
You experience bad performance when you scroll any list in the classic client in Microsoft Dynamics NAV 2009

To resolve this problem, use one of the following methods:
You can create a required index directly in the SQL database for the Record Links table by using Microsoft SQL Server Management Stu
You can execute the following TSQL statement in Microsoft NAV 2009 database:

CREATE INDEX Sign_Index ON [Record Link] ([Record ID], [Type], Company)
}
END.
}
}

[/code]