Generating Voucher Numbers When Importing General Journals With DIEF in AX 2012

by | Updated August 15, 2016 | Development, Dynamics AX

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.

 

 

Related Posts

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Managing Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Management: Introduction of NEW Functionality for Manufacturers Using Dynamics 365

november

18nov10:00 am10:30 amThe Modern Manufacturer - Tears and Trauma of MRP

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X