Cloned Date, Version Details, URL of Oracle Applications,Cloned date of oracle application and Version



Query to get Version details..
++++++++++++++++++++++++++++++++++++

SELECT product, VERSION, status
FROM product_component_version;


Query to  get cloned date of an oracle instance
+++++++++++++++++++++++++++++++++++++++++

SELECT resetlogs_time FROM v$database;

========================

Query to get the front end URL from backend
++++++++++++++++++++++++++++++++++
SELECT home_url FROM icx_parameters;

How to remove End date of specific responsibility for specific user in oracle apps R12

If you want to remove end date for particular user say user ABC and want to have access to that responsibility then run below script in back-end(plsql window) to get access to required responsibility :

{**Note-you need to have APPS access to run below script}

DECLARE

p_user_name VARCHAR2 (50) := 'ABC';
p_resp_name VARCHAR2 (50) := 'Responsibility_Name';
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;

BEGIN

BEGIN
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE UPPER (user_name) = UPPER (p_user_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('User not found');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding User.');
RAISE;
END;

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;

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 => SYSDATE,
end_date => NULL,
description => NULL
);

DBMS_OUTPUT.put_line
('‘The End Date has been removed from responsibility');
COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('‘Error calling the API’');
RAISE;
END;
END;

Workflow Email Override oracle apps R12


If you are testing with for alert.Sending email to required user then you need to enter your email in workflow mailer then and then only you will get email.Also just updating and changing email address wont work until and unless you run below script from backed and commit the changes.

--workflow email override
 BEGIN
FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW
( x_component_name => 'Workflow Notification Mailer',
x_parameter_name => 'TEST_ADDRESS',
x_parameter_value => 'User_Email_id',
x_customization_level => 'L',
x_object_version_number => -1,
x_owner => 'user_name'
);
END;

COMMIT;