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>’;

R12 Customer Bank Accounts

 The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Customer is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Customer Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Customer Site that the Bank Account is actually attached to is a little bit trickier!

This is done through linking together the following tables IBY_EXTERNAL_PAYERS_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payer Record held in IBY_EXTERNAL_PAYERS_ALL using the ext_pmt_party_id. It is the External Payer Record that links us to a customer and Customer Site Use.

There is a record stored in the IBY_EXTERNAL_PAYERS_ALL table for every customer site use defined. The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site Use that the Bank Account has been assigned to.

The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name                   customer_name
,      cust_acct.account_number
,      cust_uses.site_use_code
,      cust_loc.address1
,      cust_loc.address2
,      cust_loc.address3
,      cust_loc.address4
,      cust_loc.city
,      cust_loc.postal_code
,      bank.party_name                   bank_name
,      bank_prof.home_country
,      branch.party_name                 branch_name
,      branch_prof.bank_or_branch_number branch_number
,      account.bank_account_num
,      account.bank_account_name
FROM   hz_parties bank
,      hz_relationships rel
,      hz_parties branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
,      iby_ext_bank_accounts account
,      iby_account_owners acc_owner
,      iby_external_payers_all ext_payer
,      iby_pmt_instr_uses_all acc_instr
,      hz_parties cust
,      hz_cust_accounts cust_acct
,      hz_cust_acct_sites_all cust_site
,      hz_cust_site_uses_all cust_uses
,      hz_locations cust_loc
WHERE  1=1
AND    bank.party_id = rel.object_id
and    bank.party_type = rel.object_type
AND    rel.object_table_name = 'HZ_PARTIES'
AND    rel.relationship_code = 'BRANCH_OF'
AND    rel.subject_id = branch.party_id
AND    rel.subject_type = branch.party_type
AND    rel.subject_table_name = 'HZ_PARTIES'
AND    bank.party_id = bank_prof.party_id
AND    branch.party_id = branch_prof.party_id
AND    bank.party_id = account.bank_id
AND    branch.party_id = account.branch_id
AND    account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND    acc_owner.account_owner_party_id = cust.party_id
AND    account.ext_bank_account_id = acc_instr.instrument_id
AND    acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND    ext_payer.cust_account_id = cust_acct.cust_account_id
AND    cust_acct.cust_account_id = cust_site.cust_account_id
AND    cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND    cust_uses.site_use_id = ext_payer.acct_site_use_id
AND    cust_uses.location = cust_loc.location_id
AND    cust.party_id = cust_acct.party_id;

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;