Yo Detroit !
The simplest method (in my opinion) is to do it right once. This means to create a Database VIEW that does the necessary transformations on date and numeric fields so that they are normalized with respect to date data types and correct number of decimal places. In your crystal report you then reference the database VIEW instead of the TABLE.
What you do is create a database FUNCTION that accepts a JDE Date field as input and returns a DATE data type as output. Your database VIEW select statement then wraps the date field (RCDJ) with your DB function. Numeric fields just divide by 10^number-decimal-places.
You failed to provide any info on your database configuration so I suggest you search the forums for a database specific example of converting the julian date to a standard date type.
Here's an example of how the VIEW would look in Oracle:
CREATE OR REPLACE VIEW f3313_view (
Capacity_Mode_CRCAPM ,
Cost_Center_CRMCU ,
Capacity_Type_CRCQT ,
UnitOfMeasure_CRUM ,
Unit_Type_CRUNTY ,
Date_Start_CRSTRT ,
Qty_Transaction_CRTRQT )
AS SELECT
CRCAPM,
CRMCU,
CRCQT,
CRUM,
CRUNTY,
JDE2Date(CRSTRT),
CRTRQT/10000
FROM F3313
WITH READ ONLY;
Here's the Oracle Function to convert the JDE Julian Date to a standard date. Note that others have come up with more elegant solutions for other DBMS' - but this is the only one I've seen posted for Oracle.
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION "PRODDTA"."JDE2DATE" (jdedate IN NUMBER)
RETURN DATE
IS
v_dateout DATE;
v_jdedate NUMBER(6,0) :=0;
v_year NUMBER(4,0) :=0;
v_day NUMBER(3,0) :=0;
BEGIN
v_dateout := TO_DATE(19000101,'YYYY MM DD') ;
if (jdedate IS NOT NULL) AND (jdedate != 0) then
v_jdedate :=jdedate;
v_year := (v_jdedate / 1000) + 1900;
v_day := MOD(v_jdedate,1000) - 1;
v_dateout := TRUNC(TO_DATE(v_year,'YYYY'),'YEAR') + v_day;
end if;
RETURN (v_dateout);
END;
----------------------------------------------------------------------
One thing you may want to change is the default date value if the date field is 0. That's the line that says:
v_dateout := TO_DATE(19000101,'YYYY MM DD') ;
This statement causes the date to be set to Jan 01, 1900 when there is no date value. The alternative is to set the date value to Null as follows:
v_dateout := NULL;
We don't use the NULL value because no other fields in OW are NULL and we try to avoid confusing some of our less technical report writers with the NULL concept. They have been taught that if the date is 01/01/1900 then it is a zero date.
Regards,