DFF query

SELECT distinct 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”
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 1=1
AND 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’—name of   the lookup
AND ffc.descriptive_flex_context_code like ‘ACCOUNT SEGMENT’ — context name
–AND UPPER(att.form_left_prompt) = ‘MINOR HEAD’
ORDER BY att.column_seq_num

Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Adding Values to value set in 12.2.3

For adding values to value set in 12.2.3 first grants and security needs to be set.

Setting up value security mostly consists of creating grants using the Functional Administrator responsibility.

Grants:
The grant has three basic parts that we assign when we create the grant:
1. Grantee and security context (who gets privileges and the context where privileges are available)
2. Data security object “Flexfield Value Set Security Object”, object instance set, and parameter values if needed (what data is affected by the grant)
3. Permission set (what privileges are allowed on the object)

New Value Sets
No users are allowed to view, insert or update any value set values unless access is explicitly granted. You must explicitly set up access for specific users by enabling appropriate grants and roles for those users. That restriction includes values for value sets created by the same user. For example, if a user creates a new value set definition using the Value Set window and immediately goes to create values for that new value set, the user will not be able to find or enter values for that new set unless:

 

 

2

1

Creating User and adding responsibility from back end

/*****************Add User********************/
DECLARE
LC_USER_NAME VARCHAR2(100) := ‘PRAY’;
LC_USER_PASSWORD VARCHAR2(100) := ‘welcome123’;
ld_user_start_date DATE := sysdate;
ld_user_end_date VARCHAR2(100) := NULL;

BEGIN
fnd_user_pkg.createuser
( x_user_name => lc_user_name,
X_OWNER => NULL,
x_unencrypted_password => lc_user_password,
x_start_date => ld_user_start_date,
x_end_date => ld_user_end_date
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

 
/********************add responsibility*********************/

DECLARE
lc_user_name VARCHAR2(100) := ‘PRAY’;
lc_resp_appl_short_name VARCHAR2(100) := ‘SYSADMIN’;
lc_responsibility_key VARCHAR2(100) := ‘SYSTEM_ADMINISTRATOR’;
LC_SECURITY_GROUP_KEY VARCHAR2(100) := ‘STANDARD’;
ld_resp_start_date DATE := sysdate;
ld_resp_end_date DATE := NULL;

BEGIN
fnd_user_pkg.addresp
( username => lc_user_name,
resp_app => lc_resp_appl_short_name,
resp_key => lc_responsibility_key,
security_group => lc_security_group_key,
description => NULL,
start_date => ld_resp_start_date,
end_date => ld_resp_end_date
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Enable Trace for a concurrent program

The main use of enabling trace for a concurrent program comes during performance tuning.

By examining a trace file, we come to know which query/queries is/are taking the longest

time to execute, there by letting us to concentrate on tuning them in order to improve the

overall performance of the program.

The following is an illustration of how to Enable and View a trace file for a Concurrent     Program.

Navigation: Application Developer–>Concurrent–>Program

Check the Enable Trace Check box. After that go to that particular Responsibility and run the Concurrent Program.


Check that the Concurrent Program has been completed successfully.

The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:

Select oracle_process_id from fnd_concurrent_requests where request_id=’2768335′

(This query displays Process Id)

The path to the trace file can be found by using the below query:

 

 

(This Query displays the path of trace file)

The Trace File generated will not be in the readable format. We have to use TKPROF utility to convert the file into a readable format.

Run the below tkprof command at the command prompt.

TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

A readable file will be generated from the original trace file which can be further

analyzed to improve the performance. This file has the information about the

parsing, execution and fetch times of various queries used in the program.

Dependent parameters in Concurrent Program

This article illustrates the usage of $FLEX$ with an example.

$FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters)

Syntax –     :$FLEX$.Value_ Set_Name

Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you want your parameter to depend on.

Some scenarios where $FLEX$ can be used:

Example1:

Say you have a concurrent program with the below 2 parameters which are valuesets :

Parameter1 is Deparment

Parameter2 is Employee name

Let’s say there are 100 deparments and each deparment has 200 employees.  Therefore we have 2000 employees altogether.

If we  display all department names in the valueset of parameter1 and all employee names in parameter2  value set  then it might kill lot of performance and also it will be hard for a user to select an employee from the list of 2000 entries.

Better Solution is to let user select the department from the Department Valuset first. Based on the department selected, you can display only the employees in parameter2 that belong to the selected department in parameter1 valueset.