OAF: SQL Query to get all the Personalization on the OAF Pages in Oracle

Below Sql script is used to get all the Personliazations on the OAF Page in Oracle.

--------------------------------------------------------------------------------------------------------

SELECT PATH.path_docid perz_doc_id,

         jdr_mds_internal.getdocumentname (PATH.path_docid) perz_doc_path

    FROM jdr_paths PATH

   WHERE PATH.path_docid IN

               (SELECT DISTINCT comp_docid

                  FROM jdr_components

                 WHERE     comp_seq = 0

                       AND comp_element = 'customization'

                       AND comp_id IS NULL)

         AND UPPER (jdr_mds_internal.getdocumentname (PATH.path_docid)) LIKE

               UPPER ('%CustomPageNamePG%')

ORDER BY perz_doc_path;

--------------------------------------------------------------------------------------------------------

SQL to add System Administrator Responsibility to user from Back-end in Oracle

Below Sql script is used to add System Administrator Responsibility to user from back end in Oracle.

SET SERVEROUTPUT ON SIZE 10000000;

BEGIN
   fnd_user_pkg.addresp ('USER_NAME',
                         'SYSADMIN',
                         'SYSTEM_ADMINISTRATOR',
                         'STANDARD',
                         'Add Responsibility to USER using pl/sql',
                         SYSDATE,
                         SYSDATE + 100);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   ' Responsibility is not added due to '
                           || SQLCODE
                           || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;