Question Status

Verified
Sven Peeters asked a question on 24 Oct 2011 5:30 AM

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

 

Reply
Pierre Louis responded on 29 Jan 2013 5:05 AM

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.

Reply
Suggested Answer
Tommy Skaue responded on 29 Jan 2013 5:28 PM

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

Reply
Verified Answer
AdamS responded on 20 Mar 2013 10:16 AM

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

Reply
Verified Answer
AdamS responded on 20 Mar 2013 10:16 AM

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

Reply
Suggested Answer
Tommy Skaue responded on 29 Jan 2013 5:28 PM

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

Reply