Kintana Script : Oracle Applications Tables

Purpose

The Oracle Applications graphical user interface is arguably the worst, most hostile, user unfriendly interface ever perpetrated.

The further you can stay away from it the better.

These sql queries can help you get straight to the stuff you need for Object Migrator support bypassing the gui.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the script into Toad or SQL Runner

-- User

select as_fu.user_id,
        as_fu.user_name,
        as_fu.start_date,
        as_fu.end_date,
        as_fu.description
 from applsys.FND_USER as_fu
 where as_fu.user_name = 'DCGOODALL'
 

-- User Responsibility Groups

select as_furgo.user_id,
       as_furgo.responsibility_id,
       as_furgo.responsibility_application_id,
       as_furgo.security_group_id,
       as_furgo.description
from applsys.FND_USER_RESP_GROUPS_OLD as_furgo
where as_furgo.user_id in ( 1110, 1010, 1011 )

-- Responsibility

select as_fr.application_id,
       as_fr.responsibility_id,
       as_fr.menu_id,
       as_fr.responsibility_key
from applsys.FND_RESPONSIBILITY as_fr
where as_fr.application_id = '8400'
order by as_fr.responsibility_key

-- Request Groups

select as_frg.application_id,
       as_frg.request_group_id,
       as_frg.request_group_name,
       as_frg.description,
       as_frg.request_group_code
from applsys.FND_REQUEST_GROUPS as_frg
where as_frg.request_group_code like '%AOL%'

-- Concurrent Programs

select as_fcp.application_id,
       as_fcp.concurrent_program_id,
       as_fcp.concurrent_program_name
from applsys.FND_CONCURRENT_PROGRAMS as_fcp
order by as_fcp.concurrent_program_name

-- Executables

select as_fe.application_id,
       as_fe.executable_id,
       as_fe.executable_name,
       as_fe.execution_file_name
from applsys.FND_EXECUTABLES as_fe
order by as_fe.execution_file_name

-- Applications

select --fa.application_id,
       fa.application_short_name,
       fat.application_name
from applsys.FND_APPLICATION fa,
     applsys.FND_APPLICATION_TL fat
where fa.application_id = fat.application_id
order by fat.application_name

select count (*) from FND_APPLICATION@INSTANCE_link

-- Menus

select as_fm.menu_id,
       as_fm.menu_name,
       as_fm.type
from applsys.FND_MENUS as_fm
order by as_fm.menu_name

-- Forms

select as_ff.application_id,
       as_ff.form_id,
       as_ff.form_name,
       as_ff.last_updated_by
from applsys.FND_FORM as_ff
where as_ff.form_name like '%OKC%'
order by as_ff.form_name

-- Form Functions

select as_fff.function_id,
       as_fff.function_name,
       as_fff.last_updated_by,
       as_fff.last_updated_by
from applsys.FND_FORM_FUNCTIONS as_fff
--where as_fff.function_name like '%MYFUNCTION%'
order by as_fff.function_name

-- Concurrent (Manager) Requests
-- List program, parameters, status, log, out files

select * from applsys.FND_CONCURRENT_REQUESTS as_fcr
where as_fcr.request_id = 999999;  -- Supply concurrent request id

-- How to flag a concurrent manager request to be killed.
-- This script updates the phase_code and status_code
-- which pretty much define what you see on the apps tier.

update applsys.FND_CONCURRENT_REQUESTS
set PHASE_CODE='C', STATUS_code=’C’
where request_id = 999999;  -- Supply concurrent request id

-- If Concurrent requests timeout with
-- ORA-02049: timeout: distributed transaction waiting for lock
-- check that these Concurrent Managers Processes are running
-- in Oracle Applications for Object*Migrator:
-- o Internal Manager
-- o Conflict Resolution Manager
-- o Standard Manager
-- o Scheduler/Prereleaser Manager

select fcq.concurrent_queue_name,
       fcq.running_processes
from applsys.FND_CONCURRENT_QUEUES fcq
where fcq.concurrent_queue_name = '&myqueuename'
  and fcq.running_processes > $mythreshold;

Download

KintanaScripts.zip (58 Kb)