End Date a Responsibility for Users
If we need to end date a responsibility for a user then we can use the below syntax.
Syntax:
DECLARE
p_user_name VARCHAR2 (50);
p_resp_name VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;
BEGIN
BEGIN
SELECT application_id, responsibility_id
INTO v_application_id, v_responsibility_id
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.
put_line ('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;
/*DBMS_OUTPUT.
put_line (
'Responsibility ID'
|| v_responsibility_id
|| 'and'
|| 'Application ID'
|| v_application_id);*/
FOR u
IN (SELECT fu.user_id, furg.start_date,furg.description
FROM fnd_user_resp_groups_direct furg,
fnd_user fu,
fnd_responsibility_tl fr
WHERE fr.responsibility_name = p_resp_name
AND furg.user_id = fu.user_id
AND fu.user_name <> 'SYSADMIN'
AND furg.responsibility_id = fr.responsibility_id
AND fr.language = USERENV ('LANG'))
LOOP
v_user_id := u.user_id;
/*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/
BEGIN
DBMS_OUTPUT.put_line ('Initializing The Application');
fnd_global.
apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id);
DBMS_OUTPUT.
put_line (
'Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');
fnd_user_resp_groups_api.
update_assignment (
user_id => v_user_id,
responsibility_id => v_responsibility_id,
responsibility_application_id => v_application_id,
security_group_id => 0,
start_date => u.start_date,
end_date => TRUNC (SYSDATE) - 1,
description => u.description);
DBMS_OUTPUT.
put_line ('The End Date has been set for responsibility');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END LOOP;
END;
Syntax:
DECLARE
p_user_name VARCHAR2 (50);
p_resp_name VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;
BEGIN
BEGIN
SELECT application_id, responsibility_id
INTO v_application_id, v_responsibility_id
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.
put_line ('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;
/*DBMS_OUTPUT.
put_line (
'Responsibility ID'
|| v_responsibility_id
|| 'and'
|| 'Application ID'
|| v_application_id);*/
FOR u
IN (SELECT fu.user_id, furg.start_date,furg.description
FROM fnd_user_resp_groups_direct furg,
fnd_user fu,
fnd_responsibility_tl fr
WHERE fr.responsibility_name = p_resp_name
AND furg.user_id = fu.user_id
AND fu.user_name <> 'SYSADMIN'
AND furg.responsibility_id = fr.responsibility_id
AND fr.language = USERENV ('LANG'))
LOOP
v_user_id := u.user_id;
/*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/
BEGIN
DBMS_OUTPUT.put_line ('Initializing The Application');
fnd_global.
apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id);
DBMS_OUTPUT.
put_line (
'Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');
fnd_user_resp_groups_api.
update_assignment (
user_id => v_user_id,
responsibility_id => v_responsibility_id,
responsibility_application_id => v_application_id,
security_group_id => 0,
start_date => u.start_date,
end_date => TRUNC (SYSDATE) - 1,
description => u.description);
DBMS_OUTPUT.
put_line ('The End Date has been set for responsibility');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END LOOP;
END;
Finding Patch Level of a Product in Oracle Application
If we need to find the latest patch level for a particular product then we can use the below query.
Syntax:
select patch_level
from fnd_product_installations where patch_level like upper('%&product_name%')
Output:
PATCH_LEVEL
------------------------------
11i.AD.I.7
Syntax:
select patch_level
from fnd_product_installations where patch_level like upper('%&product_name%')
Output:
PATCH_LEVEL
------------------------------
11i.AD.I.7
APP-FND-01926 - The custom event WHEN-LOGON-CHANGED raised unhandled exception. ORA-06508 PL/SQL could not find program unit being called
This error occurs while opening Forms in Oracle Applications.
Reason:This issue accurs mostly after Upgrade/Clone of applications.The invalid objects in Custom.pll, form throws an error when opened.
Solution:Compile Custom.pll using the below command to generate Custom.plx. Always make sure to backup the previous CUSTOM.plxReason:This issue accurs mostly after Upgrade/Clone of applications.The invalid objects in Custom.pll, form throws an error when opened.
Syntax:
f60gen module=$AU_TOP/resource/CUSTOM.pll userid=APPS/APPS output_file=$AU_TOP/resource/CUSTOM.plx module_type=library batch=no compile_all=special
Finding Concurrent Program Past Execution Details
If we need to find the Concurrent Program previous\past execution details then the below query will be useful.
Script:
select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start date",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started date",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed date",
substr(ARGUMENT_TEXT,1,100) Parameter_Passed ,
TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
24
)
) "Hr",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60,
60
)
) "Mins",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60
* 60,
60
)
) "Sec"
from fnd_concurrent_requests cr where concurrent_program_id in
( select concurrent_program_id
from fnd_concurrent_programs
where concurrent_program_name = '&prog_short_name' )
and actual_start_date>sysdate - &noofdaysago
order by phase_code,actual_start_date asc
Script:
select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start date",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started date",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed date",
substr(ARGUMENT_TEXT,1,100) Parameter_Passed ,
TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
24
)
) "Hr",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60,
60
)
) "Mins",
TRUNC (MOD ( (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
* 24
* 60
* 60,
60
)
) "Sec"
from fnd_concurrent_requests cr where concurrent_program_id in
( select concurrent_program_id
from fnd_concurrent_programs
where concurrent_program_name = '&prog_short_name' )
and actual_start_date>sysdate - &noofdaysago
order by phase_code,actual_start_date asc
Finding the Blocking Session at Database
The below query can be used to find the blocking sessions at the database level.
Query
SELECT (SELECT username
FROM v$session
WHERE sid = a.sid)
blocker,
a.sid,
' is blocking ' "IS BLOCKING",
(SELECT username
FROM v$session
WHERE sid = b.sid)
blockee,
b.sid,
a.TYPE "WITH LOCK TYPE"
FROM v$lock a, v$lock b
WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2
Query
SELECT (SELECT username
FROM v$session
WHERE sid = a.sid)
blocker,
a.sid,
' is blocking ' "IS BLOCKING",
(SELECT username
FROM v$session
WHERE sid = b.sid)
blockee,
b.sid,
a.TYPE "WITH LOCK TYPE"
FROM v$lock a, v$lock b
WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2
Viewing Request Output and Log Files in HTML/Browser
If the ouput and log files of the concurrent needs to be viewed in Browser, then we can perform the below action item.
1. Goto System Administrator responsibility and select Profile>System
2. Search for the 'Viewer:%' profile.
3. Set the profile Viewer: Application for Text to Browser. If this has to be set for all users then it has to be set at Site Level or for a particular user we can set it at user level.
1. Goto System Administrator responsibility and select Profile>System
2. Search for the 'Viewer:%' profile.
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old
If we are facing the issue ORA-20002, then we can perform the below work around.This basically occurs if any Database is upgraded.
Issue:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old
Solution:
1. Connect to sqlplus / as sysdba
2. Take a Backup of FND_STATTAB table
3. Run EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,'FND_STATTAB’);
Issue:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old
Solution:
1. Connect to sqlplus / as sysdba
2. Take a Backup of FND_STATTAB table
3. Run EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,'FND_STATTAB’);
Oracle HTTP Server MLR Patchset
There might be requirement from Oracle to check the HTTP server level MLR Patch Set, we can use the below information to get the deatils.
RUN command "$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v"
httpd -v
Output:Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2006 17:56:59 (iAS 1.0.2.2.2 rollup 5)
The meaning of the various rollup are as below
Oracle HTTP Server Patch List
RUN command "$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v"
httpd -v
Output:Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2006 17:56:59 (iAS 1.0.2.2.2 rollup 5)
The meaning of the various rollup are as below
Oracle HTTP Server Patch List
Release
| ||||
1.0.2.2.2
|
Rollup 5
|
None
| ||
1.0.2.2.2
|
Rollup 4
|
11.5.10
| ||
1.0.2.2.2
|
Rollup 3
|
11.5.9
| ||
1.0.2.2.2
|
Rollup 2
|
11.5.8
| ||
1.0.2.2.2
|
Rollup 1
|
11.5.7
| ||
1.0.2.1S
|
Rollup 1
|
None
|
Tables which are purged with FND_CONC_CLONE.SETUP_CLEAN
When EXEC FND_CONC_CLONE.SETUP_CLEAN is executed various tables are purged.
To know which tables are purged/cleaned we can view $FND_TOP/patch/115/sql/AFCPCLNB.pls.
To know which tables are purged/cleaned we can view $FND_TOP/patch/115/sql/AFCPCLNB.pls.
Finding the Languages Installed in Oracle Applications
The below query can be used for the finding the languages installed in Oracle Application.
SELECT Language_code, NLS_language, Installed_flag
FROM fnd_languages
WHERE installed_flag IN ('I', 'B')
FROM fnd_languages
WHERE installed_flag IN ('I', 'B')
The Installed_Flag has 3 values
I- Installed
B- Base
D- Disabled
Subscribe to:
Posts
(
Atom
)
Posts
1 comment :
Post a Comment