You’re offline. This is a read only version of the page.
22
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.
Share
Report
All responses (
Answers (
