Using Complex Database Views on EnterpriseOne

A Database View is a subset of the database sorted and displayed in a particular way. For example, in an Invoice database, perhaps you only wish to display the Vendors stored in the database.

EnterpriseOne Views are similar in nature, where it sits on top on JD Edwards Tables, to retrieve data, or a subset of data.
With Business Views, we can join tables using Left outer, Right outer, Union, Intersect joins. One can usually join up to 5 tables in a Business View if using Simple joins. If one uses a Complex join, then number of tables is limited to 3.
What if you need to use more than 5 tables? What if you need to do some complex joins? And What if you need a column which would give you a Count of records for the group?
For e.g. if you have a need to display the number of items on Open orders and quantities shipped, with the order details, you may have to do the following SQL
SELECT sddoco, sdkcoo, sddcto, count(*), sum(sduopn)
FROM F0411
WHERE sdnxtr <=540
GROUP BY sddoco,sdkcoo,sddcto
To accomplish this in current JD Edwards EnterpriseOne Reporting, you will need to do a little more programming.
We can do this easily via Database Views. We can trick the EnterpriseOne software to use the Database views as E1 tables.
We will need to do the following steps.

1. Create a Data base view on the above Select query with name FD550411 (use a different naming convention so that they stand out as Database Views)
2. CREATE VIEW TESTDTA.FD554211 (dddoco, ddkcoo, dddcto, ddcnt, dduopn )
AS (SELECT (sddoco, sdkcoo, sddcto, count(*), sum(sduopn))
FROM F4211
WHERE sdnxtr <=540
GROUP BY sddoco,sdkcoo,sddcto)
3. Grant privileges on the View to the EnterpriseOne User id for the database, like
   Grant ALL on TESTDTA.FD554211 to PUBLIC;
4. Now, create an EnterpriseOne table with the same name = FD554211, and using the table Prefix = DD (as used in the Database View)
   1. Add the columns in the same order as in the view (created above) –
      1. DOCO
      2. KCOO
      3. DCTO
      4. CNT
      5. UOPN
   2. Save and close the Table Design Aid (TDA)
5. Do NOT Generate the Table.
6. Now, you can see the Data coming up in UTB for FD554211.
7. Once done, you have your E1 Table ready on top of the Database View.
8. You can create E1 Business Views on this E1 Table, and create/use UBE’s, Reports, Application via the Business View.

Only Con’s of this approach is that you cannot Insert or Update or Delete records in the Database.
Now, incase you are still not able to view/see the Database View in UTB or JDE, do the following

1. Create the Table First in JDE.
2. Generate it in JDE. Now you have a physical table in Database
3. Drop the table from the database –
   DROP TABLE TESTDTA.FD554211;
4. Now, you will be able to create your View as given above.
5. Grant all privileges to the View.
6. This time you should be able to view it in JDE.

Know more at MOS: [ID 640352.1]