Techno-Functional Dynamics 365 F&S Data Model Explanations for Reporting Part 25 of 25: Create Ledger Posting Group of Sales tax

Techno-Functional Dynamics 365 F&S Data Model Explanations for Reporting Part 25 of 25: Create Ledger Posting Group of Sales tax

How to Set up Ledger Posting Group for Sales Tax? Dynamics 365

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 

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.

Functional Overview: Set up Ledger Posting Group for Sales Tax 

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.

Select Ledger Posting Group Name

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.

Create New Ledge 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

New Empty Record

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.

Technical Overview: Entity Relationship Diagram (ERD)

Code:

[sourcecode language=”c-sharp”] 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;
}
}

[/sourcecode]

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.

Videos