1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to get All Active Users and their Active Responsibilities

Discussion in 'Oracle Apps Technical' started by jagadekara, Feb 11, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    SELECT fu.user_name "User Name",
    frt.responsibility_name "Responsibility Name",
    furg.start_date "Start Date",
    furg.end_date "End Date",
    fr.responsibility_key "Responsibility Key",
    fa.application_short_name "Application Short Name"
    FROM fnd_user_resp_groups_direct furg,
    applsys.fnd_user fu,
    applsys.fnd_responsibility_tl frt,
    applsys.fnd_responsibility fr,
    applsys.fnd_application_tl fat,
    applsys.fnd_application fa
    WHERE furg.user_id = fu.user_id
    AND furg.responsibility_id = frt.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fa.application_id = fat.application_id
    AND fr.application_id = fat.application_id
    AND frt.language = USERENV('LANG')
    -- AND UPPER(fu.user_name) = UPPER('Jagadekar') -- <change it>
    AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
    AND (fu.end_date IS NULL OR fu.end_date >= TRUNC(SYSDATE))
    ORDER BY 1,2;
     
  2. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jadadekara,

    This will return Direct Responsibilities, however to fetch indirect responsibilities as well 'fnd_user_resp_groups_indirect' has to be used.

    Regards,
    Yowan Cristo
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oh! Thanks for your valuable info Yowan...