Suresh : A Blog on Oracle Applications: February 2013

Wednesday, February 27, 2013

Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running

A concurrent program can be canceled either from "Submit Request Submission" form or from database side also. In case of custom concurrent programs, sometimes concurrent program do not release database session and process even though it has canceled from "Submit Request Submission" form. Active database process can be seen in running status. In those cases there is a need to manually kill that process to release CPU memory.

Take the "request_id" of a Concurrent Program which is currently running or which is to be canceled from the database side.
Connect to SQLPLUS as APPS User :

SQL> SELECT ses.sid,
            ses.serial#
       FROM v$session ses,
            v$process pro
           WHERE ses.paddr = pro.addr
                AND pro.spid IN (SELECT oracle_process_id
                                   FROM fnd_concurrent_requests
                                  WHERE request_id = &request_id);

Note: oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. When canceling a request from the "Submit Request Submission" form, then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been canceled and then it will kill database process. So ideally when canceling a request from "Submit Request Submission", wait for some time and then check associated database process.

3. Connect to SQLPLUS as the SYSTEM user:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Note the "sid" and "serial#" value returned from step 1.

Tuesday, February 12, 2013

How to use Dynamic Cursor in a PL/SQL

PL/SQL script to use a dynamic cursor.

CREATE OR REPLACE PROCEDURE XXX_dynamic_cursor (p_segment IN VARCHAR2)
IS
v_sql VARCHAR2 (2000) := NULL;
v_segment VARCHAR2 (50);
l_segment_desc VARCHAR2 (100);

TYPE cur_typ IS REF CURSOR;

c_seg_cur cur_typ;
BEGIN
v_sql :=
'SELECT distinct ss.'
|| p_segment
|| ' , ffvl.description
FROM XXXX_TEMP_GL ss,
fnd_id_flex_segments fifs,
fnd_flex_value_sets ffvs,
fnd_flex_values ffv,
fnd_flex_values_tl ffvl
WHERE ss.chart_of_accounts_id = fifs.id_flex_num
AND fifs.application_column_name = :p_segment AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvl.flex_value_id
AND ss.'
|| p_segment
|| ' = ffv.flex_value
order by 1';

OPEN c_seg_cur FOR v_sql USING p_segment;

LOOP
FETCH c_seg_cur
INTO v_segment, l_segment_desc;

EXIT WHEN c_seg_cur%NOTFOUND;
DBMS_OUTPUT.put_line (v_segment || l_segment_desc);
-- process row here
END LOOP;

CLOSE c_seg_cur;
END;
/

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