Generating Voucher Numbers When Importing General Journals With DIEF in AX 2012
When importing general journals from DIEF in AX 2012, ideally voucher numbers could be generated from number sequences. However, when you attempt to import them you will get a scope error because there is no NumberSequenceReference record for a voucher number sequence. Also voucher numbers are a little different in that new voucher numbers are assigned if general journal entries have an offsetting account specified or if the running total of debits and credits match when the offsetting account is not specified. This means it is likely that not every line in your import source should get its own new voucher number. I recently had to account for these nuances of vouchers while importing in AX 2012 and below are my notes.
The class DMFGenerateSSISPackage and methods autoGeneratedNos and generateAutoNumbers are called to generate auto numbers when data is read from your import source, i.e. Excel. The autoGenerateNos method needs to be modified at the end of the method in order to prevent the scope error from being thrown. I set it to this:
else
{
//BN 4/10/2014 - If generating a voucher need to pass in a different id since voucher's do not have a NumberSeqReference
//The generateAutoNumbers method will handle vouchers
if(sourceXMLToEntityMap.EntityField == "Voucher" && _tableName == "DMFLedgerJournalEntity")
{
DMFGenerateSSISPackage::generateAutoNumbers( NumberSeqReference::findReference(extendedTypeNum(LedgerJournalizeNum)),
_numOfRecs,
_tableName,
sourceXMLToEntityMap.EntityField,
_definitionGroupExecution);
}
else
{
DMFGenerateSSISPackage::generateAutoNumbers( NumberSeqReference::findReference(dictField.typeId()),
_numOfRecs,
_tableName,
sourceXMLToEntityMap.EntityField,
_definitionGroupExecution);
}
}
The Voucher entity field does not have an entry in the NumberSequenceReference table which causes and error to be thrown in the generateAutoNumbers method. To avoid the error sending in the EDT LedgerJournalizeNum just to avoid the error. There is no other purpose for this change. The generateAutoNumbers method handles the Voucher field with if statements. This is the code in the generateAutoNumbers method:
private static server void generateAutoNumbers(NumberSequenceReference _numberSequenceReference,
Int64 _numOfRecs,
TableName _tableName,
FieldName _fieldName,
DMFDefinitionGroupExecution _definitionGroupExecution)
{
str sqlStmt;
int numOfRecsFromList;
int64 numOfRecForLegalEntity;
SysSqlSystem sqlSystem;
UserConnection connection;
NumberSequenceList numberSequenceList;
Statement statement;
Common tmpNumberSeqGen;
NumberSequenceTable numberSequenceTable;
NumberSequenceRange nextRecOrig,
nextRecUpdated;
FieldId definitionGroupFieldId,
executionFieldId,
generateFieldId;
SqlStatementExecutePermission permission;
sysDictTable dictTable = new sysDictTable(tableName2id(_tableName));
//BN 4/11/2014 - Adding ability to generate voucher numbers
DMFLedgerJournalEntity stageTable, updTable, dmfLedgerJournalEntity;
int cntr = 1,vouchctr = 0;
real amt = 0;
Voucher curVouch = "";
//BN End
str backendTableName(TableId _tableId)
{
SysDictTable sysDictTable;
;
if (!_tableId)
{
throw error("@SYS16078");
}
sysDictTable = new SysDictTable(_tableId);
if (!sysDictTable)
{
throw error(strFmt("@SYS55416",_tableId));
}
return sysDictTable.name(DbBackend::Sql);
}
str backendFieldName(TableId _tableId, FieldId _fieldId)
{
SysDictField sysDictField = new SysDictField(_tableId, _fieldId);
;
if (!sysDictField)
{
if (!SysDictTable::newTableId(_tableId))
{
throw error(strFmt("@SYS55416",_tableId));
}
else
{
throw error(strFmt("@SYS55418",_fieldId, tableId2name(_tableId)));
}
}
return sysDictField.name(DbBackend::Sql);
}
void executeStatement(str _sqlStmt) //This private method updates the NumberSequenceTable setting the NextRec value. It is called at the very end of this whole method.
{
;
connection.ttsbegin();
numberSequenceTable.setConnection(connection);
numberSequenceTable.disableCache(true);
if((_tableName == "DMFLedgerJournalEntity") && (_fieldName == "Voucher"))
{
select firstOnly JournalName from dmfLedgerJournalEntity where dmfLedgerJournalEntity.ExecutionId == _definitionGroupExecution.ExecutionId;
ledgerJournalName = LedgerJournalName::find(dmfLedgerJournalEntity.JournalName);
select forUpdate firstOnly numberSequenceTable
where numberSequenceTable.RecId == ledgerJournalName.NumberSequenceTable;
}
else
{
select forupdate firstonly numberSequenceTable
where numberSequenceTable.RecId == _numberSequenceReference.NumberSequenceId;
}
if (numberSequenceTable)
{
if ((numberSequenceTable.NextRec + numOfRecForLegalEntity) > numberSequenceTable.Highest)
{
throw error(strFmt("@SYS17478",numberSequenceTable.NumberSequence));
}
nextRecOrig = numberSequenceTable.NextRec;
//BN 4/11/2014 - Adding ability to generate voucher numbers
if((_tableName == "DMFLedgerJournalEntity") && (_fieldName == "Voucher"))
{
numberSequenceTable.NextRec += vouchctr; //The next voucher number should be increased by the number of vouchers this journal will consume
}
else
{
numberSequenceTable.NextRec += numOfRecForLegalEntity;
}
//BN END
numberSequenceTable.update();
}
permission = new SqlStatementExecutePermission(_sqlStmt);
permission.assert();
// BP Deviation Documented
statement.executeUpdate(_sqlStmt);
CodeAccessPermission::revertAssert();
connection.ttscommit();
connection.finalize();
nextRecUpdated = numberSequenceTable.NextRec;
}
tmpNumberSeqGen = dictTable.makeRecord();
definitionGroupFieldId = dictTable.fieldName2Id(fieldStr(DMFCustomerEntity,DefinitionGroup));
executionFieldId = dictTable.fieldName2Id(fieldStr(DMFCustomerEntity,ExecutionId));
generateFieldId = dictTable.fieldName2Id(_fieldName);
numOfRecForLegalEntity = _numOfRecs;
if((_tableName == "DMFLedgerJournalEntity") && (_fieldName == "Voucher"))
{
select firstOnly JournalName from dmfLedgerJournalEntity where dmfLedgerJournalEntity.ExecutionId == _definitionGroupExecution.ExecutionId;
ledgerJournalName = LedgerJournalName::find(dmfLedgerJournalEntity.JournalName);
select firstOnly numberSequenceTable
where numberSequenceTable.RecId == ledgerJournalName.NumberSequenceTable;
}
else
{
numberSequenceTable = _numberSequenceReference.numberSequenceTable();
}
if (!numberSequenceTable)
{
throw error("@DMF943");
}
else if (numberSequenceTable.Blocked)
{
// Number sequence %1 is on hold.
throw error(strFmt("@SYS25068",numberSequenceTable.NumberSequence));
}
select count(RecId) from numberSequenceList
where numberSequenceList.NumberSequenceId == numberSequenceTable.RecId &&
numberSequenceList.Status == NumStatus::Free;
//BN - Want to keep a running total for vouchers
if ((_tableName == "DMFLedgerJournalEntity") && (_fieldName == "Voucher"))
{
while select stageTable
order by stageTable.LineNum
where stageTable.ExecutionId == _definitionGroupExecution.ExecutionId
{
// If the OffsetLedgerDimension field is empty process as a multi-line entry.
// start a running total of Debits & Credits and when difference is 0 consume
// the next journal number.
//
// If the OffsetLedgerDimension field is populated, its a one line entry and current functionality is correct
// amt is our running total for debits and credits
// curVouch is the current voucher identifier to change the value in the staging table to work as desired.
// Set initial voucher
if (curVouch == "")
{
curVouch = stageTable.Voucher;
}
// if the OffsetLedgerDimension is empty, initialize amt variable
if (stageTable.OffsetLedgerDimension == "")
{
amt += (stageTable.AmountCurDebit - stageTable.AmountCurCredit);
// test to see if amount is 0, if not, this is the same voucher,
// so update record to have same voucher number
if (amt != 0)
{
ttsBegin;
select forupdate updTable
where updTable.RecId == stageTable.RecId;
updTable.Voucher = curVouch;
updTable.doUpdate();
ttsCommit;
}
else
{
ttsBegin;
select forupdate updTable
where updTable.RecId == stageTable.RecId;
updTable.Voucher = curVouch;
updTable.doUpdate();
ttsCommit;
curVouch = int2str(str2int(curVouch) + 1);
}
}
else
{
ttsBegin;
select forupdate updTable where updTable.RecId == stageTable.RecId;
updTable.Voucher = curVouch;
updTable.doUpdate();
ttsCommit;
curVouch = int2str(str2int(curVouch) + 1);
}
}
// get the counter to fix the number sequence value as well
while select count(RecId)
from stageTable
group by Voucher
where stageTable.ExecutionId == _definitionGroupExecution.ExecutionId
{
vouchctr++;
}
}
//BN END
numOfRecsFromList = int642int(numberSequenceList.RecId);
numOfRecsFromList = min(numOfRecsFromList, numOfRecForLegalEntity);
// If number sequence needs to be pulled from NumberSequenceList table
if (numOfRecsFromList > 0)
{
numOfRecForLegalEntity = numOfRecForLegalEntity - numOfRecsFromList;
ttsbegin;
while select forupdate tmpNumberSeqGen
where tmpNumberSeqGen.(definitionGroupFieldId) == _definitionGroupExecution.DefinitionGroup &&
tmpNumberSeqGen.(executionFieldId) == _definitionGroupExecution.ExecutionId
{
tmpNumberSeqGen.(generateFieldId) = NumberSeq::newGetNum(_numberSequenceReference).num();
tmpNumberSeqGen.doUpdate();
numOfRecsFromList--;
if (numOfRecsFromList == 0)
{
break;
}
}
ttscommit;
}
if (numOfRecForLegalEntity)
{
connection = new UserConnection();
statement = connection.createStatement();
sqlSystem = new SysSqlSystem();
DMFGenerateSSISPackage::createFnFmtNumberSequence_TSQL();
sqlStmt =
' UPDATE %1 SET %5 = [%2].FN_FMT_NUMBERSEQUENCE(N\'%3\',%5,%4,0) WHERE %6 = N\'%7\' AND %8 = N\'%9\'' ; //This updates the staging table's auto generated values
sqlStmt = strFmt(sqlStmt,
backendTableName(dictTable.id()), // %1
DMFGenerateSSISPackage::getSchemaName(), // %2
numberSequenceTable.Format, // %3
numberSequenceTable.NextRec-1, // %4
backendFieldName(dictTable.id(),generateFieldId), //%5
backendFieldName(dictTable.id(),definitionGroupFieldId), //%6
_definitionGroupExecution.DefinitionGroup, //%7
backendFieldName(dictTable.id(),executionFieldId), //%8
_definitionGroupExecution.ExecutionId //%9
);
executeStatement(sqlStmt);
DMFGenerateSSISPackage::dropFnFmtNumberSequence_TSQL();
}
}
For the voucher number, the generateAutoNumbers method retrieves the JournalName from the DMFLedgerJournalEntity table which gets the value originally from the Excel spreadsheet. The JournalName in that table is then used to retrieve the NumberSequenceTable reference tied to the JournalName in the LedgerJournalName table. This is how it determines which NumberSequenceTable record to use because the Voucher number sequence does not have a corresponding record in the NumberSequenceReference table.
The generateAutoNumbers method keeps a running total of debits and credits to establish when a new voucher number is needed as a single general journal can contain one or more vouchers. Once the total count of vouchers needed in the journal is known, the Voucher field in the DMFLedgerJournalEntity table for each record in this ExecutionId is updated according to the voucher number it should have. Finally the next number in the NumberSequenceTable for the voucher number sequence is increased by the number of vouchers needed in the journal.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.