Data Import Export Framework - Generate Auto Numbers

on
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.


Comments

  1. Axapta world3:32 PM

    Hi Dominic, I also have the same requirement to import the LedgerJournals using DIXF with opening balance entity.
    In my case I have to make the journalNum and lineNum fields as auto-generated.

    So in my case what I have done is:
    For journalNum I have added a queryCriteria with "journalName" field, so with this system is automatically taking care of number sequence assigned to the journalNum with the journalName.

    Steps:
    1) Click on "Auto-generated" flag for journalNum field.
    2) Click on "Query-criteria" button, new form will open.
    3) click on new and insert the "JOurnalName" field and close.
    4) For lineNum, just click activate the "Auto-generated" flag.
    5) Remember not to keep the Auto-generated fields in the file.


    ReplyDelete

Post a Comment