Finding Responsibilities for Concurrent Programs

Finding Concurrent Program Responsibilities

SELECT fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcp.concurrent_program_id,

       frt.responsibility_name,

       REQUEST_GROUP_NAME

  FROM fnd_concurrent_programs     fcp,

       fnd_concurrent_programs_tl  fcpt,

       fnd_request_groups          frg,

       fnd_request_group_units     frgu,

       fnd_responsibility          fr,

       fnd_responsibility_tl       frt

 WHERE     USER_CONCURRENT_PROGRAM_NAME LIKE :p_conc_program_name

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fr.responsibility_id = frt.responsibility_id

       AND frgu.request_group_id = frg.request_group_id

       AND frgu.request_group_id = fr.request_group_id

       AND fcpt.concurrent_program_id = frgu.request_unit_id

       AND fcpt.language(+) = USERENV ('lang')

       AND frt.language(+) = USERENV ('lang');

Finding Responsibilities for a Particular Form

  SELECT DISTINCT u.user_name,

                  rtl.responsibility_name,

                  ff.function_name,

                  ffl.user_function_name

    FROM fnd_compiled_menu_functions cmf,

         fnd_form_functions ff,

         fnd_form_functions_tl ffl,

         fnd_responsibility r,

         fnd_responsibility_tl rtl,

         fnd_user_resp_groups urg,

         fnd_user u

   WHERE     cmf.function_id = ff.function_id

         AND r.menu_id = cmf.menu_id

         AND urg.responsibility_id = r.responsibility_id

         AND rtl.responsibility_id = r.responsibility_id

         AND cmf.grant_flag = 'Y'

         AND r.application_id = urg.responsibility_application_id

         AND u.user_id = urg.user_id

         AND UPPER (ffl.user_function_name) = UPPER (:User_Function_Name)   

         --XXARS_BATCH_PRO_SO

         AND ff.function_id = ffl.function_id

ORDER BY u.user_name

List all the Webadis

SELECT biv.application_id,

       biv.user_name webadi_name,

       bi.interface_name,

       biv.integrator_code,

       bi.upload_param_list_code,

       ba.attribute_code,

       ba.attribute1 Type,

       ba.attribute2 Package_Name,

       blb.layout_code

  FROM bne_integrators_vl biv,

       bne_layouts_b blb,

       bne_interfaces_b bi,

       bne_param_lists_b bpl,

       bne_attributes ba

 WHERE 1 = 1

   AND biv.user_name like 'EXPD%' --Webadi Name

   AND bi.integrator_code = biv.integrator_code

   AND biv.integrator_code = blb.integrator_code

   AND bi.upload_param_list_code = bpl.param_list_code

   AND bpl.attribute_code = ba.attribute_code;

Finding Responsibilities for a Webadi

SELECT RESPONSIBILITY_NAME, Description

  FROM fnd_responsibility_tl

 WHERE RESPONSIBILITY_ID IN

           (SELECT RESPONSIBILITY_ID

              FROM fnd_responsibility

             WHERE menu_id IN

                       (SELECT menu_id

                          FROM FND_MENU_ENTRIES_VL

                         WHERE function_id IN

                                   (SELECT function_id

                                      FROM FND_FORM_FUNCTIONS_VL

                                     WHERE parameters LIKE                                                   'bne:page=BneCreateDoc%integrator%'|| :Integrator)))

DFF Query to find the Descriptive Flex Values

SELECT ffv.descriptive_flexfield_name "DFF Name",

       ffv.application_table_name "Table Name",

       ffv.title "Title",

       ap.application_name "Application",

       ffc.descriptive_flex_context_code "Context Code",

       ffc.descriptive_flex_context_name "Context Name",

       ffc.description "Context Desc",

       ffc.enabled_flag "Context Enable Flag",

       att.column_seq_num "Segment Number",

       att.form_left_prompt "Segment Name",

       att.application_column_name "Column",

       fvs.flex_value_set_name "Value Set",

       att.display_flag "Displayed",

       att.enabled_flag "Enabled",

       att.required_flag "Required",

       ffc.descriptive_flex_context_code,

       ffv.title

FROM apps.fnd_descriptive_flexs_vl ffv,

     apps.fnd_descr_flex_contexts_vl ffc,

     apps.fnd_descr_flex_col_usage_vl att,

     apps.fnd_flex_value_sets fvs,

     apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

AND ap.application_id=ffv.application_id

AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name

AND ffv.application_id = ffc.application_id

AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code

AND fvs.flex_value_set_id=att.flex_value_set_id

AND ffv.title like 'Common Lookups'

AND ffc.descriptive_flex_context_code like 'EXPD_SWIFTREF_BANKING_TYPE'

ORDER BY att.column_seq_num

Query to find the Concurrent Program Details

select a.request_id, c.user_name, b.user_concurrent_program_name,

b.description, a.phase_code, a.status_code, a.request_date,

a.last_update_date, a.requested_start_Date,a.actual_start_Date,

a.actual_completion_date, a.argument_text, a.completion_text,

a.logfile_name, a.logfile_node_name, a.outfile_name, a.outfile_node_name

from fnd_concurrent_requests a, fnd_concurrent_programs_tl b, fnd_user c

where 1=1

and b.user_concurrent_program_name in ( 'EXPD HC Reprint AR Transactions')

--and a.argument_text not like '%101%'

and a.concurrent_program_id = b.concurrent_program_id

and trunc (a.request_date) >= trunc(sysdate-30)

and a.requested_by = c.user_id

--and a.phase_code ='R'

 --and c.user_name = 'RSUNDARAVADIVEL'

--and c.user_name not in ('SYSADMIN')

order by a.request_date desc