Dynamics AX In-Market Engineering

Dynamics AX In-Market Engineering (Sustained Engineering)

Fields modifiedDateTime and modifiedBy on Table InventSum

Fields modifiedDateTime and modifiedBy on Table InventSum

  • Comments 1
  • Likes

Problem:

Fields "modifiedDateTime" and "modifiedBy" on Table InventSum are not updated correct in inventory journals.

Resolution:

The following code changes will update the modifiedDateTime and modifiedBy fields on the InventSum table when posting Inventory Journals.

1.      \ Classes\InventUpdateOnHand\sqlUpdateInventSumStrSQLServer
                      

1.1   Add comments on added SQL str in the beginning of this method

/* This method will build following statement in a str:

InventSum.LastUpdDateExpected    = CASE WHEN InventSum.LastUpdDateExpected > InventSumDelta.MAX_LastUpdDateExpected

                                              THEN InventSum.LastUpdDateExpected

                                                        ELSE InventSumDelta.MAX_LastUpdDateExpected

                                                   END,

//New added begin

 InventSum.ModifiedDateTime = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()),

 InventSum.ModifiedBy              = currentUserId,

//New added end

 

1.2   Variable definition

Added code is listed as below.

    str 256             tmpFieldNameModifiedDate     = new SysDictField(tablenum(InventSum),fieldnum(InventSum, ModifiedDateTime)).name(DbBackend::Sql,0,FieldNameGenerationMode::FieldList);

str 256             tmpFieldNameModifiedBy       = new SysDictField(tablenum(InventSum),fieldnum(InventSum, ModifiedBy)).name(DbBackend::Sql,0,FieldNameGenerationMode::FieldList);

userId              currentUserId = curuserid();

 

1.3   Added SQL clause

        //  InventSum.LastUpdDateExpected = CASE WHEN InventSum.LastUpdDateExpected > InventSumDelta.MAX_LastUpdDateExpected

        //                                       THEN InventSum.LastUpdDateExpected

       //                                       ELSE InventSumDelta.MAX_LastUpdDateExpected

      //                                  END,

     pct2 += ', ' + inventSumName + '.' + tmpFieldNameUpdExp + ' = CASE WHEN '  + inventSumName + '.' + tmpFieldNameUpdExp + ' > ' + inventSumDeltaName + '.' + maxPrefix + tmpFieldNameUpdExp;

    pct2 += ' THEN '  + inventSumName + '.' + tmpFieldNameUpdExp + ' ELSE ' + inventSumDeltaName + '.' + maxPrefix + tmpFieldNameUpdExp + ' END ';

 

// New added begin

    //  InventSum.ModifiedDateTime = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()),

    pct2 += ', '  + inventSumName + '.' + tmpFieldNameModifiedDate + ' = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()) ';

 

    // InventSum.ModifiedBy = currentUserId,

    if (tmpFieldNameModifiedBy) // table property ModifiedBy might not be enabled

    {

        pct2 += ', ' + inventSumName + '.' + tmpFieldNameModifiedBy + ' = ' + '\'\'' + currentUserId + '\'\'';

}

// New added end

 

2.      Enable table property ModifiedDateTime and CreateRecIdIndex:

“\DataDictionary\Tables\InventSum\TableProperty”

Save table “\DataDictionary\Tables\InventSum”. Synchronize DataDictionary.

 

Comments
  • Maybe you could clean up the code in this post.... It's kind of hard to read...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment