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

By Becky Newell | April 16, 2014

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

Recommended Reading:

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article
4.28.22 Dynamics GP

Uncommonly Used Features – Integrate Purchasing and Payables to Fixed Assets in Dynamics GP

Being able to integrate Purchasing and Payables to Fixed Assets in Dynamics GP will help you create visibility for your […]

Read the Article

New Features in the Dynamics 365 Business Central 2022 Wave 1 Release – Ease-of-Use Features

  There are many new features in the Dynamics 365 Business Central 2022 Wave 1 Release to get excited about! […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X