Useful SQL queries for support

Useful SQL queries for support

1. Delete a Concurrent Program from back end.


BEGIN
   apps.fnd_program.remove_from_group (
      program_short_name    => 'CONCURRENT_PROGRAM_SHORT_NAME',
      program_application   => 'PROGRAM_APPLICATION_SHORT_NAME',
      request_group         => 'REQUEST_GROUP_NAME',
      group_application     => 'REQUEST_GROUP_APPICATION');
   fnd_program.delete_program ('CONCURRENT_PROGRAM_SHORT_NAME', 'PROGRAM_APPLICATION_SHORT_NAME');
   fnd_program.delete_executable ('EXECUTABLE_SHORT_NAME', 'EXECUTABLE_APPLICATION_SHORT_NAME');
   COMMIT;
END;


2. To get the Concurrent requests details :


SELECT PARENT_REQUEST_ID, request_id, PHASE_CODE, STATUS_CODE, ARGUMENT_TEXT, req.logfile_name,req.outfile_name,
         ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, RESP.RESPONSIBILITY_NAME, PGM.CONCURRENT_PROGRAM_NAME, FUS.USER_NAME
FROM APPS.FND_CONCURRENT_REQUESTS REQ, APPS.FND_CONCURRENT_PROGRAMS PGM, APPS.FND_RESPONSIBILITY_VL RESP, APPS.FND_USER FUS
WHERE REQ.CONCURRENT_PROGRAM_ID = PGM.CONCURRENT_PROGRAM_ID
AND PGM.APPLICATION_ID = REQ.PROGRAM_APPLICATION_ID
AND REQ.RESPONSIBILITY_ID = RESP.RESPONSIBILITY_ID
AND REQ.REQUESTED_BY = FUS.USER_ID
--AND PGM.CONCURRENT_PROGRAM_NAME = 'CONCURRENT_PROGRAM_SHORT_NAME'
--and req.request_id = REQUEST_ID
and FUS.USER_NAME = 'XXXXX'
order by req.request_id desc

3. Add Responsiblity to a User from backend :


BEGIN
   fnd_user_pkg.addresp (username         => 'USER_NAME',
                         resp_app         => 'RESPONSIBLITY_APP_SHORT_NAME',
                         resp_key         => 'RESPONSIBILITY_KEY',
                         security_group   => 'STANDARD',
                         description      => 'Auto Assignment',
                         start_date       => SYSDATE - 10,
                         end_date         => SYSDATE + 1000);
   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;

4. Active Users with Active responsiblities:


SELECT fu.user_name,
       frv.responsibility_name,
       TO_CHAR (furgd.start_date, 'DD-MON-RRRR') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-RRRR') "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
       AND FRV.RESPONSIBILITY_NAME = 'Application Developer'
       ORDER BY FRV.RESPONSIBILITY_NAME ;

5.Request Group of a Concurrent Program :


SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = '&CP_EXEC_NAME';