Results 1 to 8 of 8

Thread: How to Convert Date Parameter Field to julian and select.

  1. #1

    How to Convert Date Parameter Field to julian and select.

    Hi All,

    I am trying to use Date Prameter to pass values and use them in Data Selection for RCDJ Received Date which is in Julian Format. What is the simplest way to do this?

    Has anyone done data selection this way?

    Analyst/Programmer
    JDE Tools Release 8.97

  2. #2

    Re: How to Convert Date Parameter Field to julian and select.

    I dont understand your question what is the problem...if you using any date in data selection you can input normal date(mm/dd/yyyy)
    VisliCom
    Techno_Functional_Admin_Dba_Web

  3. #3
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: How to Convert Date Parameter Field to julian and select.

    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,
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  4. #4

    Re: How to Convert Date Parameter Field to julian and select.

    Thanks Larry. Sorry forgot to mention the database info, its AS400 migrating to 810 from World. Using crystal to write some reports used in World.
    Analyst/Programmer
    JDE Tools Release 8.97

  5. #5

    Re: How to Convert Date Parameter Field to julian and select.

    Hi Larry,

    One question, we are using the Peoplesoft Integration tool for Crystal and could see the tables and views and one thing we noticed that as compared to ODBC report runs very slowly.

    Any thoughts on why is it so slow?

    Thanks
    Analyst/Programmer
    JDE Tools Release 8.97

  6. #6
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: How to Convert Date Parameter Field to julian and select.

    When you say "Peoplesoft Integration Tool" are you referring to ODA?

    If you are then that explains why. ODA is basically an E1 specific ODBC driver. With ODA there are several additional layers of code that must be gotten through for each data transfer - this slows performance significantly as you have found. In my opinion its just not a robust solution. If you're interested in a product where you don't have to setup database views, etc then check out JDEDIRECT from www.unitysolutions.com.

    Regards,
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  7. #7

    Re: How to Convert Date Parameter Field to julian and select.

    The PeopleSoft Tools integration for Crystal Reports is something new for E1 8.9 onwards and in essence uses a XML interface – this might explain why you’re seeing a performance issue ?

  8. #8
    Member
    Join Date
    Dec 2000
    Location
    Australia
    Posts
    584

    Re: How to Convert Date Parameter Field to julian and select.

    The E1 Crystal integration works by using the enterprise server's CallObject kernels to execute queries and return data via an XML stream. This XMLList feature has been around since XE. The XMLCallObject is the same interface point that WebMethods/XPI uses to talk to E1.

    Yep, it is definitely slower that any sort of direct database access. It works fine but I have found that it simply can't give the performance needed for typical ad-hoc user reporting. It is probably suited for summarised reporting where Crystal does not have to read through a large resultset.

    Regards,
    Justin Miller

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.