Tuesday, December 17, 2013

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 2


The initial list of unused forms can be identified based on the strategy discussed in Identification section above.
Additionally, unused forms can also be identified by checking the latest time when each form was accessed by a user. Forms which were accessed long back are probably not in use any more and are likely candidates for removal.
Note: The following query will give latest access time for a form only if -
a.     The value for the profile ’Sign-On:Audit Level’ is set to ‘FORM’ and
b.     The built in ‘FND_STANDARD.FORM_INFO’ is called in the ‘PRE-FORM’ trigger with appropriate values for the form.

SELECT ff.form_name,
       MAX(flrf.start_time) last_accessed_time
  FROM fnd_form ff,
       fnd_login_resp_forms flrf      
 WHERE ff.form_id = flrf.form_id (+)
   AND UPPER(ff.form_name) LIKE 'XX%'
 GROUP BY ff.form_name  
 ORDER BY ff.form_name


 Check if any other form is calling the identified form.
Go to $AU_TOP/forms/US
grep –ir <FORM_NAME> *.fmb


Following steps need to be carried out to remove the form.
1) Delete the <FORM_NAME>.fmb file from to $AU_TOP/forms/US
rm –f <FORM_NAME>.fmb

2)Delete the <FORM_NAME>.fmx file from to $CUSTOM_TOP/forms/US
rm –f <FORM_NAME>.fmx

3) Remove the Form Function from the menu
4) Delete the Form Function




The initial list of unused reports can be identified based on the strategy discussed in Identification section above.
Additionally, unused reports can also be identified by checking if the corresponding concurrent programs are disabled.

SELECT fe.execution_file_name,
       fcp.concurrent_program_name
  FROM fnd_executables fe,
       fnd_concurrent_programs fcp
 WHERE fe.execution_method_code = 'P'    -- ‘P’ for Oracle reports
   AND fe.executable_id = fcp.executable_id
   AND fcp.enabled_flag = 'N'
   AND fe.execution_file_name LIKE 'XX%' -- Assuming custom reports are   -- named starting with XX

For each unused report the corresponding executable and concurrent program can be identified. The concurrent program can then be verified as follows.
Check the last time when the report concurrent program was ran.
Note: This data might not give correct usage picture as normally the concurrent program run data is frequently purged.

SELECT *
  FROM fnd_conc_req_summary_v
 WHERE program_short_name LIKE UPPER('%<PROGRAM_SHORT NAME>%')
  AND TRUNC (actual_start_date) < TRUNC (SYSDATE-365)


Following steps need to be carried out to remove the report.
1)Delete the corresponding executable and the concurrent program
BEGIN
  FND_PROGRAM.DELETE_EXECUTABLE ('<EXECUTABLE_SHORT_NAME>', '<APPLICATION_NAME>');

  FND_PROGRAM.DELETE_PROGRAM ('<PROGRAM_SHORT_NAME>', '<APPLICATION_NAME>');
END;

2)Delete the <REPORT_NAME>.rdf file from to $CUSTOM_TOP/reports/US
rm –f <REPORT_NAME>.rdf



No comments:

Post a Comment