Retrieve conc request id and database sid from os pid with the following query






select request_id, p.spid,s.* from apps.fnd_concurrent_requests r , v$process p , v$session s
where r.request_id =
(select request_id from fnd_concurrent_requests fcr
where fcr.oracle_process_id=&OS_PID
and trunc(fcr.request_date)=trunc(sysdate))
and p.SPID = r.ORACLE_PROCESS_ID
and p.ADDR = s.PADDR;




You are welcome to leave a comment....

Scripts for Workflow notification mailer 

Workflow: To see failed, open notifications
===============================================
SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
GROUP BY message_type;

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications

WHERE STATUS='OPEN'

AND mail_status = 'MAIL'

GROUP BY message_name;

Sql> SELECT * FROM wf_notifications

WHERE STATUS='OPEN'

AND mail_status = 'SENT'

ORDER BY begin_date DESC

You are welcome to leave a comment....

Script to get all Concurrent Requests pending with CRM 

select
request_id reqid,
to_char(req.requested_start_date, 'DD-Mon HH24:MI:SS') rsdate,
decode(status_code,'Q', 'Standby', 'F', 'Scheduled', 'T', 'Terminating', 'I', 'Normal', 'R', 'Normal', 'C', 'Normal', 'Other') status_code,
user_name,
req.concurrent_queue_name conc_que,
user_concurrent_program_name pname2
from
fnd_concurrent_worker_requests req,
fnd_concurrent_queues que,
fnd_user usr
where
phase_code = 'P'
and
que.concurrent_queue_name = 'Conflict_Resolution_Manager'
and
que.application_id= req.queue_application_id
and
que.concurrent_queue_id = req.concurrent_queue_id
and
req.requested_by = usr.user_id
and
requested_start_date <= sysdate
and
concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by request_id, requested_start_date ;

  


You are welcome to leave a comment....