APPS-ORACLE.RU

Расшифровка STATUS_CODE и PHASE_CODE из таблицы канкарентов FND_CONCURRENT_REQUESTS

PHASE_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_PHASE_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;

STATUS_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_STATUS_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;

Запрос для просмотра активных канкарентов (без использования lookup):

SELECT fu.user_name user_name
      ,to_char(nvl(cr.actual_start_date, cr.requested_start_date)
               ,'dd-mm-yyyy hh24:mi') start_time
      ,cr.request_id request_id
      ,decode(cr.parent_request_id, -1, 0, cr.parent_request_id) par_req_id
      ,DECODE(cr.phase_code,
              'C', 'Completed',
              'I', 'Inactive',
              'P', 'Pending',
              'R', 'Running',
              cr.phase_code
              ) as phase_code
      ,DECODE(cr.status_code,
              'A', 'Waiting', 
              'B', 'Resuming',
              'C', 'Normal', 
              'D', 'Cancelled',
              'E', 'Errored', 
              'F', 'Scheduled',
              'G', 'Warning', 
              'H', 'On Hold',
              'I', 'Normal', 
              'M', 'No Manager',
              'Q', 'Standby', 
              'R', 'Normal',
              'S', 'Suspended', 
              'T', 'Terminating',
              'U', 'Disabled', 
              'W', 'Paused',
              'X', 'Terminated', 
              'Z', 'Waiting',
              cr.status_code
              ) as status_code              
      ,cr.oracle_process_id db_pid
      ,vs.SID || ',' || vs.serial# sid_serial
      ,cr.concurrent_program_id
      ,(SELECT SUBSTR(cp.concurrent_program_name
               || '-' ||cpl.user_concurrent_program_name,1,65)
        FROM APPS.fnd_concurrent_programs cp
            ,APPS.fnd_concurrent_programs_tl cpl
        WHERE cp.application_id = cr.program_application_id
              AND cp.concurrent_program_id = cr.concurrent_program_id
              AND cpl.application_id = cr.program_application_id
              AND cpl.concurrent_program_id = cr.concurrent_program_id
              AND cpl.LANGUAGE = USERENV('LANG')
        ) CMGR_Program
FROM APPS.fnd_concurrent_requests cr
    ,v$process vp
    ,v$session vs
    ,APPS.fnd_user fu
WHERE 1=1
      and cr.phase_code <> 'C'
      AND cr.oracle_process_id = vp.spid(+)
      AND cr.oracle_session_id = vs.audsid(+)
      AND fu.user_id = cr.requested_by
order by user_name,start_time desc

Запрос для просмотра активных канкарентов (с lookup):

SELECT fu.user_name user_name
      ,to_char(nvl(cr.actual_start_date, cr.requested_start_date)
               ,'dd-mm-yyyy hh24:mi') start_time
      ,cr.request_id request_id
      ,decode(cr.parent_request_id, -1, 0, cr.parent_request_id) par_req_id
      ,flu1.meaning phase
      ,flu2.meaning status
      ,cr.oracle_process_id db_pid
      ,vs.SID || ',' || vs.serial# sid_serial
      ,cr.concurrent_program_id
      ,(SELECT SUBSTR(cp.concurrent_program_name
               || '-' ||cpl.user_concurrent_program_name,1,65)
        FROM APPS.fnd_concurrent_programs cp
            ,APPS.fnd_concurrent_programs_tl cpl
        WHERE cp.application_id = cr.program_application_id
              AND cp.concurrent_program_id = cr.concurrent_program_id
              AND cpl.application_id = cr.program_application_id
              AND cpl.concurrent_program_id = cr.concurrent_program_id
              AND cpl.LANGUAGE = USERENV('LANG')
        ) CMGR_Program
from apps.fnd_concurrent_requests cr
    ,v$process vp
    ,v$session vs
    ,apps.fnd_user fu
    ,apps.fnd_lookups flu1
    ,apps.fnd_lookups flu2    
WHERE 1=1
      and cr.phase_code <> 'C'
      AND cr.oracle_process_id = vp.spid(+)
      AND cr.oracle_session_id = vs.audsid(+)
      AND fu.user_id = cr.requested_by
      AND flu1.lookup_type = 'CP_PHASE_CODE'
      AND cr.phase_code = flu1.lookup_code
      AND flu2.lookup_type = 'CP_STATUS_CODE'
      AND cr.status_code = flu2.lookup_code      
order by user_name,start_time desc

Похожие записи:

• Java Concurrent Program
• HOST BASED CONCURRENT PROGRAM
• Список полномочий и групп запросов для отчета
• OEBS Генерация параметров запуска канкарента
• OEBS API Параллельные программы — отправка, ожидание, скрытый режим