Query to find the Request Group of a Concurrent Program


Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;
Advertisements

API’s to Create User,Reset Password and Add Responsibility


API’s to Create User,Reset Password and Add Responsibility

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
begin
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  );
  commit;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  ROLLBACK;
end;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  );
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  commit;
  else
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
  END if;
end;

Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;

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


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

The following query will fetch the Parameter List and associated Value Sets of 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 = :conc_prg_name
        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;

Find out the URL of the Application in the database


Find out the URL of the Application in the database:

Option 1:

select HOME_URL from icx_parameters;

Option 2:

Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
       (SELECT PROFILE_OPTION_ID
        FROM FND_PROFILE_OPTIONS
        WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
        AND LEVEL_VALUE=0;

Script to get all the Concurrent Program Request details


Script to get all the Concurrent Program Request details

Below is the script to get the Concurrent Program Request details by Various Users in a Particular Day.

select
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name Request_Name,
    fcpt.user_concurrent_program_name program_name,
    DECODE(fcr.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running') phase,
    DECODE(fcr.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A') status,
    round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
    round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) * 1440),2) wait_time,
    fu.User_Name Requestor,
    fcr.argument_text parameters,
    to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
    to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,
    to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,
    fcr.completion_text

From

    apps.fnd_concurrent_requests fcr,
    apps.fnd_concurrent_programs fcp,
    apps.fnd_concurrent_programs_tl fcpt,
    apps.fnd_user fu

Where 1=1
    -- and fu.user_name = 'DJKOCH' '
    -- and fcr.request_id = 1565261
    -- and fcpt.user_concurrent_program_name = 'Payables Open Interface Import''
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcp.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.program_application_id = fcp.application_id
    and fcp.application_id = fcpt.application_id
    and fcr.requested_by = fu.user_id
    and fcpt.language = 'US'
    and fcr.actual_start_date like sysdate
    -- and fcr.phase_code = 'C'
    -- and hold_flag = 'Y'
    -- and fcr.status_code = 'C'

GROUP BY
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name,
    fcr.requested_start_date,
    fu.User_Name,
    fcr.argument_text,
    fcr.actual_completion_date,
    fcr.actual_start_date,
    fcr.phase_code,
    fcr.status_code,
    fcr.resubmit_interval,
    fcr.completion_text,
    fcr.resubmit_interval,
    fcr.resubmit_interval_unit_code,
    fcr.description

Order by 1 desc;