We often go through the requirements where we need to compute the Trail balances to get the vouher transactions amounts. Here , different ways to compute the trail balance in D365 fo X++
1. This is an easy way to compute trail balances without dimension sets. If required we can add the further ranges below in the dynamic query.
protected Amount getProjBalanceForRevenue(ProjId _projId, MainAccountNum _mainaccount)
{
LedgerAllocationRuleSource ruleSource;
RecId keyValue;
Map dimensionRange = new Map(Types::Int64, Types::String);
Set dimensionRangeIncludeEmpty = new Set(Types::Int64);
str emptyQueryStr = SysQuery::valueEmptyString();
GeneralJournalAccountEntry result;
Amount DebitAmount,CreditAmount, TotalAllocatedAmount;
Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(GeneralJournalAccountEntry));
// Add fields from GeneralJournalAccountEntry to query
qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyAmount), SelectionField::Sum);
qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, AccountingCurrencyAmount), SelectionField::Sum);
qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, ReportingCurrencyAmount), SelectionField::Sum);
// Configure the query to do a Group By currency & account/dimension combination
qbds.orderMode(OrderMode::GroupBy);
qbds.addSortField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyCode));
qbds.addSortField(fieldNum(GeneralJournalAccountEntry, LedgerDimension));
// qbds.addRange(fieldnum(GeneralJournalAccountEntry,IsCredit )).value(SysQuery::value(Noyes::No));
QueryBuildDataSource qbdsHeader = qbds.addDataSource(tableNum(GeneralJournalEntry));
qbdsHeader.relations(true);
qbdsHeader.joinMode(JoinMode::InnerJoin);
qbdsHeader.fetchMode(QueryFetchMode::One2One);
// qbdsHeader.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry), fieldNum(GeneralJournalEntry, RecId));
qbdsHeader.addRange(fieldNum(GeneralJournalEntry, PostingLayer)).value(queryValue(CurrentOperationsTax::Current));
qbdsHeader.addRange(fieldNum(GeneralJournalEntry, Ledger)).value(SysQuery::value(Ledger::current()));
QueryBuildDataSource qbdsFiscalPeriod = qbdsHeader.addDataSource(tableNum(FiscalCalendarPeriod));
/// qbdsFiscalPeriod.addLink(fieldNum(GeneralJournalEntry,FiscalCalendarPeriod), fieldNum(fiscalCalendarPeriod, RecId));
qbdsFiscalPeriod.relations(true);
qbdsFiscalPeriod.joinMode(JoinMode::ExistsJoin);
qbdsFiscalPeriod.addRange( fieldNum(fiscalCalendarPeriod, type)).value(SysQuery::value(FiscalPeriodType::Operating));
QueryBuildRange qbr = qbdsHeader.addRange(fieldNum(GeneralJournalEntry, AccountingDate));
qbr.value(SysQuery::range(fromDate, toDate));
// Build the account and dimension range string from LedgerAllocationRuleSource for this rule
this.createRuleSourceCriteria( dimensionRange, _Mainaccount, _projId);
QueryBuildDataSource qbdsDavc = qbds.addDataSource(tableNum(DimensionAttributeValueCombination));
qbdsDavc.joinMode(JoinMode::ExistsJoin);
qbdsDavc.addLink(fieldNum(GeneralJournalAccountEntry, LedgerDimension), fieldNum(DimensionAttributeValueCombination, RecId));
qbdsDavc.fetchMode(QueryFetchMode::One2One);
LedgerAllocationProcessRequest::createDimensionRanges(qbdsDavc, dimensionRange.getEnumerator(), dimensionRangeIncludeEmpty);
QueryRun queryRunloc = new QueryRun(query);
while (queryRunloc.next())
{
result = queryRunloc.get(tableNum(GeneralJournalAccountEntry)) as GeneralJournalAccountEntry;
if(result.IsCredit)
{
CreditAmount += result.AccountingCurrencyAmount;
}
else
{
DebitAmount += result.AccountingCurrencyAmount;
}
}
TotalAllocatedAmount = DebitAmount + CreditAmount;
return TotalAllocatedAmount;
}
public void createRuleSourceCriteria(Map _dimensionRange, MainAccountNum _mainaccount, ProjId _ProjId)
{
DimensionAttributeRecId mainAccountDimAttrId = DimensionAttribute::getWellKnownDimensionAttribute(DimensionAttributeType::MainAccount);
_dimensionRange.insert(mainAccountDimAttrId, _mainaccount);
_dimensionRange.insert(DimensionAttribute::findByName("Project").RecId, _ProjId);
}2. Computing trial balances and getting required closing balance based on operating type with dimension set.
private void calcClosingBalanceAC()
{
LedgerTrialBalanceListPageBalanceParms balanceParameters;
DimensionHierarchy dimHier;
TransDate fromDate,toDate;
List selected = new List(Types::Enum);
selected.addEnd(CurrentOperationsTax::Current);
dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, '@PRO_Synamedia:MaserviceId');
balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
balanceParameters.parmDateCode('');
balanceParameters.parmDimensionSetHierarchy(dimHier);
balanceParameters.parmStartDate(dateNull());
balanceParameters.parmEndDate(TrandateFilter.dateValue());
balanceParameters.parmPostingLayers(selected);
balanceParameters.parmIncludeClosingAdjustments(false);
select firstOnly RecId from ledgerTrialBalanceTmp;
LedgerTrialBalanceTmp::calculateBalances_V2(ledgerTrialBalanceTmp,
dimHier.Name,
balanceParameters.getStartDate(),
balanceParameters.getEndDate(),
balanceParameters.getIncludeOpeningPeriods(),
balanceParameters.getIncludeClosingAdjustments(),
balanceParameters.getIncludeClosingTransactions(),
balanceParameters.getPostingLayers(),
true,
true,
Ledger::current(),
balanceParameters.getDisplayMainAccountCategory());
} private Real getClosingBalanceAc(BusinessUnit _BU)
{
real CLosingamount;
select firstonly *
from ledgerTrialBalanceTmp
where ledgerTrialBalanceTmp.DimensionValues[1] == unbilledRevnueAccount
&& ledgerTrialBalanceTmp.DimensionValues[2] == _BU;
if (ledgerTrialBalanceTmp.RecId)
{
CLosingamount = this.getClosingBalance();
}
return CLosingamount;
} private real getClosingBalance()
{
DimensionAttributeValueCombination dimensionAttributeCombo;
GeneralJournalAccountEntry generalJournalAccountEntry;
DAXGeneralJourAccountEntryOperatingView GeneralJourAccountEntryOperatingView;
Query query = new Query();
QueryBuildDataSource GJAccountEntryDs, GJEntryDs, FCDS, DAVC_Ds;
QueryBuildRange Operatingrange;
real closingBalance;
GJAccountEntryDs = query.addDataSource(tableNum(DAXGeneralJourAccountEntryOperatingView));
GJAccountEntryDs.addRange(fieldnum(DAXGeneralJourAccountEntryOperatingView, AccountingDate)).value(queryRange(datenull(), TrandateFilter.dateValue()));
DAVC_Ds = GJAccountEntryDs.addDataSource(tableNum(DimensionAttributeValueCombination));
DAVC_Ds.addLink(fieldNum(PROGeneralJourAccountEntryOperatingView , LedgerDimension) , fieldNum(DimensionAttributeValueCombination , RecId));
select firstonly dimensionAttributeCombo
where dimensionAttributeCombo.Recid == ledgerTrialBalanceTmp.LedgerDimension;
// DimensionHierarchy dimHierLoc = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, SubBillParameters::find().PRO_MonthEndAutomationJourDimSet);
DimensionHierarchy dimHierLoc = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, '@PRO_Synamedia:MaserviceId');
this.updateQueryForLedgerDimension(query, dimensionAttributeCombo, dimHierLoc);
QueryRun qr = new QueryRun(query);
while (qr.next())
{
GeneralJourAccountEntryOperatingView = qr.get(tableNum(DAXGeneralJourAccountEntryOperatingView));
closingBalance += GeneralJourAccountEntryOperatingView.AccountingCurrencyAmount;
}
return closingBalance;
}3. As performance concerned create a view for GeneralJournalAccountEntry Table for applying theoperating type ranges.
private void updateQueryForLedgerDimension(Query _query, DimensionAttributeValueCombination _davcSearchCriteria, DimensionHierarchy _dimHier)
{
// If the account transaction details are related to the dimension set containing Main account only, the filter can
// be applied directly to GeneralJournalAccountEntry for the account filtering.
//boolean mainAccountTransactionDetails = this.updateQueryForMainAccountOnly(_query, _davcSearchCriteria, _dimHier);
//if (mainAccountTransactionDetails == false)
//{
QueryBuildDataSource davcDS = this.getDimensionAttributeValueCombinationDataSource(_query);
DimensionAttributeLevelValueView dalvView;
while select DimensionAttribute, DisplayValue from dalvView
where dalvView.ValueCombinationRecId == _davcSearchCriteria.RecId
{
DimensionRefFieldName dimensionValueColumnName = DimensionAttribute::find(dalvView.DimensionAttribute).DimensionValueColumnName;
QueryBuildRange qbr = davcDS.addRange(fieldName2Id(tableNum(DimensionAttributeValueCombination), dimensionValueColumnName));
qbr.value(queryValue(dalvView.DisplayValue));
}
// For any dimension specified in the hierarchy but not having a value in the ledger account, add an empty string criteria
if (_dimHier)
{
DimensionHierarchyLevel dimHierLevels;
while select DimensionAttribute from dimHierLevels
where dimHierLevels.DimensionHierarchy == _dimHier.RecId
notexists join dalvView
where dalvView.DimensionAttribute == dimHierLevels.DimensionAttribute
&& dalvView.ValueCombinationRecId == _davcSearchCriteria.RecId
{
DimensionRefFieldName dimensionValueColumnName = DimensionAttribute::find(dimHierLevels.DimensionAttribute).DimensionValueColumnName;
QueryBuildRange qbr = davcDS.addRange(fieldName2Id(tableNum(DimensionAttributeValueCombination), dimensionValueColumnName));
qbr.value(SysQuery::valueEmptyString());
}
}
//}
}
private QueryBuildDataSource getDimensionAttributeValueCombinationDataSource(Query _query)
{
QueryBuildDataSource davcDS = _query.dataSourceTable(tableNum(DimensionAttributeValueCombination));
davcDS.joinMode(JoinMode::InnerJoin);
return davcDS;
}
Thanks !!
