Query 1 – List of Oracle Forms Last Modified during the period under review
SELECT a.form_name "Form Name",
b.user_form_name "User From Name",b.description,
a.creation_date "Creation Date",
a.last_update_date "Last Update Date",
a.LAST_UPDATED_BY
FROM fnd_form a, fnd_form_tl b
WHERE a.form_id = b.form_id
AND TO_DATE (a.last_update_date) >= TO_DATE ('01-Apr-2023')
ORDER BY TRUNC (a.last_update_date);
Query 2 – List of Oracle Concurrent Programs Last Modified during the period under review
select a.concurrent_program_name,b.user_concurrent_program_name,b.description, a.execution_method_code,
a.creation_date,a.last_update_date, a.LAST_UPDATED_BY
from fnd_concurrent_programs a, fnd_concurrent_programs_tl b
where a.concurrent_program_id = b.concurrent_program_id
and to_date(a.last_update_date)>= ('01-Apr-2023')
order by trunc(a.last_update_date);
Query 3 - List of all profile option changes :
SELECT pro.profile_option_id,
SUBSTR(pro.profile_option_name,1,35) Profile,
pov.level_id,pov.level_value,
DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,
DECODE(pov.level_id, 10001,'Site') Level_Value,
NVL(pov.profile_option_value,'Is Null') Profile_option_Value,
to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,
(select user_name from FND_USER where user_id=pov.created_by) created_by,
(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,
to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date
From FND_PROFILE_OPTION_VALUES pov,
FND_PROFILE_OPTIONS pro
Where 1=1
AND pro.profile_option_id = pov.profile_option_id
AND pov.level_id=10001
UNION
SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,
DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,
DECODE(pov.level_id,10002,appl.application_short_name) Level_Value,
NVL(pov.profile_option_value,'Is Null') Profile_option_Value,
to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,
(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date
FROM FND_PROFILE_OPTION_VALUES pov,
FND_PROFILE_OPTIONS pro,
FND_APPLICATION appl
Where 1=1
AND pro.profile_option_id = pov.profile_option_id
AND pov.level_id=10002
AND pov.level_value = appl.application_id
UNION
SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,
DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,
DECODE(pov.level_id, 10003,resp.responsibility_name) Level_Value,
NVL(pov.profile_option_value,'Is Null') Profile_option_Value, to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,
(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date
FROM FND_PROFILE_OPTION_VALUES pov,
FND_RESPONSIBILITY_TL resp,
FND_PROFILE_OPTIONS pro
WHERE 1=1
AND pro.profile_option_id = pov.profile_option_id
AND pov.level_value = resp.responsibility_id
AND pov.level_id=10003
UNION
SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,
DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,
DECODE(pov.level_id, 10004,u.user_name) Level_Value,
NVL(pov.profile_option_value,'Is Null') Profile_option_Value,to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,
(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date
FROM FND_PROFILE_OPTION_VALUES pov,
FND_PROFILE_OPTIONS pro,
FND_USER u
WHERE 1=1
AND pro.profile_option_id = pov.profile_option_id
AND pov.level_value = u.user_id
AND pov.level_id=10004
Order BY 1,2;
Query 4 - System generated list of all patch changes made to the Oracle Oracle ERP during the audit period.
SELECT (SELECT name FROM v$database) SID,
AP.PATCH_NAME,
AP.PATCH_TYPE,
AD.DRIVER_FILE_NAME,
TO_CHAR (AD.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE,
AP.LAST_UPDATE_DATE,
AP.LAST_UPDATED_BY,
AD.PLATFORM,
AL.LANGUAGE
FROM AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
WHERE AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
AND TO_DATE (AP.LAST_UPDATE_DATE) >= TO_DATE ('01-Apr-2023')
AND TO_DATE (AP.LAST_UPDATE_DATE) <= TO_DATE ('31-Mar-2024')
AND AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
ORDER BY AP.LAST_UPDATE_DATE DESC;