Category: Activity

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]