Friday, June 28, 2013

Data Import Export Framework - Generate Auto Numbers

Recently I needed to setup ledger journal import using Data Import Export framework (DIEF). Even though there exists the Opening Balance entity, getting it done wasn't as straight forward as I thought. I've found some problem using the "Auto generated" flag in Source to Target mapping. The focus is on the DMFGenerateSSISPackage class.

The GetSchemaName method

First of all, there is the getSchemaName method. When dealing with auto number generation from number sequence, DIEF creates a SQL function (FN_FMT_NUMBERSEQUENCE) on the fly to do the number assignment and drops it afterwards. DIEF uses the getSchemaName method to get the schema name when creating this function. The original code uses "SELECT CURRENT_USER" but it can returns the AOS service account instead of "dbo", which is what is expected. Changed it to "SELECT SCHEMA_NAME()" will solve this.

Before moving on, let's define the import data. Let say we want to import 3 lines into the same journal. So I created a "Opening Balance" entity in a processing group. I modify the source-to-staging format, marking JournalNumber auto generated, and I also added the JournalName field in Query Criteria such that all 3 lines (with the same JournalName value) will be assigned the same JournalNumber.

The "Next" value in number sequence

Ok, so the import was fine and I see all 3 lines assigned the same journalNumber in staging. However, when looking at the Number Sequence, the "Next" number has increased by 3 instead of 1. Checking the code in the generateAutoNumbers method shows that this part is not handled properly. Luckily I can count the number of distinct journalNumber used in staging table and fix it.

"Free" numbers consideration for continuous number sequence

Finally, there's the "Free" number consideration for continuous number sequence. The original code simply assign one free number to one staging record until all free numbers are used up. That's not good enough. (e.g. say there is 1 free number in the journalBatchNumber number sequence, then the first line will be assigned the free number and the other two lines a new journal number.) Further editing needs to be done in generateAutoNumbers method to set this part straight as well.

The fixes required are quite straight forward so I won't bother to post the code here. Hopefully some of you will find this information helpful.
                 
This posting is provided "AS IS" with no warranties, and confers no rights.


Thursday, June 13, 2013

Data Import Export Framework - Financial Dimension 0 does not exists

EDIT: This bug is fixed in DIXF that comes with CU7
There *seems* to be a bug in Data Import Export Framework when importing financial dimensions. When importing ledger journal lines I get a "Financial Dimension 0 does not exists" error.
I have made a guess on how it was suppose to work and made some code changes below:

In the DMFDimensionHelper::generateDynamicDimension method, add a new variable for LedgerStructure Table. Then comment out the exists join to ledgerChartOfAccountsStructure and join it with ledgerStructure instead.

                

/*
  exists join ledgerChartOfAccountsStructure where
    ledgerChartOfAccountsStructure.DimensionHierarchy == dimHierarchy.RecId &&
    ledgerChartOfAccountsStructure.ChartOfAccounts == LedgerChartOfAccounts::current()
*/
  exists join ledgerStructure where
    ledgerStructure.DimensionHierarchy == dimHierarchy.RecId &&
    ledgerStructure.Ledger == Ledger::current()
{
    i ++;
    dimAttributeList = conIns(dimAttributeList,i,dimAttribute.Name);
}
Please feel free to comments on the error or this fix.

This posting is provided "AS IS" with no warranties, and confers no rights.