Updating EBS data from APEX using Triggers and APIs (3)

In the previous posts I showed how to call the EBS APIs in a Page Process of the APEX page.

Before that post I blogged about using views to query the EBS data. In this post we will do a combination of those techniques.

In APEX you can develop really fast; e.g. when you create a Form on table with Report, in less than a minute you have an Interactive Report where people can view and analyse the data in different ways. Clicking on the edit link in the report will allow you to update and delete (and create) the data.

By building your pages manually and creating the fetch and process data manually (as in the previous APIs posts), you lose a bit of productivity. By using updateable views you gain again some development speed. The updateable view technique, which means creating "INSTEAD OF" triggers on top of your views, is not specific for EBS, but you can use in any project (for example in 2006, I blogged about this feature when I was using it in DG Tournament).

When I tried to create a person or update the email address of a person through my updateable view I received following error:

Unless I missed a setting in EBS, it looks like the EBS API call I do in the trigger is not really "trigger compatible". When I discussed with Thierry and Paolo they told me about a parameter called p_validate (which changing didn't resolve my issue) and that using pragma autonomous_transaction might work. When I tried my trigger as an autonomous transaction I received following error:

That error was solved by adding a commit in the trigger.

So this is my "INSTEAD OF" trigger for the apex_per_people_vw in the APPS schema
(if you create the trigger on top of the view in your own schema you get an insufficient privilege error, see previous posts how I did the grants)

create or replace trigger apex_per_people_vw_trg
instead of insert or update on apex_per_people_vw
declare
  l_person_id                 number;
  l_assignment_id             number;
  l_per_object_version_number number;
  l_asg_object_version_number number;
  l_assignment_sequence       number;
  l_assignment_number         varchar2(4000);
  l_object_version_number     number;
  l_employee_number           varchar2(4000);
  l_effective_start_date      date;
  l_effective_end_date        date;
  l_full_name                 varchar2(4000);
  l_comment_id                number;
  l_name_combination_warning  boolean;
  l_assign_payroll_warning    boolean;
  l_orig_hire_warning         boolean;  
  -- use of Autonomous Transaction needed for this API
pragma autonomous_transaction;
begin
  if INSERTING 
  then
    apps.apex_api_pkg.create_person ( 
      p_hire_date                 => trunc(:NEW.effective_start_date), 
      p_business_group_id         => :NEW.business_group_id,     
      p_last_name                 => :NEW.last_name,     
      p_sex                       => :NEW.sex,
      p_first_name                => :NEW.first_name,     
      p_date_of_birth             => :NEW.date_of_birth,     
      p_email_address             => :NEW.email_address,     
      p_employee_number           => l_employee_number,     
      p_person_id                 => l_person_id,     
      p_assignment_id             => l_assignment_id,     
      p_per_object_version_number => l_per_object_version_number, 
      p_asg_object_version_number => l_asg_object_version_number, 
      p_per_effective_start_date  => l_effective_start_date, 
      p_per_effective_end_date    => l_effective_end_date, 
      p_full_name                 => l_full_name, 
      p_per_comment_id            => l_comment_id, 
      p_assignment_sequence       => l_assignment_sequence, 
      p_assignment_number         => l_assignment_number, 
      p_name_combination_warning  => l_name_combination_warning, 
      p_assign_payroll_warning    => l_assign_payroll_warning, 
      p_orig_hire_warning         => l_orig_hire_warning 
    );
  elsif UPDATING 
  then
    l_object_version_number := :OLD.object_version_number;
    apps.apex_api_pkg.update_person_email (
      p_effective_date            => trunc(:NEW.effective_start_date),
      p_datetrack_update_mode     => 'CORRECTION',
      p_person_id                 => :OLD.person_id,
      p_email_address             => :NEW.email_address,
      p_object_version_number     => l_object_version_number,
      p_employee_number           => l_employee_number,
      p_effective_start_date      => l_effective_start_date,
      p_effective_end_date        => l_effective_end_date,
      p_full_name                 => l_full_name,
      p_comment_id                => l_comment_id,
      p_name_combination_warning  => l_name_combination_warning,
      p_assign_payroll_warning    => l_assign_payroll_warning,
      p_orig_hire_warning         => l_orig_hire_warning
    );  
  end if;
  -- as we user autonomous transaction a commit or rollback is necessary
  commit;
exception
  when others
  then
    rollback;
end;

On page 10 of the Oracle whitepaper about the integration of E-Business Suite and APEX you find another working example of the updateable view method with the FND_FLEX_VAL_API without an autonomous transaction.

So which technique is best? Using updateable views or call the APIs from the Page Processes?
It depends, they have both advantages and disadvantages, but sometimes you don't have a choice and can't use the trigger method.

Advantages using triggers:

• Code in one place regardless which APEX page/process inserts/updates/deletes on the view
• Able to use the APEX wizards to build report and form on top of the view with build-in APEX processes

Disadvantage (or things you should know) using triggers:

• Not every EBS API seems to work through triggers
• Might need to use Autonomous transaction in your trigger to get it working, but what are the side effects? (especially as I don't know what is going on inside the EBS API call)
• Recreating the view will lose the trigger, so make sure you can recreate the trigger
• APEX automatic row locking might interfere with EBS locking (the Oracle whitepaper doesn't mention anything of that, but Paolo posted a comment about this, also see documentation of FSP_DML_LOCK_ROW - I didn't experience this yet)
• Other people might not know about those triggers, so make sure it's clear to who you work with or who will take over the project to tell him you use triggers 
• Read Tom Kyte's article about triggers, so you know when to use/not use them

Advantages using custom process

• Full control when you want to run some code
• Works regardless of EBS API
• Different logic possible on different screens on the same data
• Easier to debug (how much time a process takes, debug output etc.)

Disadvantages using custom process

• Takes more time to build the pages and processes manually. There is a wizard to build on top of a procedure too, but I find that still slower than if you can use the wizard to build on top of a view/table.
• More code if you need to call the same procedure on multiple pages

If you experienced other advantages/disadvantages, feel free to comment.

Previous related posts:

• Creating EBS data from APEX using APIs (2)
• Updating EBS data from APEX using APIs (1)
• Querying EBS data from APEX using Views
• E-Business Suite and APEX integration (overview)
• E-Business Suite and APEX installation
• APEX and E-Business Suite integration (OBUG event)