This post follows on from a previous post which describes how to seamlessly integrate your APEX and E-Business Suite environments – if you missed that then you can find it here.

Parsing Schema

One of the first things you will need to decide upon when writing applications in APEX for E-Business Suite is the Parsing Schema. There are two main options here.

1. Use the APPS schema which gives you access to all EBS objects.
2. Use a custom schema

There are various views out there on each of the above, however I will try and explain the benefits of each. First I must point out that the supported approach by Oracle is using a custom schema. The whitepaper published by Oracle recommends that a minimal privilege schema be created and all objects granted to that. I have my own views on the justification for this which I have expressed to the author, however you should take your own decision on this. 

APPS

Using the APPS schema as your parsing schema involves very little. You assign the schema and that’s generally it. Your application has access to pretty much everything that you would have in the source E-Business Suite environment and you develop your objects accordingly. So you would continue developing in your custom schema, grant to apps, create the synonym etc. So there is very little additional work you need to do to begin APEX development.
The disadvantage with this approach however is that you have to be very careful with your developments (but you should be anyway!!). If a developer accidentally (or even purposely) introduces a SQL injection bug into an application then there can be a lot more damage done by a malicious user as they have all the privileges of the APPS user. For example, even though you are only querying a view, that user may get pretty much unlimited privileges on your instance.

Custom Schema

This approach utilizes a custom schema where objects are specifically granted with the permissions you require. You can either grant select on the objects you wish to query and then query those tables directly within your applications, or create views and then grant those. The latter is perhaps more “pure”, however there is obviously a trade-off in terms of object counts and so on. Insert/Update/Delete permissions are not granted directly, however are managed through table handler packages instead (more about this later).
The benefit here of course is that should a vulnerability arise in your code then an attacker only has access to the objects granted to APEX, and not to the entire E-Business Suite instance. That said, don’t let this be an excuse not to check your code. The obvious disadvantage is that there is a lot more overhead in the development process as you must create and maintain additional objects. Plus if you are querying HR then this may cause permission issues when querying secure views etc.

So whilst I have my own personal views on the above, I am of course unable to recommend the APPS approach given that Oracle will not do so. If you choose to parse as a custom schema the APEX side of things is exactly the same, however you will need to create grants to your custom schema and then synonyms as that user.

General

In general, any coding concepts that apply to standard E-Business Suite developments should be applied when developing in APEX for EBS. For example messages should not be hard-coded but instead should be set and retrieved using FND Message functionality.
Similarly, database standards obviously apply. Use bind variables wherever possible, not just for performance reasons but also to prevent SQL injection attacks.

The flip side of the coin also applies – always use any standards that you would specific to APEX developments. So, if you restricting data visibility to people (for example only allowing records to be shown where the user is the creator) then always use session state protection. It is trivial to modify the URL otherwise to potentially return records that you shouldn’t have access to. Also never assume that a process is secure – if you have a button calling a dynamic action that calls a process, don’t assume that just because that button isn’t shown to the user that your dynamic action can’t be maliciously triggered. Again, there is a lot that can be done from the URL.

It is also best practice to allow your user to get back your E-Business Suite environment too – my preferred method is to have a navigation bar entry which points to a URL defined by an application item. I then compute this item with an Application Computation upon login.

DML/Queries

Always store as much code as possible in the database. If you have a large complex query to populate a table then create a view on the database and select from that instead.
Don’t use APEX internal row handlers for Insert/Update/Delete operations. These are far too restrictive and inflexible for extensions to E-Business Suite. Code your own table handlers in packages and call those instead. For example:

PROCEDURE insert_birthday_row(p_emp_name IN xxmytable.emp_name%type, p_dob in xxmytable.dob%type, p_emp_id out xxmytable.id%type) IS   l_user_id fnd_user.user_id%type:=fnd_global.user_id; -- You probably want this as a global constant BEGIN   INSERT INTO p_emp_name(id, emp_name, dob, creation_date, created_by, last_update_date last_updated_by) -- last_update_login doesn't get populated if not in an EBS session   VALUES(xxmytable_s.nextval, p_emp_name, p_dob, sysdate, l_user_id, sysdate, l_user_id)   RETURNING id INTO p_emp_id;      -- Other processing, exception handling etc. END insert_birthday_row;  PROCEDURE update_birthday_row(p_emp_id in xxmytable.emp_name%type...) -- and so on 

Then you would have an on-submit process in APEX which calls the above after executing all validations. You could of course use a MERGE statement and have all the above in one statement/call. That is possibly a bit more adventurous. 

Security

One key requirement of an application is to secure appropriately. That doesn’t mean just securing pages. You must also secure any processes, computations etc where applicable. For example, if you have a delete button which is only available to a super user then it is not sufficient to just hide the button to non-super users. You must also ensure that the process itself cannot be run by non-super users, regardless of the user interface.
In terms of providing security at this level, it is always a good idea to use the right tool for the job. APEX provides authorization schemes to do just this. However don’t be tempted to hard-code responsibility names and so on in these. It will inevitably lead to extensibility problems! Instead define the levels of access within your application, taking particular care to ensure that higher levels can inherit the permissions of lower levels. Profile values are very useful for this. Let’s take an example; presume we have a generic level of access which is READ ONLY but then we have UPDATE level access and then a super level of access who can also DELETE. We may create two profile values XXBY_CAN_UPDATE_MY_APP and XXBY_CAN_DELETE_MY_APP. We would then have two authorization schemes, one for update and one for delete.

Then for all responsibilities requiring update I would functionally set that to Y in E-Business Suite. Note however – The super responsibility that can delete records must also have this set. Otherwise you are effectively saying “this responsibility can delete but not update”. There are of course things you can do which are a lot more clever, however this is just a very basic example with something that will get you an application that can be secured differently for different responsibilities.

Human Resources

There are a number of challenges to overcome when attempting to query data from Oracle HRMS, however whether these will apply to your environment will depend on your setup and configuration. First, secure views. These can be a bit hit and miss, and depends on whether you are using dynamic or static lists. Generally, if you are using static lists then the person and assignment secure views will work OK. However, if you are using dynamic lists such as the supervisor hierarchy then it is necessary to set up the ANONYMOUS user as a reporting user in E-Business Suite (see My Oracle Support for how to do that). You should of course ensure your configuration of this user is such to prevent people from logging in manually with a weak password and querying the data directly. I’m not going to go into why this is necessary however the HR Security Configuration guide available from MOS goes into some detail on reporting users and the history. I’ve found however that querying from the organization secured views can be a little unpredictable – I’m looking further into that and have found others who have had similar issues.
Second, date-tracking. HR uses date tracking extensively and whilst it is possible to manually implement date tracking using the date-track columns, it is perhaps a little more elegant to use the fnd_session date-tracked views such as per_people. The issue here however is that you don’t have an FND session and so querying from such views will yield no results. Oracle’s suggested approach to combat this is to insert a record manually into fnd_sessions. Taking the issue we discussed in the installation blog, whereby sessions are not guaranteed to be maintained, this clearly must be implemented for each session. So the logical place to do this is to extend the VPD functionality to insert (or actually a merge) a row into fnd_sessions. Then on the page clean up process perform a delete. This will ensure that the session never gets permanently stored in the table – it is visible to your session only, for the duration of that page load. Clearly this adds the overhead of introducing two additional statements per page (the insert/merge and the delete) and whilst these should be very lightweight, you should take that into consideration if working with a very high volume of sessions/transactions.
My suggestion would be to hold the effective date in a page item on your page which can be set accordingly. Again, best practice would dictate this logic is held in a stored procedure and that procedure called.

   PROCEDURE init_effective_date (p_date IN fnd_sessions.effective_date%TYPE)    IS    BEGIN      MERGE INTO fnd_sessions fs      USING dual d         ON (fs.session_id = USERENV ('sessionid'))      WHEN MATCHED      THEN       UPDATE SET fs.effective_date=NVL(p_date,TRUNC(SYSDATE))      WHEN NOT MATCHED      THEN      INSERT     (session_id, effective_date)        VALUES (USERENV ('sessionid'), NVL(p_date,TRUNC(SYSDATE)));    END init_effective_date; 

Then a cleanup pl/sql code section along the lines of

   PROCEDURE cleanup_effective_date    IS    BEGIN       DELETE fnd_sessions fs        WHERE fs.session_id = USERENV ('sessionid');    END cleanup_effective_date; 

It’s very difficult to give a complete view of all development standards and best practices however hopefully the above will help someone along a little. Please feel free to ask in the comments section if you have any specific questions, suggestions etc.