• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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. [FONT=Lato, sans-serif]Create the database view TEST.FD55134[/FONT]
  2. [FONT=Lato, sans-serif][/FONT][FONT=Lato, sans-serif]Grant all on TEST.FD55134[/FONT] 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?
 

schojo44

Member
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 :confused:;) but it always felt "cleaner" to me
 
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.
 

johndanter

Member
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
 
Top