Useful Queries

Database Session Query:

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s

join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;




Kill Database Session:


SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (
    SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME =  <TABLE NAME>
);
SELECT * FROM v$locked_object;
ALTER SYSTEM KILL SESSION 'SID, SERIAL#';


Set NLS Parameters:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY'

ACTID for Calling Workflow API:

SELECT item_key, Wf_Process_Activity.FindActivity( Wf_Process_Activity.RootInstanceId( wfi.item_type , wfi.item_key , wfi.root_activity ) , <Activity Name> , Wf_Item.Active_Date(wfi.item_type, wfi.item_key) ) actid

FROM wf_items wfi

WHERE item_type = <Item Type>

AND actid is not null

AND item_key    = <Item Key>;

DFF Identification Query:

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

Responsibility based on FORM FUNCTION:

SELECT  fr.responsibility_name,
        fm.user_menu_name,
        fme.prompt prompt_name,
        fff.user_function_name
 FROM   apps.fnd_menu_entries_vl fme,
        apps.fnd_menus_vl fm,
        apps.fnd_form_functions_vl fff,
        apps.fnd_form_vl ff,
        apps.fnd_responsibility_vl fr
 WHERE  1 = 1
 AND    ff.form_name = 'CSXSRISR'
 AND    fff.form_id = ff.form_id
 AND    fme.function_id = fff.function_id
 AND    fm.menu_id = fme.menu_id

 AND    fr.menu_id = fm.menu_id;

Setting up SQL Environment:

alter session set nls_language = 'AMERICAN';
begin
  mo_global.set_policy_context('S',101);

end;

Query to file Profile Option Values:

SELECT SUBSTR(pro1.user_profile_option_name,1,100) Profile,
pro1.profile_option_name,
  DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,
  DECODE(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value,
  NVL(pov.profile_option_value,'Is Null') Profile_option_Value
FROM fnd_profile_option_values pov,
  fnd_responsibility_tl resp,
  fnd_application appl,
  fnd_user u,
  fnd_profile_options pro,
  fnd_profile_options_tl pro1
WHERE 1=1
AND pro1.user_profile_option_name like ('%User Profile Name%')
AND pro.profile_option_name = pro1.profile_option_name
AND pro.profile_option_id     = pov.profile_option_id
AND resp.responsibility_name LIKE '%Resp Name'
AND pov.level_value = resp.responsibility_id (+)
AND pov.level_value = appl.application_id (+)
AND pov.level_value = u.user_id (+)

ORDER BY 1,2;