Sample Scripts copy of Responsibilities from one user account to another user account

 fnd_user_pkg.addresp is an Oracle standard API to add responsibilities to a user account.

Below is the script to copy responsibilities of user “INTG_USER” to “IMPL_USER” 

DECLARE

  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'INTG_USER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'IMPL_USER',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;

Thanks,

Amar

Oracle Apps + SQL Query to Get Oracle Menus, Sub Menus and Functions

Posted by Amar Alam at 03:11 2 comments

 1. Query below will give tree structure as per menu_id, but I need to get the backend User Function Name as well (which was missing).

SELECT * FROM 
      (SELECT  menu_id, sub_menu_id, function_id,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.menu_id
AND language='US') menu_name,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.sub_menu_id
AND language='US') sub_menu,(select USER_FUNCTION_NAME from fnd_form_functions_vl 
where function_id=fme.function_id) fucntion_name,
         LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
                FROM fnd_menu_entries_vl fme
                WHERE prompt IS NOT NULL
                CONNECT BY PRIOR sub_menu_id = menu_id
                START WITH menu_id = 79980
              AND menu_id =  79980
              AND prompt IS NOT NULL
              AND grant_flag = 'Y'
              ORDER BY entry_sequence) a
     CONNECT BY PRIOR sub_menu_id = menu_id
     START WITH menu_id =  79980  /*like INV_NAVIGATE*/
     AND menu_id =  79980
     AND prompt IS NOT NULL;

2).

SELECT LPAD(' ', 6*(LEVEL-1)) || menu_entry.entry_sequence sequence 
, LPAD(' ', 6*(LEVEL-1)) || menu.user_menu_name SubMenu_Descrition 
, LPAD(' ', 6*(LEVEL-1)) || func.user_function_name Function_Description 
, menu.menu_id 
, func.function_id 
, menu_entry.grant_flag Grant_Flag 
, DECODE( menu_entry.sub_menu_id 
, null, 'FUNCTION' 
, DECODE( menu_entry.function_id 
, null, 'SUBMENU' 
, 'BOTH') 
) Type 
FROM fnd_menu_entries menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND grant_flag = 'Y'
START WITH menu_entry.menu_id = (SELECT menu_id 
FROM fnd_menus_tl menu2 
WHERE menu2.user_menu_name = '&Parent_Menu_User_Name') 
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence

3).

SELECT * FROM
     (SELECT  menu_id, sub_menu_id, function_id,
        ,(select function_name from fnd_form_functions_vl where function_id= fme.function_id) func,
        LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
               FROM fnd_menu_entries_vl fme
               WHERE prompt IS NOT NULL
               CONNECT BY PRIOR sub_menu_id = menu_id
               START WITH menu_id = 67605
             AND menu_id =  67605
             AND prompt IS NOT NULL
             AND grant_flag = 'Y'
             ORDER BY entry_sequence) a
    CONNECT BY PRIOR sub_menu_id = menu_id
    START WITH menu_id =  67605  /*like INV_NAVIGATE*/
    AND menu_id =  67605
    AND prompt IS NOT NULL;

How to enable the “Operating Unit” Parameter for a Concurrent Program in Oracle Apps R12

Posted by Amar Alam at 04:29 17 comments

We notice that the new field as 'Operating Unit' added in R12 and this is by default is in disabled mode:

An applications responsibility can access multiple OUs. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of

'S' for single operating unit and 'M'(concurrent program will be in this mode by default) for multiple operating units.

If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The ‘Operating Unit’ field/parameter is known as ‘Reporting Context’ in MOAC(Multi Org Access Control) terminology.

How we can achieve from the Oracle Applications Front-End.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent è Programs

3. Query for Short Name or Program Name of the concurrent program like as below.

4. Click on Update icon of your program.

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field.
7. Save changes by clicking on 'Apply' button.

8. Change responsibility where the ‘Concurrent Program’ can be run, in this example ‘TESTOU’ is assigned to ‘AR Super User’ responsibility                              

9. Select the ‘Concurrent Program’ from the ‘SRS Window’, now you can see that ‘Operating 

    Unit’ field is enabled and you can see the Operating Units in the LOV.

 

10. This particular selected Operating Unit value can be accessed using the standard MOAC API:MO_GLOBAL.GET_CURRENT_ORG_ID