Problem with some views in AX 2012 (PRODCALCTRANSEXPANDED and PRODROUTETRANSEXPANDED)

This question is answered

Hi,

 

I'm installing my first AX 2012 (CU1) @ a customer site and stumbled on problem processing the OLAP cubes.

I've followed the error to it's source and appearantly it's a view that won't work ( Data convertion error from nvarchar to string)

In the view PRODCALCTRANSEXPANDED, there is fixed date (N'24/10/2011')  that should be converted to DateTime.

First of all, because I'm on a European server with DD/MM/YYYY settings, it writes the date the same in T-SQL. But in T-SQL, it's always MM/DD/YYYY. This is a bug I presume. Setting my server to US dateformat,synschronising the view and the error is gone.

But then I tried to understand the logic of this view, the date is updated every time you do a synchronise of that view. Meaning if you don't synchronise very often and Prodstatus is not 5 or 7, you production-order is not delayed ( DELAYED = 0 ). Why doesn't they use GETDATE() or am I missing something here?

The same for view PRODROUTETRANSEXPANDED, same bug and some illogical use of a fixed date.

This is the piece of code that holds that fixed date in the first view.

 CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < N'24/10/2011')
                      THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
                      (T2.PRODSTATUS = 7)) AND (T2.DLVDATE < T2.FINISHEDDATE) THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
                      (T2.PRODSTATUS = 7)) AND (T2.DLVDATE >= T2.FINISHEDDATE) THEN 0 ELSE NULL END) AS INT) AS DELAYED

T2 is PRODTABLE in this piece of T-SQL.

Any idea's?

Regards,

Sven Peeters

BELGIUM

 

Verified Answer
  • Hi,

    Both issues are fixed in AX2012 R2, namely in class method ProdTableExpandedColumn::delayed(), which is used by view ProdCalcTransExpanded. The code which resulted in hardcoding current date with date format derived from local machine:

    SysComputedColumn::comparisonLiteral(date2StrUsr(today(), DateFlags::None))

    was replaced with proper:

    SysComputedColumn::getCurrentUtcDate()

    As the result, the view (after synchronization) contains:

    CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < GETUTCDATE())

    Hope that helps.

    Regards,

    AdamS

All Replies
  • Hi Sven,

    Did you get a permanent solution to this issue.

    We found this "synchronisation" date is also still used in the view created in the AX2012 R2 version.

  • Not sure why they hard coded a date in there, but maybe you can wrap that part with some additional SQL just to make sure the type remains a datetime (assuming DLVDATE is same type as TRANSDATE).

    Depending on what version of SQL you are running, perhaps you can get inspired from here:

    msdn.microsoft.com/.../hh213505

    Or here: msdn.microsoft.com/.../ms187928.aspx (look at the first community comment at the bottom).

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • Hi,

    Both issues are fixed in AX2012 R2, namely in class method ProdTableExpandedColumn::delayed(), which is used by view ProdCalcTransExpanded. The code which resulted in hardcoding current date with date format derived from local machine:

    SysComputedColumn::comparisonLiteral(date2StrUsr(today(), DateFlags::None))

    was replaced with proper:

    SysComputedColumn::getCurrentUtcDate()

    As the result, the view (after synchronization) contains:

    CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < GETUTCDATE())

    Hope that helps.

    Regards,

    AdamS