Wednesday, April 18, 2018

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)
                       ;

Thursday, April 5, 2018

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;