Freigeben über

Fields modifiedDateTime and modifiedBy on Table InventSum

  • Artikel

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

  • Anonymous
    December 29, 2010
    Maybe you could clean up the code in this post.... It's kind of hard to read...