Daily used Apps scripts by Oracle AppsTechnical consultant

SQL Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
fdfcuv.flex_value_set_id "Value Set Id",
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name like 'XX%'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

User concurrent name 

select user_concurrent_program_name from apps.fnd_conc_req_summary_v
where upper(user_concurrent_program_name) = :user_concurrent_program_name
/

Responsibility associated with concurrent program

SELECT frv.responsibility_name reponsibility,
    frg.request_group_name requestgroup
    FROM apps.fnd_request_groups frg,
     apps.fnd_request_group_units fgu,
     apps.fnd_concurrent_programs fcp,
     apps.fnd_concurrent_programs_tl fct,
     apps.fnd_responsibility_vl frv
    WHERE frg.request_group_id = fgu.request_group_id
    AND fgu.request_unit_id = fcp.concurrent_program_id
    AND fcp.concurrent_program_id = fct.concurrent_program_id
    AND frv.request_group_id = fgu.request_group_id
    AND fct.user_concurrent_program_name = :Program_name
    AND fct.LANGUAGE = 'US'
    /
 
Executed concurrent program details

SELECT DISTINCT fcr.request_id, fcr.actual_start_date,
fcr.actual_completion_date,
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600) HOURS,
floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60) MINUTES,
round((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) SECS,
DECODE (fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) phase_code,
DECODE (fcr.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',
'N/A'
) status_code,
fcr.outfile_name, fcr.number_of_arguments, fcr.argument_text,
frt.responsibility_name, fav.application_name, fav.application_short_name appl_short_name, fu.user_name,
fu.description user_description, fu.start_date user_start_date,
fcp.user_concurrent_program_name,
fcp.concurrent_program_name short_name, fe.executable_name,
DECODE (fe.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
) execution_method,
fe.execution_file_name
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_application_vl fav,
apps.fnd_responsibility_tl frt,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_executables fe
WHERE fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.responsibility_application_id = fav.application_id
AND FCP.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND fcp.user_concurrent_program_name like '%Concurrent_program_name % '
order by fcr.actual_start_date desc

/


concurrent program associated to request set

SELECT frs.user_request_set_name "Request Set Name",
frs.start_date_active "Request set Start Date",    
frs.end_date_active "Request set End Date", frsf.stage_name,
frsf.user_stage_name, frsp.SEQUENCE "Stage Request Sequence",    
fcp.user_concurrent_program_name "Concurrent Program"
FROM fnd_request_sets_vl frs, fnd_req_set_stages_form_v frsf,
fnd_request_set_programs frsp, fnd_concurrent_programs_tl fcp
WHERE frs.request_set_id = frsf.request_set_id
AND frsf.request_set_stage_id = frsp.request_set_stage_id  
AND frsp.concurrent_program_id = fcp.concurrent_program_id
AND upper(fcp.user_concurrent_program_name) like upper(' : Web Time Archiving Program%')--upper(' TSI OUTBOUND FILE FTP Program%')--upper('%Web%Time%Archiving%Program%')
/

Query to get the List of Users having Any specific responsibility.

SELECT fu.user_name,
  frv.responsibility_name,
  TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
  TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id            = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND FURGD.END_DATE         IS NULL
AND FRV.RESPONSIBILITY_NAME          = :Responsiblity_name
AND furgd.start_date                <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND NVL(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND NVL(frv.end_date, sysdate + 1)   > sysdate;


-- Query to get the  request group name  for responsibility

SELECT DISTINCT FRT.RESPONSIBILITY_NAME,
                FMT.USER_MENU_NAME,
                FAT.APPLICATION_NAME,
                (SELECT REQUEST_GROUP_NAME
                   FROM APPS.FND_REQUEST_GROUPS FRG
                  WHERE FRG.APPLICATION_ID = FR.APPLICATION_ID
                    AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID) REQUET_GRP
  FROM APPS.FND_RESPONSIBILITY_TL FRT,
       APPS.FND_RESPONSIBILITY    FR,
       APPS.FND_MENUS_TL          FMT,
       APPS.FND_MENUS             FM,
       APPS.FND_APPLICATION_TL    FAT,
       APPS.FND_APPLICATION       FFA
 WHERE FRT.RESPONSIBILITY_ID(+) = FR.RESPONSIBILITY_ID
   AND FRT.RESPONSIBILITY_NAME  like 'Pay%'
   AND FR.MENU_ID = FMT.MENU_ID
   AND FR.MENU_ID = FM.MENU_ID
   AND FAT.APPLICATION_ID = FfA.APPLICATION_ID
   AND FFA.APPLICATION_ID = FR.APPLICATION_ID
   AND FRT.LANGUAGE = 'US'
   AND FAT.LANGUAGE = 'US'

Query will provide the dump of all form personalizations in Oracle

SELECT ffcr.function_name, ffcr.form_name, ffcr.SEQUENCE "P Seq", ffcr.enabled "Rule Enabled?", ffcr.fire_in_enter_query, ffcr.rule_type, ffcr.description "Rule Description", ffcr.trigger_event, ffcr.condition, ffca.SEQUENCE "Action sequence", DECODE (ffca.action_type, 'P', 'Property', 'M', 'Message', 'S', 'Menu', 'B', 'Builtin' ) "Action Type", ffca.summary " Action Desc", ffca.enabled "Action Enabled?", ffca.object_type, ffca.target_object, ffca.property_value, DECODE (ffca.MESSAGE_TYPE, 'W', 'Warn', 'H', 'Hint', 'E', 'Error', 'D', 'Debug', 'S', 'Show', NULL ) "Message Type", decode(ffca.builtin_type, 'G', 'Go Item', 'C', 'Launch SRS Form', 'E', 'Launch a Function', 'U', 'Launch a URL', 'D', 'Do Key', 'P', 'Execute a Procedure', 'B', 'GO_BLOCK', 'F', 'FORMS_DDL', 'R', 'RAISE_FORM_TRIGGER_FAILURE', 'T', 'EXECUTE_TRIGGER', 'S', 'SYNCHRONIZE', 'L', 'Call Custom Library', 'Q', 'Create Record Group from Query', 'X', 'Set Profile Value in Cache', NULL) "Builtin Type", ffca.builtin_arguments, ffca.property_name, ffca.menu_entry, ffca.menu_label, ffca.menu_seperator, ffca.menu_enabled_in, ffca.menu_argument_long, ffca.menu_argument_short FROM fnd_form_custom_rules ffcr, fnd_form_custom_actions ffca WHERE ffcr.ID = ffca.rule_id