Query to get All Users Responsibilities

  SELECT fu.user_name "User Name",
         (SELECT full_name
            FROM per_all_people_f
           WHERE     SYSDATE BETWEEN effective_start_date
                                 AND effective_end_date
                 AND employee_id = person_id)
            "Employee Name",
         fu.START_DATE "User Starting Date",
         fu.END_DATE "User End Date",
         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",
         fat.application_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 fat.language = USERENV ('LANG')
ORDER BY 1

Changing oracle user password FND_USER_PKG.CHANGEPASSWORD

DECLARE
   v_user_name      VARCHAR2 (30) := UPPER ('&user_name');
   v_new_password   VARCHAR2 (30) := '&new_password';
   v_status         BOOLEAN;
BEGIN
   v_status :=
      fnd_user_pkg.ChangePassword (username      => v_user_name,
                                   newpassword   => v_new_password);

   IF v_status = TRUE
   THEN
      DBMS_OUTPUT.put_line (
         'The password reset successfully for the User:' || v_user_name);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (
            'Unable to reset password due to'
         || SQLCODE
         || ' '
         || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
   END IF;
END;

Query to get all Org attached to responsibility

SELECT responsibility_name,
         (SELECT name
            FROM hr_all_organization_units
           WHERE organization_id = fpov.profile_option_value)
            operating_unit
    FROM applsys.fnd_profile_option_values fpov,
         applsys.fnd_profile_options fpo,
         applsys.fnd_profile_options_tl fpot,
         applsys.fnd_responsibility_tl fr
   WHERE     1 = 1
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fpo.profile_option_id = fpov.profile_option_id
         AND fr.responsibility_id(+) = fpov.level_value
         -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
         AND fpot.profile_option_name = 'ORG_ID'
         -- and fpov.profile_option_value='11'
         --AND fr.responsibility_name LIKE '%Payables%Manager%'
         AND fr.language = 'US'
         AND fpot.language = 'US'
ORDER BY 2

sending report output (PDF) as attachment in customizable email

1. Create RDF file using the attached template (XXTEST_REP.rdf)
2. You have to rename the file name to your report file name.
3. You have to set the following formulas inside the RDF file (PLSQL Formula).

4. Use the BODY_TEXT formula to return the email HTML body.
5. You can customize FORMAT_BODY function to customize body text using HTML tags.
6. Use PUT_LINE to add new text to email body ends with BR(Enter).
7. Use NEW_LINE function to add new empty line to email body.
8. You have to set attachment file name using REPORT_NAME.
9. You have to copy RTF file to the same path with RDF file on the server.
10. You have to set report application top inside RTF_FILE_PATH formula (FND_TOP in our sample and PER_TOP for human resources application,…..etc).

11. You have to set the RTF file path/name on the server using RTF_FILE_PATH. 

12. You have to change the following query without changing the group name (G_BURSTING).  

13. Upload the attached bursting control file (BURSTING_CONTROL.xml) when you define the template data definition (Update Mode) .

BURSTING_CONTROL.xml:

<xapi:requestset type="bursting" xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

<xapi:request select="/XXTEST_REP/G_BURSTING">

    <xapi:delivery>

      <xapi:email reply-to="" from="erp@xyz.com" port="25" server="10.10.100.41">

      <xapi:message subject="${EMAIL_SUBJECT}" content-type="text/html" attachment="true" to="${SEND_TO}" cc="${CC_SEND_TO}" id="123">

         <![CDATA[<HTML> <BODY> ${BODY_TEXT}</BODY></HTML> ]]>

      </xapi:message>

      </xapi:email>

    </xapi:delivery>

<xapi:document delivery="123" output-type="pdf" output="${REPORT_NAME}">

    <xapi:template type="rtf" location="${RTF_FILE_PATH}"> </xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

Query to get Oracle apps users password

Sometimes you need to get the password for specific oracle apps user, firstly you should create the below Package:

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;


Query:
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'SYSADMIN';

Query to list all form personalization

This query is used to list all form personalization by passing form  function name:

SELECT
         FUNCTION_NAME,
         FORM_NAME,
         ID,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         LAST_UPDATE_LOGIN,
         SEQUENCE,
         RULE_KEY,
         DESCRIPTION,
         RULE_TYPE,
         ENABLED,
         TRIGGER_EVENT,
         TRIGGER_OBJECT,
         CONDITION,
         FIRE_IN_ENTER_QUERY
    FROM FND_FORM_CUSTOM_RULES
   WHERE     (   (function_name = 'PO_POXPOEPO' AND rule_type = 'A')
              OR (form_name = 'POXPOEPO' AND rule_type = 'F'))
         AND rule_key IS NULL
ORDER BY sequence

To Remove Responsibility from user

Declare

CURSOR cur_resp
   IS
      SELECT application_short_name, responsibility_key, security_group_key
        FROM fnd_responsibility fr,
             fnd_application fa,
             fnd_security_groups frg,
             fnd_responsibility_tl frt
       WHERE     fr.application_id = fa.application_id
             AND fr.data_group_id = frg.security_group_id
             AND fr.responsibility_id = frt.responsibility_id
             AND frt.language = 'US'
             AND fr.responsibility_id = 12345;
BEGIN
   FOR i IN cur_resp
   LOOP
      BEGIN
         fnd_user_pkg.delresp (username         => 'ALI',
                               resp_app         => i.application_short_name,
                               resp_key         => i.responsibility_key,
                               security_group   => i.security_group_key);
     
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;

      DBMS_OUTPUT.put_line (
         
         || i.application_short_name
         || ' '
         || i.responsibility_key
         || ' '
         || i.security_group_key);

   END LOOP;

   COMMIT;
END;

To Add user Responsibility like another user

DECLARE
   lc_user_name              VARCHAR2 (100) := 'ALI';
   lc_resp_appl_short_name   VARCHAR2 (100) := 'SYSADMIN';
   lc_responsibility_key     VARCHAR2 (100) := 'SYSTEM_ADMINISTRATOR';
   lc_security_group_key     VARCHAR2 (100) := 'STANDARD';
   ld_resp_start_date        DATE := TO_DATE ('25-MAR-2015');
   ld_resp_end_date          DATE := NULL;
BEGIN
   fnd_user_pkg.addresp (username         => lc_user_name,
                         resp_app         => lc_resp_appl_short_name,
                         resp_key         => lc_responsibility_key,
                         security_group   => lc_security_group_key,
                         description      => NULL,
                         start_date       => ld_resp_start_date,
                         end_date         => ld_resp_end_date);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

Query to get Concurrent Programs

 SELECT f.user_concurrent_program_name,
         f.concurrent_program_name,
         f.output_print_style,
         f.output_file_type,
         column_seq_num no,
         end_user_column_name parameter,
         a.form_left_prompt prompt,
         b.flex_value_set_name value_set,
         DECODE (a.default_type,
                 'C', 'Constant',
                 'P', 'Profile',
                 'S', 'SQL Statement')
            default_type,
         a.DEFAULT_VALUE,
         required_flag required,
         srw_param token,
         a.enabled_flag display,
         g.application_table_name,
         g.additional_where_clause,
         srw_param
    FROM fnd_descr_flex_col_usage_vl a,
         fnd_flex_value_sets b,
         fnd_flex_validation_tables g,
         fnd_concurrent_programs_vl f
   WHERE     1 = 1
         AND SUBSTR (descriptive_flexfield_name, 7) LIKE 'XX%'
         AND a.flex_value_set_id = b.flex_value_set_id
         AND SUBSTR (descriptive_flexfield_name, 7) = f.concurrent_program_name
         AND b.flex_value_set_id = g.flex_value_set_id(+)
ORDER BY descriptive_flexfield_name, column_seq_num