If you want to view data coming from EBS in your APEX application, follow these steps:

Identify where the data is in EBS

If you are not familiar with the data model of EBS, it can be hard to find the right information. A good starting point would be the APPS schema, because that has access to the complete Oracle E-Business Suite data model. You can compare it with the SYSTEM schema, which has access to the entire database.

This pictures shows an overview of the APPS schema and base product schemas.

In my example I wanted to find the people that are in my organisation (HR). I started to look for views that would give me that information. My first query was like this:

select object_name from user_objects where object_name like '%PEOPLE%' and object_type = 'VIEW'
order by 1

That query returned 82 rows in my environment. In the results I saw e.g. ADS_PEOPLE_V, HRBG_PEOPLE, PER_ALL_PEOPLE, PER_PEOPLE, PER_PEOPLE_F etc.

I started to look at the definitions of those, but if you are not familiar with EBS it's hard to know which one is the one you need. So my recommendation would definitely be; when you are not that familiar with EBS, talk with somebody who knows more about it. For me that is the case, I only started to look into EBS and actually do something with it, a few weeks ago.

When I talked to somebody more experienced in EBS, he told me I probably wanted to look at PER_ALL_PEOPLE_F. Hmm, that wasn't in the result set of the above query. After investigating a bit more PER_ALL_PEOPLE_F is a synonym for HR.PER_ALL_PEOPLE_F.

I wanted to understand the naming convention in EBS a bit better e.g. for the PER%PEOPLE% objects.

Below I created a table how I interpret the EBS objects:

View /
Synonym (^)

count(*)

count(distinct person_id)

Interpretation

per_all_people_f (^)

32295

18518

Synonym to real HR table

per_all_people

0

0

Needs EBS session (record in
FND_SESSIONS) so it knows what you can see

per_all_people_d

32295

18518

All records but shows
translated text if user settings are applied

per_people

0

0

Needs EBS session, shows
effective records based on user's date

per_people_f

32295

18518

EBS security implemented, you
only see records you are allowed to see

per_people_v

0

0

Needs EBS Session, includes a
lot of display text and is language dependend

per_people_x

18518

18518

EBS security implemented (same
as per_people_f), but limits to only the effective records
(WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND
EFFECTIVE_END_DATE)

So to me PER_PEOPLE_X looks like a good candidate to use in my APEX application. If I'm not logged into the app as an E-Business user I still see all records that are effective at the time I run the query.

Create a view on top of the EBS views and use some naming conventions so it's easy to recognise which objects you created and are not native EBS ones.

create view apex_per_people_vw as select * from per_people_x

Grant access on that view to the schema that is linked to your APEX workspace and application

grant select on apex_per_people_vw to apex_ebs

Create a view in your own schema that selects everything from the view in the apps schema.We do that so that the views are a one-on-one mapping between schema's, but they show up in the APEX wizards.

This first examples shows how you can view data from EBS in your own APEX application. We can now create a calendar, charts etc. in APEX based on the data coming from EBS. In the next post I will show how you can edit this data.