www.stiffmaster.nl

Listing some Oracle (eBS) scripts here.
Some syntaxes that I keep forgetting and some statements that are too long to type often.

Generate compile invalids script:
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||owner||'.'||object_name||' compile '||decode(object_type,'PACKAGE BODY','BODY')||';'
from dba_objects where status='INVALID' ;

Find concurrent request / program:
select cr.request_id, cpt.user_concurrent_program_name, cr.phase_code, cr.status_code,
cr.requested_start_date, cr.actual_start_date, cr.actual_completion_date, cr.argument_text
from fnd_concurrent_requests cr
join fnd_concurrent_programs_tl cpt on cr.concurrent_program_id=cpt.concurrent_program_id
where (cr.request_id =to_number('&req_id') or '&req_id' is null)
and (cpt.user_concurrent_program_name like '%&name%' or '&name' is null)
and cpt.language=USERENV('LANG');

Resubmit IB Interface errors:
UPDATE csi_txn_errors
SET processed_flag = 'R'
WHERE processed_flag='E'
and source_id in (select line_id from oe_order_lines_all where header_id=&header_id);

declare
v_err varchar2(200);
v_ret number;
begin
CSI_RESUBMIT_PUB.Resubmit_Interface(errbuf => v_err
,retcode => v_ret
,p_option => 'SELECTED'
);
end;

Check last_analyzed on partitioned tables
select dt.table_name,dt.last_analyzed,dp.partition_name,dp.last_analyzed,dsp.subpartition_name,dsp.last_analyzed
From dba_tables dt
left outer join dba_tab_partitions dp on (dt.table_name=dp.table_name)
left outer join dba_tab_subpartitions dsp on (dt.table_name=dsp.table_name and dp.partition_name=dsp.partition_name)
where dt.table_name='WF_ITEMS'

Find Workflow notification in WF_NOTIFICATION_OUT
select notification_id,msg_state,msg_id,role,corrid,enq_time,deq_time
from (select msg_id, o.enq_time, o.deq_time, msg_state
,(select str_value
from table (o.user_data.header.properties)
where name = 'NOTIFICATION_ID') notification_id
, (select str_value
from table (o.user_data.header.properties)
where name = 'ROLE') role
, (select str_value
from table (o.user_data.header.properties)
where name = 'Q_CORRELATION_ID') corrid
from applsys.aq$wf_notification_out o)
where notification_id=[notification_id]

Find a notification (for denormalize or send) in WF_DEFERRED
select v.msg_id,v.msg_state,v.enq_time,v.deq_time,v.corr_id
from applsys.aq$wf_deferred v
, table(v.user_data.parameter_list) t
where v.corr_id like 'APPS:oracle.apps.wf.notification.%'
and name ='NOTIFICATION_ID'
and value = [Notification_id]

Report on requests that run longer than twice the average in the last 7 days
select user_concurrent_program_name "Concurrent Program"
, cnt "Number over average"
, av "Average runtime"
, mx "Max runtime"
, actual_start_date "Start date longest"
from (select req.concurrent_program_id
, fcpt.user_concurrent_program_name
, sum(cnt) over (partition by req.concurrent_program_id) cnt
, req.av
, req.mx
, req.actual_start_date
, rn
from (select concurrent_program_id
, program_application_id
, round(avg(ela_min) over (partition by concurrent_program_id),2) av
, round(max(ela_min) over (partition by concurrent_program_id),2) mx
, row_number() over (partition by concurrent_program_id order by ela_act desc) rn
, max(ela_act) over (partition by concurrent_program_id) mx_act
, case when ela_min>avg(ela_min) over (partition by concurrent_program_id)*2 then 1 else 0 end cnt
, actual_start_date
, actual_completion_date
, request_id
, ela_min
, ela_act
from (select concurrent_program_id
, program_application_id
, request_id
, actual_start_date
, actual_completion_date
, round((actual_completion_date-actual_start_date)*24*60,2) ela_min
, actual_completion_date-actual_start_date ela_act
from fnd_concurrent_requests)
where actual_completion_date>=trunc(sysdate)-7) req
join fnd_concurrent_programs_tl fcpt on fcpt.concurrent_program_id=req.concurrent_program_id and fcpt.application_id=req.program_application_id
where cnt=1
and fcpt.language='US')
where rn=1
order by mx-av desc;