List of Running Requests

List of Running Requests

set lines 130

set pages 200

col os form A7 head AppProc

col spid form a6 head SPID

col program form A50 trunc

set pages 38

col time form 99999.99 head Elapsed

col "Req Id" form 99999999

col "Parent" form a8

col "Prg Id" form 9999999

col serial# form 999999 head Serial#

col qname head "Manager" format a10 trunc

col sid format 9999 head SID

col user_name form A12 head User trunc

set recsep off

select

       q.concurrent_queue_name qname

      ,f.user_name

      ,a.request_id "Req Id"

      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"

      ,a.concurrent_program_id "Prg Id"

      ,a.phase_code,a.status_code

--      ,b.os_process_id "OS"

--      ,vs.sid

--      ,vs.serial# "Serial#"

--      ,vp.spid

      ,a.oracle_process_id "spid"

      ,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"

      ,c.concurrent_program_name||' - '||

       c2.user_concurrent_program_name||' '||a.description "Program"

from APPLSYS.fnd_Concurrent_requests a

    ,APPLSYS.fnd_concurrent_processes b

    ,applsys.fnd_concurrent_queues q

    ,APPLSYS.fnd_concurrent_programs_tl c2

    ,APPLSYS.fnd_concurrent_programs c

    ,APPLSYS.fnd_user f

--    ,v$session vs

--    ,v$process vp

where

      a.controlling_manager = b.concurrent_process_id

  and a.concurrent_program_id = c.concurrent_program_id

  and a.program_application_id = c.application_id

  and c2.concurrent_program_id = c.concurrent_program_id

  and c2.application_id = c.application_id

  and a.phase_code in ('I','P','R','T')

  and a.requested_by = f.user_id

  and b.queue_application_id = q.application_id

  and b.concurrent_queue_id = q.concurrent_queue_id

  and c2.language = 'US'

--  and vs.process (+) = b.os_process_id

--  and vs.paddr = vp.addr (+)

order by 9 desc;

List of pending Jobs waiting for managers

List of pending Jobs waiting for managers

set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID 
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;

col program_description format a60
col user_concurrent_queue_name format a40

LIST E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES

LIST E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name)
like upper('%&user_profile_name%'))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;

R12 Finding Trace File of a Concurrent Program

Finding Trace File of a Concurrent Program

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

R12 Finding SID of a Concurrent Request

Finding SID of a Concurrent Request

select b.sid, oracle_session_id, oracle_process_id, os_process_id

from fnd_concurrent_requests a ,

v$session b

 where a.request_id=&request_id and

        a.ORACLE_SESSION_ID = b.AUDSID

R12 FIND PRODUCTS INSTALLED

FIND PRODUCTS INSTALLED

select a.oracle_id, a.last_update_date, a.product_version,a.patch_level, decode(a.status, 'I', 'Installed', 'S', 'Shared', 'N', 'Not Installed',a.status) Status, a.industry, b.application_name, c.application_short_name from fnd_product_installations a, fnd_application_tl b, fnd_application c where a.application_id = b.application_id and a.application_id = c.application_id and b.language = 'US' order by c.application_short_name;

R12 Cost Manager

R12 Cost Manager

--SQL Query to find out uncosted transactions

SELECT * FROM mtl_material_transactions WHERE costed_flag = 'N';

--Look for costing errors

select
count(*),organization_id,costed_flag
from apps.mtl_material_transactions
where 1=1
and costed_flag is not null
group by organization_id,costed_flag

--