Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

Unable to insert rows in the General Journal table

Posted on by 5
Hello, 
I need to calculate Royalty Amount for each item sold. For this I have extended the Item table and Item Card page. User can select a vendor and royalty percentage on the Item Card page and save it.
 I need to calculate the royalty amount for each item in the Sales Lines. So when sales order is submitted, I tap into the "OnAfterPostSalesLines" event and execute below code to insert rows into the General Journal table.
I am able to calculate the royalty amount for each item correctly but the rows are not being stored properly in the General Journal table. Issues I am facing are:
1) If there are multiple SalesLines rows, only the first row is stored in the General Journal table. The second row never makes it to the General Journal table.
2)  In case of consecutive run, the new rows are inserted but with old data that is already existing in the General Journal table.
Please help as I am not able to resolve this issue. If there is any other solution, please let me know.
===================================================================================================================================================
codeunit 50156 "Sales Post Royalty Codeunit"
{
    EventSubscriberInstance = StaticAutomatic;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post", 'OnAfterPostSalesLines', '', true, true)]

    local procedure OnAfterPostSalesLinesPosted(var SalesHeader: Record "Sales Header"; var SalesShipmentHeader: Record "Sales Shipment Header"; var SalesInvoiceHeader: Record "Sales Invoice Header"; var SalesCrMemoHeader: Record "Sales Cr.Memo Header"; var ReturnReceiptHeader: Record "Return Receipt Header"; WhseShip: Boolean; WhseReceive: Boolean; var SalesLinesProcessed: Boolean; CommitIsSuppressed: Boolean; EverythingInvoiced: Boolean; var TempSalesLineGlobal: Record "Sales Line")
    //[EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post", 'OnBeforePostSalesLines', '', false, false)]
    //local procedure OnBeforePostSalesLines(var SalesHeader: Record "Sales Header"; var TempSalesLineGlobal: Record "Sales Line"; var TempVATAmountLine: Record "VAT Amount Line"; var EverythingInvoiced: Boolean);


    var
        lineItem: Record Item;
        pct: decimal;
        amount: decimal;
        royaltyAmt: Decimal;
        vendor: code[20];
        counter: integer;
        GenJnlLine: Record "Gen. Journal Line";
        lineNo: Integer;
    begin
        if not TempSalesLineGlobal.IsEmpty() then begin

            TempSalesLineGlobal.Reset();
            counter := TempSalesLineGlobal.Count();
           
            IF TempSalesLineGlobal.FINDSET THEN BEGIN
                repeat
                    lineItem := TempSalesLineGlobal.GetItem();
                   
                    vendor := lineItem.RoyaltyVendor;
                    amount := TempSalesLineGlobal."Line Amount";
                    royaltyAmt := (Amount) * (lineItem.RoyaltyPercentage / 100);
                    Message('Vendor - %1, Amount %2', lineItem.RoyaltyVendor, royaltyAmt);
                    GenJnlLine.Init();
                    GenJnlLine."Account Type" := "Gen. Journal Account Type".FromInteger(0);
                    GenJnlLine."Account No." := '8455';
                    GenJnlLine.Amount := royaltyAmt;
                    GenJnlLine."Document Type" := TempSalesLineGlobal."Document Type";
                    GenJnlLine."Document No." := TempSalesLineGlobal."Document No.";
                    GenJnlLine."Posting Date" := Today;
                    GenJnlLine."Bal. Account Type" := "Gen. Journal Account Type".FromInteger(2);
                    GenJnlLine."Bal. Account No." := Format(vendor);
                    GenJnlLine."Journal Batch Name" := 'DEFAULT';
                    GenJnlLine."Journal Template Name" := 'GENERAL';

                    IF GenJnlLine.FINDLAST THEN begin
                        lineNo := GenJnlLine."Line No." + 10000;
                    end
                    else begin
                        lineNo := 10000;
                    end;

                    GenJnlLine."Line No." := lineNo;

                    GenJnlLine.Insert(true);
                    Commit();
                     Message('record# %1 inserted.', lineNo);
                until TempSalesLineGlobal.next() = 0;
            END;
        end;

    end;

}















Categories:
  • RE: Unable to insert rows in the General Journal table

    Resolved ! Thanks a lot.

  • Suggested answer
    Deekshitha Reddy Profile Picture
    Deekshitha Reddy 219 on at
    RE: Unable to insert rows in the General Journal table

    Also to insert  a record in a table INIT() & INSERT are used.

    learn.microsoft.com/.../record-insert--method

    --you can try using it before the values you want to be inserted to the table.

    Hope this helps.

  • RE: Unable to insert rows in the General Journal table

    Thanks Mohana for the quick reply.

    I would like to keep the rows created earlier as of now.

    I will try with updated code as given by  you and revert back.

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,135 Super User 2024 Season 2 on at
    RE: Unable to insert rows in the General Journal table

    To fix the point 1, You can use the below code

    GenJnlLine."Line No." := GenJnlLine.GetNewLineNo(GenJnlLine."Journal Template Name",GenJnlLine."Journal Batch Name");

    instead of

    IF GenJnlLine.FINDLAST THEN begin

                           lineNo := GenJnlLine."Line No." + 10000;

                       end

                       else begin

                           lineNo := 10000;

                       end;

                       GenJnlLine."Line No." := lineNo;

    Point 2, Do you want to delete the lines which are generated earlier?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans