EBS READ ONLY Form Access

Need to Enable all accounts (including SYSADMIN) are read only as part of DR Testing

Requirement to enable query only for all the application users.
Step 1: Create backup table for fnd_form_functions

Create table fnd_form_functions_bkp_t1 as select * from fnd_form_functions;

Step 2: update parameters as QUERY_ONLY=”YES”

update fnd_form_functions set parameters=’QUERY_ONLY=”YES”‘ where parameters is null and form_id is not null;

DECLARE
f1 number;
p1 VARCHAR2(2000 BYTE);
CURSOR C1 IS
select function_id, PARAMETERS from fnd_form_functions where form_id is not null
and parameters ‘QUERY_ONLY=”YES”‘ AND parameters ‘QUERY_ONLY=YES’
and parameters not like ‘%QUERY_ONLY=”YES”%’ AND parameters not like ‘%QUERY_ONLY=YES%’;
begin
open c1;
loop
fetch c1 into f1,p1;
exit when c1%notfound;
update fnd_form_functions set parameters=p1||’ QUERY_ONLY=”YES”‘
where function_id=f1
;
–commit;
END LOOP;
close c1;
end;
/

commit;

Rollback plan :

Step 1: update parameters to null in fnd_form_functions with backup table

update fnd_form_functions set parameters=null where function_name in (select function_name from fnd_form_functions_bkp_t1 where parameters is null and form_id is not null);

DECLARE
f1 number;
p1 VARCHAR2(2000 BYTE);
CURSOR C1 IS
select function_id, PARAMETERS from fnd_form_functions_bkp_t1 where form_id is not null
and parameters ‘QUERY_ONLY=”YES”‘ AND parameters ‘QUERY_ONLY=YES’
and parameters not like ‘%QUERY_ONLY=”YES”%’ AND parameters not like ‘%QUERY_ONLY=YES%’;
begin
open c1;
loop
fetch c1 into f1,p1;
exit when c1%notfound;
update fnd_form_functions set parameters=p1
where function_id=f1
;
–commit;
END LOOP;
close c1;
end;
/

Commit;