How to delete datadefinition and Template

Script for deleting the datadefinition
————————————————>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
Commit;
END;
——————————————————–
——————————————————–
script for deleting the template
——————————————————>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
Commit;
END;

OR

declare
begin
delete   from XDO_TEMPLATES_B        where template_code    = <template_code>
delete   from XDO_TEMPLATES_TL       where template_code    = <template_code>
delete   from xdo_lobs               where lob_code         = <template_code>
delete   from XDO_DS_DEFINITIONS_TL  where data_source_code = <def_code>
delete   from XDO_DS_DEFINITIONS_b   where data_source_code = <def_code>
end;

Commit;

query to find users who have a responsibility

  SELECT usr.user_id,
         usr.user_name,
         res.RESPONSIBILITY_ID,
         res.RESPONSIBILITY_NAME
    FROM apps.FND_USER usr,
         apps.FND_RESPONSIBILITY_TL res,
         apps.FND_USER_RESP_GROUPS grp
   WHERE     (   UPPER (res.RESPONSIBILITY_NAME) LIKE '%SUZ%'
              OR UPPER (res.RESPONSIBILITY_NAME) LIKE '%NANJING%'
              OR UPPER (res.RESPONSIBILITY_NAME) LIKE '%SZ%')
         AND grp.responsibility_id = res.responsibility_id
         AND grp.user_id = usr.user_id
ORDER BY 2

Batch Status in OPM

select * from fnd_lookup_values where lookup_type  like 'BATCH_STATUS' AND LANGUAGE ='US'

How to attach concurrent program to request group using pl/sql script.

DECLARE

BEGIN

FND_PROGRAM.add_to_group

    (

     PROGRAM_SHORT_NAME  =>'XXHLFUSIONPHLFIINT'

    ,PROGRAM_APPLICATION =>'XXHL'

    ,REQUEST_GROUP       => 'US SHRMS Reports & Processes'

    ,GROUP_APPLICATION   =>'PER'

    ) ;   

  commit;

exception

    when others then

           dbms_output.put_line('Object already exists');

END;

/

Converter number to words in RTF level

<?xdoxslt:toWordsAmt(sum(AMOUNT))?>

<?xdoxslt:toWordsAmt(12345.98)?>


https://blogs.oracle.com/xmlpublisher/entry/numbers_to_words_update

How to add system administrator resposibility from backend

BEGIN
   fnd_user_pkg.addresp ('RAJKUMARMA',
                         '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;

Reset an Oracle Apps User Password via script

DECLARE
      l_ret_val BOOLEAN;
      l_user_name   varchar2(50) := 'RAJKUMARMA';
      l_new_pwd     varchar2(20) := 'oracle120';
BEGIN
      l_ret_val :=
           fnd_user_pkg.changepassword(username=> l_user_name
                                      ,newpassword => l_new_pwd);
     IF l_ret_val
     THEN
           DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  l_new_pwd);
           COMMIT;
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed');
     END IF;
END;

sending Mail s using utl_smtp

CREATE OR REPLACE PROCEDURE common_text_email_prc (
   p_from_mail   IN   VARCHAR2,
   p_to_mail     IN   VARCHAR2,
   p_subject     IN   VARCHAR2,
   p_message     IN   VARCHAR2,
   p_host_name   IN   VARCHAR2
)
IS
   l_mail_conn   UTL_SMTP.connection;
   l_count       NUMBER;

   CURSOR email_cur
   IS
      SELECT LIST.tml emails
        FROM (WITH xhl_email_list_rec AS
                   (SELECT p_to_mail tml
                      FROM DUAL)
              SELECT     REGEXP_SUBSTR (tml, '[^,]+', 1, LEVEL) tml
                    FROM xhl_email_list_rec
              CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (tml, ',', NULL))
                                     FROM xhl_email_list_rec)) LIST
       WHERE LIST.tml IS NOT NULL;
BEGIN
   l_mail_conn := UTL_SMTP.open_connection (p_host_name);
   UTL_SMTP.helo (l_mail_conn, p_host_name);
   UTL_SMTP.mail (l_mail_conn, p_from_mail);

   FOR email_rec IN email_cur
   LOOP
      UTL_SMTP.rcpt (l_mail_conn, email_rec.emails);
   END LOOP;

   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn, 'From: ' || p_from_mail || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || p_to_mail || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, p_message);
   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
   DBMS_OUTPUT.put_line ('Mail sent successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Exception in send_mail'
                            || SUBSTR (SQLERRM, 1, 200)
                           );
END common_text_email_prc;