Apps initialize in R12

In release 12, the old dbms_application_info.set_client_info(<org_id>) is not enough to view data from apps views. This is due to the new security model.

In order to do so, the environment must be fully initialized. You can do this by running the script bellow, replacing :USER and :RESPONSIBILITY_NAME with the correct value:

DECLARE
  CURSOR c_details(p_user IN VARCHAR2, p_resp_name IN VARCHAR2) IS
    SELECT user_id,responsibility_id,responsibility_application_id, security_group_id
      FROM fnd_user_resp_groups
     WHERE user_id = (SELECT user_id FROM fnd_user WHERE user_name = p_user)
       AND responsibility_id in (SELECT responsibility_id FROM fnd_responsibility_tl WHERE responsibility_name = p_resp_name)
         ;
  CURSOR c_application(p_application_id IN NUMBER) IS
    SELECT application_short_name
      FROM fnd_application
     WHERE application_id = p_application_id
         ;
  l_user_id                NUMBER;
  l_responsibility_id      NUMBER;
  l_resp_appl_id           NUMBER;
  l_security_group_id      NUMBER;
  v_user_name              VARCHAR2(30);
  v_responsibility_name    VARCHAR(100);
  v_application_short_name VARCHAR2(10);
BEGIN
  v_user_name := :USER;
  v_responsibility_name := :RESPONSIBILITY_NAME;
  /**/
  OPEN c_details(v_user_name, v_responsibility_name);
  fetch c_details INTO l_user_id, l_responsibility_id, l_resp_appl_id, l_security_group_id;
  CLOSE c_details;
  /**/
  OPEN c_application(l_resp_appl_id);
  fetch c_application INTO v_application_short_name;
  close c_application;
  /**/
  fnd_global.apps_initialize(l_user_id,l_responsibility_id,l_resp_appl_id);
  mo_global.init (‘S’);
END;