Blogs

Creating a Custom Job Queue to create Sales order from Purchase order in MS Dynamics 365 Business Central using AL Code

Inkey, August 13, 2024 749 Views

Microsoft Dynamics 365 Business Central is a powerful Enterprise Resource Planning (ERP) solution that offers a wide range of customization options to meet the unique needs of your business. One such customization is to create a custom job queue to create Sales order from Purchase order in Business Central. A job queue in Microsoft Dynamics 365 Business Central is a feature that allows you to schedule and automate tasks to be executed in the background. This can be anything from data synchronization to custom code execution. By creating a custom job queue, you can automate specific business processes, like the conversion of purchase orders to sales orders.

Requirement:

We recently encountered a requirement where we regularly receive purchase orders and we need to convert these into sales orders for the customers. Instead of doing this manually, we can create a custom job queue to handle the conversion process automatically.

Prerequisites:

• Access to a Dynamics 365 Business Central instance with administrator privileges.
• Visual Studio Code with AL Language extension installed.

Solution (Implementation Steps):

In Dynamics 365 Business Central, To create a custom job queue for converting purchase orders to sales orders, follow these implementation steps:

Step 1: Create a Custom Report in AL

• In the Visual Studio Code, create a Custom Report in AL.
• In the dataset, we define a data item for “Purchase Header” and another for “Purchase Line.” We specify filtering conditions to retrieve relevant data.
• Inside the Report, Within the “OnAfterGetRecord” trigger, you can write custom AL code to convert purchase orders to sales orders.
• We initiate a new sales header record (“Sales Header”) and search for an existing sales order related to the purchase order. If a corresponding sales order is not found, we populate the sales header with necessary data from the purchase header such as customer information, addresses, shipping details, and other relevant data.
• We insert the sales header and look for existing sales lines. If found, we can choose to update or skip them based on your business logic. If not found, a new sales line is created. We also update the purchase header with a reference to the newly created sales order for future tracking.

report 50100 VAS_PurchaseOrder
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    ProcessingOnly = true;
    UseRequestPage = false;
    // DefaultRenderingLayout = LayoutName;

    dataset
    {
        dataitem("Purchase Header"; "Purchase Header")
        {
            // RequestFilterFields = "No.";
            DataItemTableView = where("Is_Canceled" = const(false),"CAU Accptance Ref. No." = filter(<> ''));
            // DataItemTableView = where("Document Type" = const(Order));
            dataitem("Purchase Line"; "Purchase Line")
            {
                DataItemLink = "Document No." = field("No.");
                trigger OnAfterGetRecord()
                var
                    myInt: Integer;
                    SalesHeader: Record "Sales Header";
                    SalesLine: Record "Sales Line";
                    PurchaseHeader: Record "Purchase Header";
                begin
                    if ("Purchase Header"."Document Type" = "Purchase Header"."Document Type"::Order) then begin
                        SalesHeader.INIT();
                        // Try to find an existing sales order for the same purchase order
                        IF NOT SalesHeader.GET("Purchase Header"."Document Type", "Purchase Header"."No.") THEN BEGIN
                            // Populate sales header from purchase header
                            SalesHeader."Document Type" := "Purchase Header"."Document Type"::Order;
                            SalesHeader."No." := "Purchase Header"."No.";
                            SalesHeader.Validate("Sell-to Customer No.",'F00030');
                            SalesHeader."Sell-to Customer Name" := 'Sam';
                            SalesHeader.SourceId := "Purchase Header".SourceId;
                            SalesHeader.SourceName := "Purchase Header".SourceName;
                            SalesHeader."Sell-to Address" := "Purchase Header"."Buy-from Address";
                            SalesHeader."Sell-to City" := "Purchase Header"."Buy-from City";
                            SalesHeader."Sell-to County" := "Purchase Header"."Buy-from County";
                            SalesHeader."Sell-to Post Code" := "Purchase Header"."Buy-from Post Code";
                            SalesHeader."Sell-to Country/Region Code" := "Purchase Header"."Buy-from Country/Region Code";
                            SalesHeader."Sell-to Contact" := "Purchase Header"."Buy-from Contact";
                            SalesHeader."Sell-to Contact No." := "Purchase Header"."Buy-from Contact No.";
                            SalesHeader."Ship-to Address" := "Purchase Header"."Ship-to Address";
                            SalesHeader."Ship-to City" := "Purchase Header"."Ship-to City";
                            SalesHeader."Ship-to Code" := "Purchase Header"."Ship-to Code";
                            SalesHeader."Ship-to Contact" := "Purchase Header"."Ship-to Contact";
                            SalesHeader."Ship-to Country/Region Code" := "Purchase Header"."Ship-to Country/Region Code";
                            SalesHeader."Ship-to County" := "Purchase Header"."Ship-to County";
                            SalesHeader."Ship-to Name" := "Purchase Header"."Ship-to Name";
                            SalesHeader."Ship-to Post Code" := "Purchase Header"."Ship-to Post Code";
                            SalesHeader."Shipment Method Code" := "Purchase Header"."Shipment Method Code";
                            SalesHeader.Validate("Bill-to Customer No.",'F00030');
                            SalesHeader.Validate("Location Code","Purchase Header"."Location Code");
                            SalesHeader.Validate("VAT Bus. Posting Group","Purchase Header"."VAT Bus. Posting Group");
                            SalesHeader."Reference Origin ID" := "Purchase Header"."Reference Origin ID";
                            SalesHeader."External Document No." := "Purchase Header"."Reference Origin ID";
                            SalesHeader."Due Date" := WorkDate() + 1;
                            // Insert the sales header
                            SalesHeader.INSERT();
                            SalesLine.Reset();
                            SalesLine.SETRANGE("Document Type", SalesHeader."Document Type");
                            SalesLine.SETRANGE("Document No.", SalesHeader."No.");
                            SalesLine.SETRANGE("Line No.", "Purchase Line"."Line No.");
                            // Check if the sales line already exists
                            IF SalesLine.FindSet() THEN BEGIN
                                // Sales line exists, update it or skip it based on your logic
                                SalesLine.Description := "Purchase Line".Description;
                                SalesLine."Unit of Measure Code" := "Purchase Line"."Unit of Measure Code";
                                SalesLine.Validate(Quantity,"Purchase Line".Quantity);
                                SalesLine."Unit Price" := "Purchase Line"."Unit Cost" + ("Purchase Line"."Unit Cost" * 0.03);
                                SalesLine.Validate("Location Code","Purchase Line"."Location Code");
                                SalesLine.Validate("Line Amount","Purchase Line"."Line Amount" + ("Purchase Line"."Line Amount" * 0.03));
                                SalesLine.Validate("Gen. Prod. Posting Group","Purchase Line"."Gen. Prod. Posting Group");
                                SalesLine.Validate("Gen. Bus. Posting Group","Purchase Line"."Gen. Bus. Posting Group");
                                SalesLine.Validate("Qty. to Ship","Purchase Line"."Qty. to Receive");
                                SalesLine.Validate("Qty. to Invoice","Purchase Line"."Qty. to Invoice");
                                SalesLine.Validate("VAT Bus. Posting Group","Purchase Line"."VAT Bus. Posting Group");
                                SalesLine.Modify(true);
                            END ELSE BEGIN
                                SalesLine.Init();
                                // Sales line doesn't exist, create a new sales line
                                SalesLine."Document Type" := SalesHeader."Document Type";
                                SalesLine."Document No." := SalesHeader."No.";
                                SalesLine."Line No." := "Purchase Line"."Line No.";
                                SalesLine.Validate("Sell-to Customer No.",SalesHeader."Sell-to Customer No.");
                                SalesLine."Type" := SalesLine."Type"::Item;
                                SalesLine.Validate("No.","Purchase Line"."No.");
                                SalesLine.Description := "Purchase Line".Description;
                                SalesLine."Unit of Measure Code" := "Purchase Line"."Unit of Measure Code";
                                SalesLine.Validate(Quantity,"Purchase Line".Quantity);
                                SalesLine."Unit Price" := "Purchase Line"."Unit Cost" + ("Purchase Line"."Unit Cost" * 0.03);
                                SalesLine.Validate("Location Code","Purchase Line"."Location Code");
                                SalesLine.Validate("Line Amount","Purchase Line"."Line Amount" + ("Purchase Line"."Line Amount" * 0.03));
                                SalesLine.Validate("Gen. Prod. Posting Group","Purchase Line"."Gen. Prod. Posting Group");
                                SalesLine.Validate("Gen. Bus. Posting Group","Purchase Line"."Gen. Bus. Posting Group");
                                SalesLine.Validate("Qty. to Ship","Purchase Line"."Qty. to Receive");
                                SalesLine.Validate("Qty. to Invoice","Purchase Line"."Qty. to Invoice");
                                SalesLine.Validate("VAT Bus. Posting Group","Purchase Line"."VAT Bus. Posting Group");
                                // SalesLine."Line Amount" := "Purchase Line"."Line Amount" * 1.03;
                                SalesLine.Insert(true);
                            END;
                            // SalesLine.Modify(true);
                            PurchaseHeader.GET("Purchase Header"."Document Type", "Purchase Header"."No.");
                            PurchaseHeader.Sales_Reference_No := SalesHeader."No.";
                            PurchaseHeader.Modify(true);
                            // SalesHeader.Modify(true);
                        END;                       
                    END;
                end;
            }
        }
    }
}

Step 2: Publish and Install the Extension 

• After creating the custom report, you’ll need to publish and install the extension in your Dynamics 365 Business Central instance.

Step 3: Create a Custom Job Queue Entry

• With the custom report, you can now create a custom job queue entry to schedule and automate the conversion process.
• Access your Business Central instance.
• Navigate to “Job Queue Entries” and create a new job queue entry.
• Fill out the necessary fields, such as Name, Report ID (which should point to your custom report), and Description.

Step 4: Set Up Job Queue Parameters

• In the job queue entry, you can specify parameters such as the schedule for when the job should run.
• Make sure to enable the job queue entry, and then save and run it. This will initiate the job queue, and it will automatically convert purchase orders to sales orders based on the defined schedule.

Step 5: Test the Customization

• Test the customization thoroughly to ensure it works as expected and meets your business requirements

Conclusion:

Creating a custom job queue to automatically convert purchase orders to sales orders in Microsoft Dynamics 365 Business Central can greatly streamline your business processes and increase efficiency. This solution allows you to automate the conversion process, saving time and reducing manual errors. By following the implementation steps outlined in this guide, you can build a custom job queue using AL code to handle the conversion seamlessly. The custom report is designed to retrieve purchase order data, initiate sales orders, and manage the data transfer and transformation.