Skip to main content

Query to Add Responsibility - Oracle apps R12

Use below query to add responsibility from backend  


DECLARE

    v_user_name        VARCHAR2 (20) := 'USERNAME';

    v_req_resp_name    VARCHAR2 (50) := 'System Administrator';

    v_description      VARCHAR2 (100) := 'Adding Responsibility to user using script';

    v_appl_shrt_name   VARCHAR2 (20);

    v_appl_name        VARCHAR2 (50);

    v_resp_key         VARCHAR2 (50);

BEGIN

    SELECT fav.application_short_name,

           fav.application_name,

           frv.responsibility_key

      INTO v_appl_shrt_name, v_appl_name, v_resp_key

      FROM FND_APPLICATION_VL fav, FND_RESPONSIBILITY_VL frv

     WHERE frv.application_id = fav.application_id

           AND frv.responsibility_name = v_req_resp_name;


    fnd_user_pkg.addresp (username         => v_user_name,

                         resp_app         => v_appl_shrt_name,

                          resp_key         => v_resp_key,

                          security_group   => 'STANDARD',

                          description      => v_description,

                          start_date       => SYSDATE,

                          end_date         => NULL);

    COMMIT;

    DBMS_OUTPUT.put_line ('The responsibility ' || v_req_resp_name || ' is added to the user ' || v_user_name);

EXCEPTION

    WHEN OTHERS THEN

       DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

       ROLLBACK;

END;


Comments

Popular posts from this blog

Query to find concurrent programs associated to Responsibility - Oracle Apps R12

 Query to Find Programs associated to Responsibility SELECT frt.responsibility_name, frg.request_group_name,     frgu.request_unit_type,frgu.request_unit_id,     fcpt.user_concurrent_program_name     FROM fnd_Responsibility fr, fnd_responsibility_tl frt,     fnd_request_groups frg, fnd_request_group_units frgu,     fnd_concurrent_programs_tl fcpt     WHERE frt.responsibility_id = fr.responsibility_id     AND frg.request_group_id = fr.request_group_id     AND frgu.request_group_id = frg.request_group_id     AND fcpt.concurrent_program_id = frgu.request_unit_id     AND frt.LANGUAGE = USERENV('LANG')     AND fcpt.LANGUAGE = USERENV('LANG')     AND fcpt.user_concurrent_program_name = :conc_prg_name     ORDER BY 1,2,3,4

Query to find Open PO Periods - Oracle Apps R12

 Query to Find Open PO Periods for Month End Closure SELECT sob.name "Set of Books" ,   fnd.product_code "Product Code" ,   ps.PERIOD_NAME "Period Name" ,   ps.START_DATE "Period Start Date" ,   ps.END_DATE "Period End Date" ,   DECODE(ps.closing_status, 'O','O - Open' ,                             'N','N - Never Opened' ,                             'F','F - Future Enterable' ,                             'C','C - Closed' ,'Unknown') "Period Status" FROM gl_period_statuses ps ,   Apps.GL_SETS_OF_BOOKS sob ,   FND_APPLICATION_VL fnd WHERE ps.application_id      IN (201) -- 101 GL & 201 PO  AND sob.SET_OF_BOOKS_ID       = ps.SET_OF_BOOKS_ID AND fnd.application_id        = ps.appl...

Query for profile options - Oracle Apps R12

 Query for Profile Options Profile’s Enabled at Site Level SELECT fpot.user_profile_option_name, fpov.profile_option_value   FROM fnd_profile_option_values fpov,        fnd_profile_options fpo,        fnd_profile_options_tl fpot  WHERE     fpo.profile_option_id = fpov.profile_option_id        AND fpo.profile_option_name = fpot.profile_option_name        AND fpot.language = 'US'        AND fpov.level_id = 10001                              -- Site Level ID Profile’s Enabled at Application Level SELECT fat.application_name,        fpot.user_profile_option_name,        fpov.profile_option_value   FROM fnd_profile_option_values fpov,        fnd_profile_options fpo,        fnd_profile_options_tl fpot, ...