Results 1 to 5 of 5

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
    199
    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

  5. #5
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,401
    Quote Originally Posted by Fsldiscover View Post

    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????

      When I create views like this if my external table has 10 columns I create a mirror table with the columns types and lengths in E1. This is what you see in E1 and the specs E1 will use when talking to the table. You generate the table in E1, with indexes etc
      You then ask your DBA to DROP this from the database and REPLACE it with your View

      So, if you have to come along and create 11 columns, yes you need to start again.

    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?
    You just grant the VIEW the correct privileges for SELECT access for the E1 role connecting etc. If you need to update/insert this needs to be granted too. You don't need to touch the UBE no
    John Danter
    You don't go out looking for a job dressed like that do you, on a weekday?

    E900 TR 9.1.5.3 Unix

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.