You’re offline. This is a read only version of the page.
20
Extract Trial balance in CSV file using Sys-operation Framework
1. Create Contract class
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(TrialBalanceUIBuilder)),
SysOperationGroupAttribute('Focuses', “”, '1'),
SysOperationGroupAttribute('Date', "@SYS7402", '2'),
SysOperationGroupAttribute('Frequency', “”, '3'),
SysOperationGroupAttribute('Manual', “”, '4')
]
class TrialBalanceContract implements SysOperationValidatable
{
TransDate fromDate;
TransDate toDate;
DimensionsLedgerTransSpecDim specification;
int frequencyDays;
NoYesId manualRun;
Name primaryDimensionFocus;
[
DataMemberAttribute('ManualRun'),
SysOperationLabelAttribute(literalstr("@")),
SysOperationHelpTextAttribute(literalstr("@")),
SysOperationGroupMemberAttribute('Manual'),
SysOperationDisplayOrderAttribute('4')
]
public NoYesId parmManualRun(NoYesId _manualRun = manualRun)
{
manualRun = _manualRun;
return manualRun;
}
[
DataMemberAttribute('FromDate'),
SysOperationLabelAttribute(literalstr("@SYS5209")),
SysOperationHelpTextAttribute(literalstr("@SYS67")),
SysOperationGroupMemberAttribute('Date'),
SysOperationDisplayOrderAttribute('1')
]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}
[
DataMemberAttribute('PrimaryDimensionFocus'),
SysOperationLabelAttribute(literalstr("@")),
SysOperationGroupMemberAttribute('Focuses'),
SysOperationDisplayOrderAttribute('3')
]
public Name parmPrimaryDimensionFocus(Name _primaryDimensionFocus = primaryDimensionFocus)
{
primaryDimensionFocus = _primaryDimensionFocus;
return primaryDimensionFocus;
}
[
DataMemberAttribute('ToDate'),
SysOperationLabelAttribute(literalstr("@SYS14656")),
SysOperationHelpTextAttribute(literalstr("@SYS67")),
SysOperationGroupMemberAttribute('Date'),
SysOperationDisplayOrderAttribute('2')
]
public TransDate parmToDate(TransDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}
public boolean validate()
{
boolean isValid = true;
FiscalCalendarRecId calendarRecId;
TransDate toDatePeriodStart;
TransDate fromDatePeriodStart;
recId primaryFocusRecId;
recId secondaryFocusRecId;
calendarRecId = Ledger::fiscalCalendar(CompanyInfo::find().RecId);
if (fromDate && toDate)
{
if (fromDate > toDate)
{
isValid = checkFailed(strfmt("@SYS55013", date2StrUsr(fromDate, DateFlags::FormatAll), date2StrUsr(toDate, DateFlags::FormatAll)));
}
fromDatePeriodStart = LedgerFiscalCalendar::findOpeningStartDateByDate(calendarRecId, fromDate);
toDatePeriodStart = LedgerFiscalCalendar::findOpeningStartDateByDate(calendarRecId, toDate);
if (fromDatePeriodStart != toDatePeriodStart)
{
isValid = checkFailed(strfmt("@SYS28745", year(fromDatePeriodStart), year(toDatePeriodStart)));
}
}
primaryFocusRecId = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, this.parmPrimaryDimensionFocus()).RecId;
if (!primaryFocusRecId)
{
isValid = checkFailed("@");
}
return isValid;
}
}
2. Create controller class
[SysOperationJournaledParametersAttribute(true)]
class USMFTrialBalanceController extends SysOperationServiceController
{
public static void main(Args _args)
{
USMFTrialBalanceController controller =
USMFTrialBalanceController::construct();
controller.parmArgs(_args);
controller.startOperation();
}
public static USMFTrialBalanceController construct(
SysOperationExecutionMode _executionMode =
SysOperationExecutionMode::Asynchronous)
{
USMFTrialBalanceController controller =
new USMFTrialBalanceController();
controller.parmExecutionMode(_executionMode);
return controller;
}
public void new()
{
super(classStr(USMFTrialBalanceService),
methodStr(USMFTrialBalanceService,
processData),
SysOperationExecutionMode::Asynchronous);
}
}
class USMFTrialBalanceService
{
const str staticalAccCategory = “OTHERCA";
public void processData(USMFTrialBalanceContract _dataContract)
{
GeneralJournalEntry generalJournalEntry,generalJournalEntry_Loc;
GeneralJournalAccountEntry generalJournalAccountEntry;
SubledgerVoucherGeneralJournalEntry voucherGeneralJournalEntry;
FiscalCalendarPeriod fiscalCalPeriod;
Ledger ledger;
CompanyInfo companyInfo;
SubledgerVoucherGeneralJournalEntry SubledgerVoucherGeneralJournalEntry;
TaxTransGeneralJournalAccountEntry taxTransGeneralAccEntry;
TaxTrans taxTrans;
DimensionAttributeValueCombination dimensionAttributeValueCombination;
MainAccount mainAccount;
MainAccountCategory mainAccountCategory;
DimensionHierarchyLevel dimensionHierarchyLevel;
USMFLedgerTransStatementStagingTmp USMFLedgerTransStatementStagingTmp, USMFLedgerTransStatementStagingTmp1;
TransDate fromDate;
TransDate toDate;
NoYesId manualRun;
Name primaryDimensionFocus;
MainAccountNum mainAccountId;
UserConnection con = new UserConnection();
USMFTrialBalanceService trialBalanceService = new USMFTrialBalanceService();
USMFTrialBalanceParameters trialBalanceParameter;
DimensionAttributeValueSetStorage dimStorage;
DimensionValue dimensionValue;
DimensionDefault defaultDimension;
int i;
DimensionAttributeValueCombination dimValueCombi;
;
fromDate = _dataContract.parmFromDate();
toDate = _dataContract.parmToDate();
primaryDimensionFocus = _dataContract.parmPrimaryDimensionFocus();
manualRun = _dataContract.parmManualRun();
delete_from USMFLedgerTransStatementStagingTmp;
if(!manualRun)
{
select trialBalanceParameter;
fromDate = today()-trialBalanceParameter.BackDatedEntriesDays ;
toDate = today()-1;
while select generalJournalAccountEntry
join JournalNumber, PostingLayer,AccountingDate,RecId,Ledger,SubledgerVoucher,FiscalCalendarPeriod
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= fromDate
&& generalJournalEntry.AccountingDate <= toDate
join Voucher
from SubledgerVoucherGeneralJournalEntry
where SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join Type from fiscalCalPeriod
where fiscalCalPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
&& fiscalCalPeriod.Type == FiscalPeriodType::Operating
exists join ledger
where ledger.RecId == generalJournalEntry.Ledger
exists join companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
&& companyInfo.DataArea == curext()
{
if(generalJournalAccountEntry.RecId)
{
ttsbegin;
USMFLedgerTransStatementStagingTmp.clear();
USMFLedgerTransStatementStagingTmp.TransDate = GeneralJournalEntry.AccountingDate;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntry = GeneralJournalEntry.RecId;
USMFLedgerTransStatementStagingTmp.Voucher = SubledgerVoucherGeneralJournalEntry.Voucher;
USMFLedgerTransStatementStagingTmp.MainAccountId = MainAccount::findByLedgerDimension(GeneralJournalAccountEntry.LedgerDimension).MainAccountId;
if(generalJournalAccountEntry.IsCredit)
{
USMFLedgerTransStatementStagingTmp.AmountCredit = GeneralJournalAccountEntry.AccountingCurrencyAmount * -1;
}
else
{
USMFLedgerTransStatementStagingTmp.AmountDebit = GeneralJournalAccountEntry.AccountingCurrencyAmount;
}
USMFLedgerTransStatementStagingTmp.GeneralJournalAccountEntry = GeneralJournalAccountEntry.RecId;
USMFLedgerTransStatementStagingTmp.LedgerDimension = GeneralJournalAccountEntry.LedgerDimension;
USMFLedgerTransStatementStagingTmp.TransTxt = GeneralJournalAccountEntry.Text;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntryDataArea = curExt();
USMFLedgerTransStatementStagingTmp.CurrencyCode = GeneralJournalAccountEntry.TransactionCurrencyCode;
USMFLedgerTransStatementStagingTmp.LedgerAccount = generalJournalAccountEntry.LedgerAccount;
taxTrans.clear();
if (generalJournalAccountEntry.PostingType == LedgerPostingType::Tax)
taxTrans = trialBalanceService.findTaxCode(GeneralJournalAccountEntry);
if(taxTrans.RecId)
{
USMFLedgerTransStatementStagingTmp.TaxCode = taxTrans.TaxCode;
}
USMFLedgerTransStatementStagingTmp.insert();
ttscommit;
}
}
}
if(manualRun)
{
while select generalJournalAccountEntry
join JournalNumber, PostingLayer,AccountingDate,RecId,Ledger,SubledgerVoucher,FiscalCalendarPeriod
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= fromDate
&& generalJournalEntry.AccountingDate <= toDate
join Voucher
from SubledgerVoucherGeneralJournalEntry
where SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join Type from fiscalCalPeriod
where fiscalCalPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
&& fiscalCalPeriod.Type == FiscalPeriodType::Operating
exists join ledger
where ledger.RecId == generalJournalEntry.Ledger
exists join companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
&& companyInfo.DataArea == curext()
{
if(generalJournalAccountEntry.RecId)
{
ttsbegin;
USMFLedgerTransStatementStagingTmp.clear();
USMFLedgerTransStatementStagingTmp.TransDate = GeneralJournalEntry.AccountingDate;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntry = GeneralJournalEntry.RecId;
USMFLedgerTransStatementStagingTmp.Voucher = SubledgerVoucherGeneralJournalEntry.Voucher;
USMFLedgerTransStatementStagingTmp.MainAccountId = MainAccount::findByLedgerDimension(GeneralJournalAccountEntry.LedgerDimension).MainAccountId;
if(generalJournalAccountEntry.IsCredit)
{
USMFLedgerTransStatementStagingTmp.AmountCredit = GeneralJournalAccountEntry.AccountingCurrencyAmount * -1;
}
else
{
USMFLedgerTransStatementStagingTmp.AmountDebit = GeneralJournalAccountEntry.AccountingCurrencyAmount;
}
USMFLedgerTransStatementStagingTmp.GeneralJournalAccountEntry = GeneralJournalAccountEntry.RecId;
USMFLedgerTransStatementStagingTmp.LedgerDimension = GeneralJournalAccountEntry.LedgerDimension;
USMFLedgerTransStatementStagingTmp.TransTxt = GeneralJournalAccountEntry.Text;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntryDataArea = curExt();
USMFLedgerTransStatementStagingTmp.CurrencyCode = GeneralJournalAccountEntry.TransactionCurrencyCode;
USMFLedgerTransStatementStagingTmp.LedgerAccount = generalJournalAccountEntry.LedgerAccount;
taxTrans.clear();
if (generalJournalAccountEntry.PostingType == LedgerPostingType::Tax)
taxTrans = trialBalanceService.findTaxCode(GeneralJournalAccountEntry);
if(taxTrans.RecId)
{
USMFLedgerTransStatementStagingTmp.TaxCode = taxTrans.TaxCode;
}
USMFLedgerTransStatementStagingTmp.insert();
ttscommit;
}
}
}
USMFLedgerTransStatementStagingTmp1.setConnection(con);
trialBalanceService.populateDimensionInformationInStaging(USMFLedgerTransStatementStagingTmp,primaryDimensionFocus);
this.exportDataToLocalFolder(manualRun,primaryDimensionFocus
}
public TaxTrans findTaxCode(GeneralJournalAccountEntry _generalJournalAccountEntry = null)
{
GeneralJournalAccountEntry generalJournalAccountEntry;
TaxTransGeneralJournalAccountEntry taxTransGeneralAccEntry;
TaxTrans taxTrans;
select TaxTrans,GeneralJournalAccountEntry
from taxTransGeneralAccEntry
where taxTransGeneralAccEntry.GeneralJournalAccountEntry == _generalJournalAccountEntry.RecId
join TaxCode
from taxTrans
where taxTrans.recid == taxTransGeneralAccEntry.TaxTrans;
return taxTrans;
}
public void populateDimensionInformationInStaging(
USMFLedgerTransStatementStagingTmp _ledgerTransStatementTmp,
Name _dimensionFocusName )
{
UserConnection con= new UserConnection();
MainAccount mainAccount;
DimensionFocusNameTmp dimNameTmpMainValue;
MainAccountConsolidateAccount mainAccountConsolidateAccount;
DimensionAttributeValueCombination dimValueCombi;
DimensionAttributeLevelValueAllView dimAllview;
DimensionAttribute dimAttr;
DimensionAttributeValue dimAttrValue;
ExtCodeTable extCodeTable;
ExtCodeValueTable extCodeValue;
dimNameTmpMainValue.setConnection(con);
MainAccountCategory mainAccountCategory;
Query focusNameQuery = new Query();
SysQuery::findOrCreateDataSource(focusNameQuery, tableNum(USMFLedgerTransStatementStagingTmp));
QueryBuildDataSource davcDS = SysQuery::findOrCreateDataSource(focusNameQuery, tableNum(DimensionAttributeValueCombination), tableNum(USMFLedgerTransStatementStagingTmp));
davcDS.addLink(fieldNum(USMFLedgerTransStatementStagingTmp, LedgerDimension), fieldNum(DimensionAttributeValueCombination, RecId));
USMFLedgerTransStatementStagingTmp querySource;
querySource.setConnection(con);
select querySource;
QueryRun focusNameQueryRun = new QueryRun(focusNameQuery);
focusNameQueryRun.setRecord(querySource);
focusNameQuery = focusNameQueryRun.query();
DimensionFocusNameTmp::generateFocusNames(dimNameTmpMainValue, focusNameQuery, _dimensionFocusName, con);
update_recordset _ledgerTransStatementTmp setting
MainFocusValue = dimNameTmpMainValue.FocusValue,
MainFocusName = _dimensionFocusName
join dimNameTmpMainValue
where dimNameTmpMainValue.LedgerDimension == _ledgerTransStatementTmp.LedgerDimension;
DimensionFocusNameTmp::generateFocusDescriptions(dimNameTmpMainValue, _dimensionFocusName, con);
update_recordset _ledgerTransStatementTmp setting
MainFocusDescription = dimNameTmpMainValue.FocusValue
join dimNameTmpMainValue
where dimNameTmpMainValue.LedgerDimension == _ledgerTransStatementTmp.LedgerDimension;
update_recordset _ledgerTransStatementTmp setting
BusinessUnit = dimAllview.DisplayValue
join dimAllview
join dimAttr
join dimValueCombi
where dimAttr.RecId == dimAllview.DimensionAttribute
&& dimAttr.Name == "BusinessUnit"
&& dimValueCombi.DisplayValue == _ledgerTransStatementTmp.LedgerAccount
&& dimAllview.ValueCombinationRecId == dimValueCombi.RecId ;
update_recordset _ledgerTransStatementTmp setting
AccountCategory = mainAccountCategory.AccountCategory
join mainAccount
join mainAccountCategory
where mainAccount.MainAccountId == _ledgerTransStatementTmp.MainAccountId
&& mainAccountCategory.AccountCategoryRef == mainAccount.AccountCategoryRef;
}
Description255 getDimensionValue(RecId _recId,Name _DimensionName)
{
DimensionAttributeLevelValueAllView dimAllview;
DimensionAttribute dimAttr;
;
dimAllview.clear();
select firstOnly dimAllview
where dimAllview.ValueCombinationRecId == _recId
join dimAttr
where dimAttr.RecId == dimAllview.DimensionAttribute
&& dimAttr.Name == _DimensionName;
return dimAllview.DisplayValue;
}
public void exportDataToLocalFolder(boolean _manualRun = NoYes::No, Name _primaryDimensionFocus = '')
{
USMFLedgerTransStatementStagingTmp USMFTrialBalanceTemp;
DimensionAttributeValueCombination dimAttributeValueCombination;
FolderName folderName;
Filename fileName,fileNameTime;
date currentDate = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());
DMFParameters dmfParameters;
CommaIo commaIo;
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryRun queryRun;
GeneralJournalEntry generalJournalEntryUpd;
QueryBuildRange USMFTrialBalanceParameters trialBalanceParameters;
boolean stopProcess;
utcdatetime exportedDateTime;
try
{
dmfParameters = DMFParameters::find();
fileName = date2Str(currentDate-1,213,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4);
fileNameTime = strFmt("%1",DateTimeUtil::getTimeNow(DateTimeUtil::getUserPreferredTimeZone()));
folderName = dmfParameters.SharedFolderPath + @"\" + "TrialBalanceExport_"+fileName+"_"+fileNameTime+".csv";
commaIo = new CommaIo(folderName, "W");
commaIo.write("Company code","Posting date","Ledger dimensions","Document ID","Description","Credit","Debit","Currency","GST code","D365 Ledger dimensions");
while select USMFTrialBalanceTemp
where
USMFTrialBalanceTemp.MainFocusName == _primaryDimensionFocus
{
commaIo.write(USMFTrialBalanceTemp.DataAreaId,USMFTrialBalanceTemp.TransDate, ,USMFTrialBalanceTemp.Voucher,USMFTrialBalanceTemp.TransTxt,USMFTrialBalanceTemp.AmountCredit,USMFTrialBalanceTemp.AmountDebit,USMFTrialBalanceTemp.CurrencyCode,USMFTrialBalanceTemp.TaxCode,USMFTrialBalanceTemp.MainFocusValue);
}
if(!_manualRun)
{
ttsbegin;
exportedDateTime = DateTimeUtil::newDateTime(today(), timeNow(),DateTimeUtil::getUserPreferredTimeZone());
update_recordset generalJournalEntryUpd
setting USMFExported = NoYes::Yes,
USMFExportedDateTime = exportedDateTime
join USMFTrialBalanceTemp
where generalJournalEntryUpd.RecId == USMFTrialBalanceTemp.GeneralJournalEntry
&& USMFTrialBalanceTemp.MainFocusName == _primaryDimensionFocus
;
select forupdate trialBalanceParameters;
trialBalanceParameters.LastBatchRunDate = today()-1;
trialBalanceParameters.update();
ttscommit;
}
}
}
catch(Exception::CLRError)
{
System.Exception exception = CLRInterop::getLastException();
if (exception != null)
{
exception = exception.get_InnerException();
if (exception != null)
{
ttsabort;
Error(exception.ToString());
}
}
}
}
Could you explain your question, please?
Your code doesn't contain indentation, therefore it's very difficult to read. Please use Insert > Insert Code (in the rich formatting view) to paste source code.
And which version of AX is it about? I see you're using attributes and the SysOperation framework, therefore it must be either AX 2012 or D365FO, but I'm not sure which one.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (