Results 1 to 4 of 4

Thread: create a report that trick E1 to use the database views as E1 table

  1. #1

    create a report that trick E1 to use the database views as E1 table

    Hi I am very new in JDE. sorry if I ask dump question.

    I was given a SQL ( select statement with outter join, left join, right join on 7 table). I need to write a report and csv file base on all the column from this SQL . I found this ( ID 640352.1 ) from my oracle support.

    It said that is possible by doing this

    1. Create the database view TEST.FD55134
    2. Grant all on TEST.FD55134 to public;
    3. create E1 table with same name FD55134, save and close the table design Aid
    4. now the E1 table is the database view
    5. now you create E1 Business views on this E1 table. To further create reports..


    My question is
    1. What if I have to modify the database view after the report is created using this view ( through business view )? The change could be update / remove condition in the view .. or adding new column, join new table...etc Will my report still work without doing anything? or I need to re-create the report from ground zero? or????
    2. As usual, we have DEV, TEST and PROD environment. As I can image, the database view and Grant should be run for all three enviornment like DEV.FD55134, TEST.FD55134 and PROD.FD55143. How about the business view and report? I don't think I have to worry about this right?

  2. #2
    Quote Originally Posted by Fsldiscover View Post
    Hi I am very new in JDE. sorry if I ask dump question.

    I was given a SQL ( select statement with outter join, left join, right join on 7 table). I need to write a report and csv file base on all the column from this SQL . I found this ( ID 640352.1 ) from my oracle support.

    It said that is possible by doing this

    1. Create the database view TEST.FD55134
    2. Grant all on TEST.FD55134 to public;
    3. create E1 table with same name FD55134, save and close the table design Aid
    4. now the E1 table is the database view
    5. now you create E1 Business views on this E1 table. To further create reports..


    My question is
    1. What if I have to modify the database view after the report is created using this view ( through business view )? The change could be update / remove condition in the view .. or adding new column, join new table...etc Will my report still work without doing anything? or I need to re-create the report from ground zero? or????
    2. As usual, we have DEV, TEST and PROD environment. As I can image, the database view and Grant should be run for all three enviornment like DEV.FD55134, TEST.FD55134 and PROD.FD55143. How about the business view and report? I don't think I have to worry about this right?
    We have something like this but did it the other way round: Created a table in E1 with table design aid; Deploy & Generate in Environment, go into your DBMS, drop the table and create a view under that name. Done.

    PS: While writing this i remember that i'm not even sure to this day if E1 needs the generation of the table at all but it always felt "cleaner" to me
    EnterpriseOne 8.12 to 9.2
    AIX, Linux, Windows, IBM i
    Oracle DB, MSSQL, DB2
    WAS, WLS

  3. #3
    Schojo. Do you have answers of my questions?

  4. #4
    Member Mike Mackinnon's Avatar
    Join Date
    Nov 2000
    Location
    Halifax, Canada
    Posts
    182
    Yes,
    1.) Create SQL view in all required environments.
    2.) Create JDE table in design aid with the same name as SQL view (do not generate)
    3.) Create JDE business view on new table.
    4.) Create UBE using report with design aid selecting the new business view
    5.) Any modifications to the UBE (new fields for the report) would require a modification to SQL view, JDE table, JDE business view and of course the UBE.
    * If you are just changing the conditions or code in the view and not affecting the columns being presented to JDE then no other changes are required as JDE "doesn't care" about the SQL script.
    Mike MacKinnon
    IMP Group International
    EnterpriseOne tools RL 9.2.2.6, Application RL 9.2
    BI Desktop Publisher 11.1.1.7
    Browser IE10 & Chrome 62.0.3202.94

    - Financials, Inventory (Sales & Procurement), MRP, Manufacturing, Development Tools, BI Publisher

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.