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.
Database View Diagram
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 FD550411 (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
  4. Now, create an EnterpriseOne table with the same name = FD550411, 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 FD550411.
  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.

Know more at MOS: [ID 640352.1]

Leave a Reply