Dynamics 365 Create Ledger Posting Groups of Sales Tax

Sales Tax is calculated and posted to Main Accounts that are specified in Ledger Posting Groups. These Ledger Posting Groups are attached to each sales tax code separately. It is possible for you to set up individual Ledger Posting Groups for each sales tax code also. You can use one ledger posting group for all sales tax codes, or assign multiple ledger posting groups to the sales tax codes.

In this blog, we will be setting up ledger posting groups for you, according to USA Taxation Rules.

Note: Before setting up a ledger posting group, we need a few main accounts of general ledger.

To create a main account for general ledger, please follow this link.

Let’s start with helping you in setting up a Ledger Posting Group for Sales Tax.

Functional Overview: Set up Ledger Posting Group for Sales Tax 

Open Microsoft Dynamics 365

• Open Dynamics 365 Client in Internet Explorer.

Ledger Posting Groups

• Go to Navigation pane > Modules > Tax > Setup > Sales tax > Ledger posting groups. On clicking Ledger posting group menu item, Ledger posting groups form will come in display. All the existing ledger posting groups will be listed here.

Select Ledger Posting Group Name

• By selecting any ledger posting group name, all of its information will be displayed on the details form in the General fast tab.

Sales Tax Payable, Settlement Account and Use Tax Payable Fields

• In the General fast tab, there are some values filled in Sales tax payable, Settlement account and Use tax payable fields. Let’s discuss these fields and their purposes.

Sales Tax Payable

• In this field, we select the main account for outgoing sales taxes that are payable to the tax authority. Sales taxes are collected on behalf of the tax authority when you sell taxable goods and services.

Settlement Account

• For the settlement account, we select the main account that the net balance of the ledger accounts will be posted in. The balance will be created when the sales tax is settled and job post is run. If the tax authority for the settlement period is associated with a vendor account, the balance is posted to the vendor account instead.

Use Tax Payable

• In this field we select the main account for posting incoming Use taxes that are payable to tax authorities.

Vendor and Customer Cash Discounts

Let’s have a look at vendor and customer cash discounts for sales tax in Microsoft Dynamics 365 as explained below.

Vendor Cash Discount

• In this field we select the main account to post cash discount for Sales tax codes associated with this Ledger posting group. This is optional and if no account is entered, the main account on Cash discount codes will be used. It can be useful to use different accounts per Ledger posting group if using the reverse sales tax on cash discount option on Sales tax groups.

Customer Cash Discount

• In this field, we select the main account to post cash discount for Sales tax codes associated with this Ledger posting group. This is, however, optional. If no account is entered, the main account on the Cash discount codes will be used.

It can be useful to use different accounts per ledger posting group, if you are using the reverse sales tax on cash discount option in Sales tax groups.

Create New Ledge Posting Group

• Click on +New button to create new Ledger Posting group.

New Empty Record

• A new empty record will be added in the form. Please select values as mentioned below.

Ledger Posting Group: IB_NY_Stat
Description: Instructor Brandon New York State Tax
Sales tax payable: 202260
Settlement account: 222100
Use tax payable: 222100

Save Changes

• Click on Save button, or close the form to save changes.
  

Technical Overview: Entity Relationship Diagram (ERD)

Below is the graphical display of the Entity Relationship Diagram (ERD).

In Setup Ledger Posting Group for sales tax process, two tables are majorly used.

All the information we put in the functional process above is saved in TaxLedgerAccountGroup.

This table is connected to DimensionAttributeValueCombination, which is further connected to other related tables having specified relationships.

Code:

class SetupLedgerPostinGroupRunableClass
{        
    /// <summary> 
    /// Runs the class with the specified arguments. 
    /// </summary> 
    /// <param name = "_args">The specified arguments.</param> 
    public static void main(Args _args)
    {
        TaxLedgerAccountGroup        taxLedgerAccountGroup;
        LedgerRecId                    ledgerRecId;
        container                    _conValue = [0,0,0];
        try
        {
            ttsbegin;
            taxLedgerAccountGroup.TaxAccountGroup    = "IB_NY_STG";
            taxLedgerAccountGroup.Name                = "IB New York State tax group";
            taxLedgerAccountGroup.TaxOutgoingLedgerDimension        = SetupLedgerPostinGroupRunableClass::GetCreateLedgerDim("202260", _conValue);
            taxLedgerAccountGroup.TaxReportLedgerDimension            = SetupLedgerPostinGroupRunableClass::GetCreateLedgerDim("222100", _conValue);
            taxLedgerAccountGroup.TaxOffsetUseTaxLedgerDimension    = SetupLedgerPostinGroupRunableClass::GetCreateLedgerDim("222100", _conValue);
 
            taxLedgerAccountGroup.insert();       
            ttscommit;
            Info(strFmt('New Ledger posting group %1 has been created!', taxLedgerAccountGroup.TaxAccountGroup));
        }
        catch(Exception::CLRError)
        {
            Info(strFmt('%1', enum2Str(Exception::CLRError)));
        }
    }
    static int64 GetCreateLedgerDim(MainAccountNum _mainAccountNum, container _conValue)
    {
        container   _conData;
        int hierarchyCount;
        int hierarchyIdx;
        RecId                   dimAttId_MainAccount;
        LedgerRecId             ledgerRecId;
        MainAccount                mainAccount;
        RefRecId                recordvalue;
        DimensionAttribute      dimensionAttribute;
        DimensionAttributeValue dimensionAttributeValue;
        DimensionSetSegmentName DimensionSet;
        DimensionStorage         dimStorage;
        LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
        DimensionAttributeValueContract  ValueContract;
        List   valueContracts = new List(Types::Class);
        dimensionAttributeValueCombination dimensionAttributeValueCombination;
        _conData = _conValue;
        mainAccount = MainAccount::findByMainAccountId(_mainAccountNum);
        recordvalue = DimensionHierarchy::getAccountStructure(mainAccount.RecId,Ledger::current());
        hierarchyCount = DimensionHierarchy::getLevelCount(recordvalue);
        DimensionSet = DimensionHierarchyLevel::getDimensionHierarchyLevelNames(recordvalue);
        for(hierarchyIdx = 1;hierarchyIdx&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;=hierarchyCount;hierarchyIdx++)
        {
            if(hierarchyIdx == 1)
            continue;
            dimensionAttribute = DimensionAttribute::findByLocalizedName(DimensionSet[hierarchyIdx],false,"en-us");
            if(dimensionAttribute)
            {
                dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,conPeek(_conData,hierarchyIdx));
                if(dimensionAttributeValue)
                {
                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(dimensionAttribute.Name);
                    ValueContract.parmValue(dimensionAttributeValue.CachedDisplayValue);
                    valueContracts.addEnd(ValueContract);
                }
            }
        }
        LedgerAccountContract.parmMainAccount(_mainAccountNum);
        LedgerAccountContract.parmValues(valueContracts);
        dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
        dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.saveAsDefaultAccount());
        ledgerRecId = dimensionAttributeValueCombination.RecId;
        return ledgerRecId;
    }
}

TaxLedgerAccountGroup:

This table has all the major fields which are used in the Ledger Posting Group Creation Process. From this table, we can get the Ledger Posting Group Id, name and attached main accounts.

In the table below, some major fields are mentioned with the exact column name and description.

Field	Data Type	Description
TaxAccountGroup	string	Ledger posting group id is stored in this field
Name	string	Detailed name/ description is stored in this field
TaxOutgoingLedgerDimension	Int64	Relevant RecId of DimensionAttributeValueCombination table is stored in this field for reference
TaxReportLedgerDimension	Int64	Relevant RecId of DimensionAttributeValueCombination table is stored in this field for reference
TaxOffsetUseTaxLedgerDimension	Int64	Relevant RecId of DimensionAttributeValueCombination table is stored in this field for reference
CashDiscountIncomingLedgerDimension	Int64	Relevant RecId of DimensionAttributeValueCombination table is stored in this field for reference
CashDiscountOutgoingLedgerDimension	Int64	Relevant RecId of DimensionAttributeValueCombination table is stored in this field for reference

DimensionAttributeValueCombination:

This table is connected to TaxLedgerAccountGroup to provide main account values in different fields.

Some major fields are mentioned in the table below:

Field	Data Type	Description
DisplayValue	string	Displays the value for main account stored in this field
LedgerDimensionType	enum	Selected account type is stored in this field
RecId	Int64	Unique record id for every record is stored in this field

Summary: How to Set up Ledger Posting Group for Sales Tax? 

To sum it all up, we discussed Ledger Posting groups for Sales Tax in this blog series. We also learnt how to create Ledger Posting groups for Sales Tax in Dynamics 365.

In the technical overview, you learnt about ERD to understand tables, relations of tables to get a better idea of the data model. ‘Runnable Class’ was also included for Developers to create Ledger Posting groups directly from X++.

Consequently, some major tables, fields of tables were discussed to get a better idea of ‘Database Designs’.

We sincerely hope that you enjoyed this technical blog post on Setting Up Ledge Posting Group for Sales Tax with Microsoft Dynamics 365. At Instructor Brandon, we aim to provide quality service at all times. If you feel the need to reach out to me, you can get in touch  here. – Brandon Ahmad, founder of Instructor Brandon and Dynatuners.