Tuesday, October 29, 2013

How to find the long running sqls

select sid,
       opname,
       target,
       sofar,
       totalwork,
       units,
       (totalwork-sofar)/time_remaining bps,
       time_remaining,
       sofar/totalwork*100 fertig
from   v$session_longops
where  time_remaining > 0

Monday, October 28, 2013

Workflow lookup table and it is details

The below table contains the look up types related to workflow
select * from WF_LOOKUP_TYPES_TL

The below table will have the meaning of each lookup type. I gave one example query below.

select * from WF_LOOKUPS_TL where lookup_type like 'WFSTD_DAY_OF_MONTH%' and language ='US'

Monday, October 21, 2013

How to find the processing constraints and related to templates from back end.

select OEPCC.CONSTRAINT_ID
, OEPCC.CONDITION_ID
, OEPCC.last_update_date
, OEPCCT.USER_MESSAGE
, OEPCC.VALIDATION_TMPLT_ID
, OEVT.ENTITY_ID
, OEVT.VALIDATION_TMPLT_SHORT_NAME
, OEVT.VALIDATION_TYPE
, OEVT.SYSTEM_FLAG
, OEVT.ACTIVITY_NAME
, OEVT.ACTIVITY_STATUS_CODE
, OEVT.ACTIVITY_RESULT_CODE
, OEVT.WF_ITEM_TYPE
from OE_PC_CONDITIONS OEPCC
, OE_PC_CONDITIONS_TL OEPCCT
, OE_PC_VTMPLTS OEVT
where OEPCC.CONSTRAINT_ID = &constraint_id
and OEPCCT.CONDITION_ID = OEPCC.CONDITION_ID
and (OEVT.VALIDATION_TMPLT_ID = OEPCC.VALIDATION_TMPLT_ID
and OEPCCT.language='US'    );

--

select OEPCC.CONSTRAINT_ID
, OEPCC.CONDITION_ID
, OEPCC.last_update_date
, OEPCCT.USER_MESSAGE
, OEPCC.VALIDATION_TMPLT_ID
, OEVTC.COLUMN_NAME
, OEVTC.VALIDATION_OP
, OEVTC.VALUE_STRING
from OE_PC_CONDITIONS OEPCC
, OE_PC_CONDITIONS_TL OEPCCT
, OE_PC_VTMPLT_COLS OEVTC
where OEPCC.CONSTRAINT_ID = &constraint_id
and OEPCCT.CONDITION_ID = OEPCC.CONDITION_ID
and (OEVTC.VALIDATION_TMPLT_ID = OEPCC.VALIDATION_TMPLT_ID
and OEPCCT.language='US' );

How to know the concurrent program is assigned to which concurrent manager

SELECT a.include_flag, a.queue_application_id , c.user_concurrent_queue_name,
       d.user_concurrent_program_name
  FROM applsys.fnd_concurrent_queue_content a,
       applsys.fnd_concurrent_programs b,
       apps.fnd_concurrent_queues_vl c,
       fnd_concurrent_programs_tl d
 WHERE type_id = b.concurrent_program_id
  AND c.concurrent_queue_id = a.concurrent_queue_id
   AND b.concurrent_program_id = d.concurrent_program_id
   AND d.user_concurrent_program_name LIKE '%Lines on Credit Check Hold Report%'

Monday, October 7, 2013

How to find the sid from concurrent request id

The below sql retrieve the sid information for the running concurrent program.

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
AND a.phase_code = 'R';

Also you can use the below sql to find out which sql is running for the above concurrent request id.

select a.sid, a.serial#, b.sql_text
   from v$session a, v$sqlarea b
    where a.sql_address=b.address
     and a.sid = &sid