SQL Query to find Request Set for the given concurrent program

SQL Query to find  Request Set  for the given concurrent program :

SELECT

   DISTINCT  user_request_set_name

FROM

    fnd_request_sets_tl rs,

    fnd_request_set_programs rsp,

    fnd_concurrent_programs_tl cp

WHERE

    rsp.concurrent_program_id = cp.concurrent_program_id

    AND   rs.request_set_id = rsp.request_set_id

    AND   upper(user_concurrent_program_name) = upper(:enter_prog_name)

                       ;

Script to Change Password of user from Backend In Oracle Applications Using API

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER('SGUDURU');

  v_new_password VARCHAR2(30):= 'welcome1';

  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 list Active Responsibilities of a Active User In Oracle Applications

SELECT fu.user_name,

       frv.responsibility_name,

       frv.responsibility_key,

       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",

       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"

FROM fnd_user fu,

  fnd_user_resp_groups_direct furgd,

  fnd_responsibility_vl frv

WHERE fu.user_id                     = furgd.user_id

AND furgd.responsibility_id          = frv.responsibility_id

AND furgd.end_date                  IS NULL

AND fu.user_name                     = '&user_name'

AND furgd.start_date                <= sysdate

AND NVL(furgd.end_date, sysdate + 1) > sysdate

AND fu.start_date                   <= sysdate

AND NVL(fu.end_date, sysdate + 1)    > sysdate

AND frv.start_date                  <= sysdate

AND NVL(frv.end_date, sysdate + 1)   > sysdate;

How to get table to index structure like toad

Here is the built in which will give you the table or index structure .

How to get tables / Index structure.
SELECT to_char(DBMS_METADATA.GET_DDL ('INDEX', index_name,index_owner))
FROM dba_indexes
WHERE table_owner=upper('APPS');

SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', TABLE_name, table_owner))
FROM dba_TABLES
WHERE table_owner=upper('ONT');

PL/SQL Script to Add System Administrator in Oracle Applications

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

Pl/SQL Script to Create user in Oracle applications

DECLARE
  v_user_name  VARCHAR2(30):=UPPER('SGUDURU');
  v_password   VARCHAR2(30):='ORACLE123';
  v_session_id INTEGER     := USERENV('sessionid');
BEGIN
  fnd_user_pkg.createuser (
    x_user_name => v_user_name,
    x_owner => NULL,
    x_unencrypted_password => v_password,
    x_session_number => v_session_id,
    x_start_date => SYSDATE,
    x_end_date => NULL
  );
  COMMIT;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
END;

How to rebuild a partitioned index

alter index my_ind1 rebuild partition P_1 ;

How to verify the xml template file

select * from apps.xdo_templates_vl  where template_name like '%Service Contract%'

How to find the oracle workflow version using sql query

select * from   WF_RESOURCES where  NAME = 'WF_VERSION' and language = 'US'