Active or Recently Used Printers in Oracle EBS

Recently had a request from one of the admins for list of printers used in the EBS application and how many jobs are directed to a printer. The following SQL provided what the admin needed, thought a good one to share...

select distinct fcr.printer, fu.user_name, 

       fu.description, count(*) jobs

from apps.fnd_concurrent_requests fcr, apps.fnd_user fu

where fcr.requested_by = fu.user_id

and (fcr.printer is not null and fcr.printer not in  ('noprint','LOCAL_PRINT','LOCAL','LOCAL_PRINTTO', 'LOCAL_PREVIEW'))

group by fcr.printer, fu.user_name, fu.description;

The caveat is that if you purge concurrent requests, the information is dependent on the records available in concurrent requests table...

Hope this helps someone, or if there is a better way to find the printer usage, please let me know... have a great day!

Oracle EBS Housekeeping Jobs

For a long time, I searched for a comprehensive list of housekeeping jobs [Standard EBS jobs provided by Oracle] that should be scheduled periodically on the EBS instance. Since I could not find a single source, came up with my own after a lot of reading... There may be more, but these should be scheduled on the EBS to keep the instance performing healthy... The arguments and run interval may need to be modified to suit the workload and business requirement... but this will get you started... Few jobs may need multiple scheduling with different parameters...

Job       : Gather Schema Statistics 
Executable: FNDGSCST 
Frequency : Daily 
Parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER AUTO, , N 
Frequency : Weekly 
Parameters: ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER, , Y  


Job       : JTF Item InterMedia Index Optimizing operation
Executable: JTFOPTI 
Frequency : Weekly 
Parameters: FULL, 2 


Job       : JTF Item InterMedia Index Sync Operation
Executable: JTFSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : MES InterMedia Index Sync Operation
Executable: AMVSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : OCM InterMedia Index Synchronizing for Attribute Bundles 
Executable: IBCSYNCATTR 
Frequency : Weekly 
Parameters: (None) 


Job       : Purge Debug Log and System Alerts FNDLGPRG 
Frequency : Weekly 
Parameters: (Date - 30 days)


Job       : Purge FND_STATS History Records 
Executable: FNDPGHST 
Frequency : Weekly 
Parameters: DATE, 01-Jan-00, 30-JUN-11 


Job       : Purge Obsolete Generic File Manager Data 
Executable: FNDGFMPR Daily 
Parameters: Y, , 


Job       : Purge Obsolete Workflow Runtime Data FNDWFPR 
Frequency : Weekly
Parameters: , , 60, PERM, N, 500, N Weekly , , 60, TEMP, N, 500, N 


Job       : Purge Signon Audit data FNDSCPRG 
Frequency : Weekly 
Parameters: (Date - 60 days)


Job       : Workflow Agent Activity Statistics Concurrent Program  
Executable: FNDWFAASTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Background Process 
Executable: FNDWFBG 
Frequency : 12 HOURS 
Parameters: , , , N, N, Y 
Frequency : 1 HOUR
Parameters: , , , N, Y, N 
Frequency : 5 MINUTES 
Parameters: , , , Y, N, N 


Job       : Workflow Control Queue Cleanup 
Executable: FNDWFBES_CONTROL_QUEUE_CLEANUP 
Frequency :  12 HOURS 
Parameters: (None) 


Job       : Workflow Directory Services User/Role Validation
Executable: FNDWFDSURV 
Frequency : Daily 
Parameters: 10000, Y, Y, Y 


Job       : Workflow Mailer Statistics Concurrent Program
Executable: FNDWFMLRSTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Work Items Statistics Concurrent Program
Executable: FNDWFWITSTATCC 
Frequency : Daily 
Parameters: (None)  


Enjoy!

Script to Refresh Materialized Views

The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month...

REM Uncomment below line if EBS database

REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare

cursor mv2ref is select owner ||'.'||  mview_name mview

       from dba_mviews

       where last_refresh_date between sysdate -30 and sysdate -1

       and fast_refreshable = 'DML';

dummy pls_integer;

begin

REM Uncomment below line if EBS database

REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop

begin

    dbms_mview.refresh(rmv2ref.mview, 'F');

    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception

when others then

    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));

    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;

/

Enjoy!