I will try to give the basic queries without joining with plans or options or programs. So that everyone can understand the basic underlying conditions. Later on you can add all plan and program joins to add to the reports.
1. Enrollment Results
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE business_group_id = p_business_group_id
AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.person_id = p_person_id
Same query will give you coverage amount stored as bnft_amt in the table.
2. Enrollment Rates
For each enrollment you can have different rates. Rates are store in table ben_prtt_rt_val
you can use the following query for rates
SELECT *
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
But here main things to take care is rt_strt_dt and rt_end_dt. rt_strt_dt will always be less than equal to rt_end_date for a recurring rate but rt_strt_dt and rt_end_dt are equal when rt is non-recurring.
3. Eligibility Results
These results are store for each life event in person record. Also in this table you will find if a particular enrollments is set for default or auto enrollment with dflt_flag and auto_enrt_flag.
SELECT epe.*
FROM ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
WHERE pil.person_id = p_person_id
AND epe.per_in_ler_id = pil.per_in_ler_id;
4. Enrolled Dependents
SELECT *
FROM ben_elig_cvrd_dpnt_f dpnt,
ben_prtt_enrt_rslt_f pen
WHERE dpnt.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND EXISTS (SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = pen.per_in_ler_id
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT'))
AND p_effective_date BETWEEN dpnt.cvg_strt_dt AND Nvl(dpnt.cvg_thru_dt,p_effective_date)
AND (Nvl(dpnt.cvg_thru_dt,p_effective_date) <= dpnt.effective_end_date
OR p_effective_date BETWEEN dpnt.effective_start_date AND Nvl(dpnt.effective_end_date,p_effective_date))
AND dpnt.dpnt_person_id = p_person_id
In above query you can dpnt_person_id is the dependent. You can see the enrolled person in ben_prtt_enrt_rslt_f table.
5. Pending Action Items
SELECT *
FROM ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
WHERE pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
AND pil.per_in_ler_id (+) = pea.per_in_ler_id
AND (pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT')
OR pil.per_in_ler_stat_cd IS NULL)
AND pen.sspndd_flag = 'Y'
AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
I will come up with some queries. Please let me know what kind of queries I can add here.
Wednesday, March 11, 2009
Wednesday, January 21, 2009
Basic Definition of Oracle Advanced Benefits (OAB)Terminology
Hi Guys
I have received lot of questions regarding Benefits module. So I thought I will go through some basics terminology used in Benefits following with some details and queries.
Life Event:: Life event is any change in the life of an employee. that may be new hire, marriage, child, or even termination. All these events, in one or other organization, effect benefits given to employee. Any life event that can be processed is a Potential Life Event. Once we Process a potential Life event it is called Person in Life Event. Person in Life event is stored in BEN_PER_IN_LER. All Benefits data is stored with Primary key of this table (i.e. PER_IN_LER_ID), like eligibility records for each processed life events, enrollments, rate values, etc.
Plan: Any Benefit given to employee is defined as a Plan. It may be Medical Insurance, Vision/ Dental Insurance, 401 K Saving Plan, Car Insurance etc. A Program is a group of plans that have similar properties, like when to enroll, how to enroll etc.
Enrollment Requirements: For each Program or Plan we define Enrollment Requirements. These definitions tells the processes, when to start the coverage or rates (deductions), what all plans will be automatically enrolled, what all will be defaulted. Setting up these are mandatory. We can defined these properties at different levels, Program, Plan, Life event for Program, Life event for Plan, Option. Lowest level always takes precedence.
Standard Rate: We can attach a monetary value or any numeric value to a plan, this may be deduction or a bonus. We define this using a standard rate. Elements are also attached to a standard rate. This is the link where payroll and Benefits are integrated.
I have received lot of questions regarding Benefits module. So I thought I will go through some basics terminology used in Benefits following with some details and queries.
Life Event:: Life event is any change in the life of an employee. that may be new hire, marriage, child, or even termination. All these events, in one or other organization, effect benefits given to employee. Any life event that can be processed is a Potential Life Event. Once we Process a potential Life event it is called Person in Life Event. Person in Life event is stored in BEN_PER_IN_LER. All Benefits data is stored with Primary key of this table (i.e. PER_IN_LER_ID), like eligibility records for each processed life events, enrollments, rate values, etc.
Plan: Any Benefit given to employee is defined as a Plan. It may be Medical Insurance, Vision/ Dental Insurance, 401 K Saving Plan, Car Insurance etc. A Program is a group of plans that have similar properties, like when to enroll, how to enroll etc.
Enrollment Requirements: For each Program or Plan we define Enrollment Requirements. These definitions tells the processes, when to start the coverage or rates (deductions), what all plans will be automatically enrolled, what all will be defaulted. Setting up these are mandatory. We can defined these properties at different levels, Program, Plan, Life event for Program, Life event for Plan, Option. Lowest level always takes precedence.
Standard Rate: We can attach a monetary value or any numeric value to a plan, this may be deduction or a bonus. We define this using a standard rate. Elements are also attached to a standard rate. This is the link where payroll and Benefits are integrated.
Coverage: Coverage is basically a kind of benefit that is given to an employee, like medical insurance, dental insurance etc.
..
Now I will try to use all above terms to be used in a benefit, say XX_Medical_Insurance. Say customer requirement is to give medical insurance once a person joins.
1. As setup a new life event will be setup, which will be triggered when a new employee is cretaed.
2. XX_Medical_Insurance Plan should be created. Then we will define enrollment requirements such that when a life event is processed person is enrolled as first date of next pay-period and deductions are also starting on first date of next payperiod.
3. Coverage is enterable such that employee will tell, I need $50,000 coverage on my medical insurance
4. Standard Rate is dependent on coverage, so whatever coverage employee chooses accordingly amount will be deducted. Deduction amount is attached with a element.
5. Now when person is hired, life event will trigger. This will be potential life event.
6. Now once this life event is processed, Person in Life event record will be created. At this time eligibility will be calculated.
7. Now once the person enrolls into benefit, benefits will stored again Person in Life event record.
I will be coming up with various queries to find enrollments, enrollment rates, eligibility etc.
Tuesday, September 30, 2008
Miscellaneous SQL/ PLSQLs
Finding Patch Set Level
SELECT * FROM (
SELECT '(Patch: ' || bug_number || ') applied ' || LAST_UPDATE_DATE PFbugDate
FROM ad_bugs
WHERE BUG_NUMBER IN (
'2115771',
'2268451',
'2502761',
'2632500',
'2803988',
'2968701', -- F
'3116666', -- G
'3233333', -- H
'3127777', -- I = 11.5.10 base
'3333633', -- J
'3500000', -- K
'3800000', -- L
'5055050') -- Rup1
ORDER BY LAST_UPDATE_DATE desc
) WHERE ROWNUM = 1;
Find the key flex field structure
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
'Y', 'OK: Dynamic',
'ERROR: No Dynamic'
) dynamic,
DECODE (freeze_flex_definition_flag,
'Y', 'OK: Freezed',
'ERROR: No Freezed'
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
'Y', 'OK: Required',
'ERROR: No required'
) required,
DECODE (fvs.flex_value_set_name,
NULL, 'NULL',
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
'N', 'OK: No validation',
'ERROR: ' || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
'N', 'OK: No',
'ERROR: Uppercase Only'
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
'Y', 'OK: Allowed',
'ERROR: Not Allowed'
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
'N', 'OK:Not justified',
'ERROR: Justified'
) right_justify,
DECODE (format_type,
'C', 'OK: Char',
'ERROR: ' || format_type
) format_type
FROM fnd_id_flex_structures_vl ffst,
fnd_id_flex_segments_vl ffsg,
fnd_flex_value_sets fvs
WHERE ffst.application_id = 800
AND ffst.id_flex_code = 'GRD'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id;
Find the Descriptive flex field structure
SELECT apps.application_name "App",
d_flex.title "Title",
d_flex.application_table_name "Table",
d_flex.context_user_override_flag "DFF Disp",
d_flex.freeze_flex_definition_flag "Frozen",
d_flex.default_context_value "Default Ctx",
d_flex.context_required_flag "DFF Required",
ctx_val_s.flex_value_set_name "Ctx ValueSet",
ctx.descriptive_flex_context_name "Ctx",
ctx.enabled_flag "Ctx Enabled",
col.form_left_prompt "Prompt",
col.end_user_column_name "Column",
val_s.flex_value_set_name "Value Set",
col.enabled_flag " Col Enabled",
col.required_flag "Required",
col.application_column_name "App Column",
col.display_flag " Col Display",
col.default_value "Default Val",
col.security_enabled_flag "Security",
col.display_size,
col.maximum_description_len,
col.concatenation_description_len,
col.application_id,
d_flex.concatenated_segs_view_name,
col.descriptive_flexfield_name,
col.form_above_prompt,
col.descriptive_flex_context_code,
col.range_code,
col.flex_value_set_id,
col.default_type,
col.srw_param
FROM fnd_descr_flex_col_usage_vl col,
fnd_descr_flex_contexts_vl ctx,
fnd_descriptive_flexs_vl d_flex,
fnd_flex_value_sets val_s,
fnd_flex_value_sets ctx_val_s,
fnd_application_tl apps
WHERE d_flex.application_id IN (809,
808)
--AND Upper(d_flex.title) LIKE Upper('%Addtional Ben Pl F Details%')
AND d_flex.descriptive_flexfield_name = ctx.descriptive_flexfield_name
AND ctx.descriptive_flexfield_name = col.descriptive_flexfield_name (+)
AND col.descriptive_flex_context_code (+) = ctx.descriptive_flex_context_code
AND val_s.flex_value_set_id (+) = col.flex_value_set_id
AND ctx_val_s.flex_value_set_id (+) = d_flex.context_override_value_set_id
AND apps.application_id = d_flex.application_id
AND d_flex.application_table_name <> 'FND_SRS_MASTER'
ORDER BY apps.application_name,
d_flex.title,
ctx.descriptive_flex_context_name,
col.column_seq_num;
Compile a Menu
declare
l_r number;
l_e varchar2(1000);
begin
fnd_function.compile(l_e,l_r,'N');
end;
Retrieve Supervisory Hierarchy (CONNECT BY PRIOR)
SELECT lpad(' ',level*3)
||
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.person_id
AND rownum =1
) full_name,
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.supervisor_id
AND rownum =1
) sup ,
assignment_number,
position_id
FROM per_all_assignments_f paf
WHERE business_group_id = 81
AND assignment_type = 'E'
AND assignment_status_type_id = 1
AND sysdate BETWEEN effective_start_date AND effective_end_date START
WITH assignment_number = '2748' CONNECT BY prior person_id = supervisor_id;
Initialize Apps (FND_GLOBAL.APPS_INTIALIZE)
declare
l_user_id number;
l_resp_id number;
l_resp_app_id number;
cursor get_user is
select user_id
from fnd_user
where user_name = ‘GSEHGAL’;
cursor get_resp is
select responsibility_id, application_id
from fnd_responsibility_vl
where responsibility_key = 'HIRING_MANAGER';
begin
open get_user;
fetch get_user into l_user_id;
close get_user;
open get_resp;
fetch get_resp into l_resp_id, l_resp_app_id;
close get_resp;
fnd_global.apps_initialize(l_user_id, l_resp_id,l_resp_app_id);
end;
SELECT * FROM (
SELECT '(Patch: ' || bug_number || ') applied ' || LAST_UPDATE_DATE PFbugDate
FROM ad_bugs
WHERE BUG_NUMBER IN (
'2115771',
'2268451',
'2502761',
'2632500',
'2803988',
'2968701', -- F
'3116666', -- G
'3233333', -- H
'3127777', -- I = 11.5.10 base
'3333633', -- J
'3500000', -- K
'3800000', -- L
'5055050') -- Rup1
ORDER BY LAST_UPDATE_DATE desc
) WHERE ROWNUM = 1;
Find the key flex field structure
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
'Y', 'OK: Dynamic',
'ERROR: No Dynamic'
) dynamic,
DECODE (freeze_flex_definition_flag,
'Y', 'OK: Freezed',
'ERROR: No Freezed'
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
'Y', 'OK: Required',
'ERROR: No required'
) required,
DECODE (fvs.flex_value_set_name,
NULL, 'NULL',
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
'N', 'OK: No validation',
'ERROR: ' || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
'N', 'OK: No',
'ERROR: Uppercase Only'
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
'Y', 'OK: Allowed',
'ERROR: Not Allowed'
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
'N', 'OK:Not justified',
'ERROR: Justified'
) right_justify,
DECODE (format_type,
'C', 'OK: Char',
'ERROR: ' || format_type
) format_type
FROM fnd_id_flex_structures_vl ffst,
fnd_id_flex_segments_vl ffsg,
fnd_flex_value_sets fvs
WHERE ffst.application_id = 800
AND ffst.id_flex_code = 'GRD'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id;
Find the Descriptive flex field structure
SELECT apps.application_name "App",
d_flex.title "Title",
d_flex.application_table_name "Table",
d_flex.context_user_override_flag "DFF Disp",
d_flex.freeze_flex_definition_flag "Frozen",
d_flex.default_context_value "Default Ctx",
d_flex.context_required_flag "DFF Required",
ctx_val_s.flex_value_set_name "Ctx ValueSet",
ctx.descriptive_flex_context_name "Ctx",
ctx.enabled_flag "Ctx Enabled",
col.form_left_prompt "Prompt",
col.end_user_column_name "Column",
val_s.flex_value_set_name "Value Set",
col.enabled_flag " Col Enabled",
col.required_flag "Required",
col.application_column_name "App Column",
col.display_flag " Col Display",
col.default_value "Default Val",
col.security_enabled_flag "Security",
col.display_size,
col.maximum_description_len,
col.concatenation_description_len,
col.application_id,
d_flex.concatenated_segs_view_name,
col.descriptive_flexfield_name,
col.form_above_prompt,
col.descriptive_flex_context_code,
col.range_code,
col.flex_value_set_id,
col.default_type,
col.srw_param
FROM fnd_descr_flex_col_usage_vl col,
fnd_descr_flex_contexts_vl ctx,
fnd_descriptive_flexs_vl d_flex,
fnd_flex_value_sets val_s,
fnd_flex_value_sets ctx_val_s,
fnd_application_tl apps
WHERE d_flex.application_id IN (809,
808)
--AND Upper(d_flex.title) LIKE Upper('%Addtional Ben Pl F Details%')
AND d_flex.descriptive_flexfield_name = ctx.descriptive_flexfield_name
AND ctx.descriptive_flexfield_name = col.descriptive_flexfield_name (+)
AND col.descriptive_flex_context_code (+) = ctx.descriptive_flex_context_code
AND val_s.flex_value_set_id (+) = col.flex_value_set_id
AND ctx_val_s.flex_value_set_id (+) = d_flex.context_override_value_set_id
AND apps.application_id = d_flex.application_id
AND d_flex.application_table_name <> 'FND_SRS_MASTER'
ORDER BY apps.application_name,
d_flex.title,
ctx.descriptive_flex_context_name,
col.column_seq_num;
Compile a Menu
declare
l_r number;
l_e varchar2(1000);
begin
fnd_function.compile(l_e,l_r,'N');
end;
Retrieve Supervisory Hierarchy (CONNECT BY PRIOR)
SELECT lpad(' ',level*3)
||
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.person_id
AND rownum =1
) full_name,
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.supervisor_id
AND rownum =1
) sup ,
assignment_number,
position_id
FROM per_all_assignments_f paf
WHERE business_group_id = 81
AND assignment_type = 'E'
AND assignment_status_type_id = 1
AND sysdate BETWEEN effective_start_date AND effective_end_date START
WITH assignment_number = '2748' CONNECT BY prior person_id = supervisor_id;
Initialize Apps (FND_GLOBAL.APPS_INTIALIZE)
declare
l_user_id number;
l_resp_id number;
l_resp_app_id number;
cursor get_user is
select user_id
from fnd_user
where user_name = ‘GSEHGAL’;
cursor get_resp is
select responsibility_id, application_id
from fnd_responsibility_vl
where responsibility_key = 'HIRING_MANAGER';
begin
open get_user;
fetch get_user into l_user_id;
close get_user;
open get_resp;
fetch get_resp into l_resp_id, l_resp_app_id;
close get_resp;
fnd_global.apps_initialize(l_user_id, l_resp_id,l_resp_app_id);
end;
Miscellaneous
Using Benefits Personal Actions
If you want to add an eligibility criteria for any function in the menu for different employees then personal actions feature of benefits can be used.
This is possible using the SSHR Actions eligibility model.
You need to create an eligibility profile for your Change Grade action using the Employment > Grade criteria for grades D, E and F, i.e. not including grades A, B, and C.
This eligibility profile then needs to be attached to a performance plan linked to your SSHR function for Change Grade; this is done in the 'Plan Eligibility' form task flowed from the 'Plan' form.
Note: The Miscellaneous tab in the Plan form is where you enter the SSHR function name for your Change Grade action. You need to ensure Plan Type has an 'Option Type' of 'Personnel Actions'.
Create a Reporting Group with 'Purpose' of 'Personnel Action' and a 'Function' equal to your Change Grade function name. (Note: If you are using one launch, points for all your Manager Actions then instead enter that function name here for the launching function from the menu.) In the components section enter the plan name you previously created.
Last step you need to set the system profiles:
HR:Allow use of eligibility for Self Service actions - Yes
HR:Allow processing of ineligible Self Service actions - No
HR:Run BENMNGLE when processing a Self Service action – Yes
Notes for Forms Personalization
Note: 279034.1 - Information About the Oracle Applications Form Personalization Feature in 11i
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=279034.1
Note: 468657.1 - How To Do Forms Personalization
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=468657.1
Forms Personalization - Get It While It's Hot!
http://blogs.oracle.com/schan/2006/07/18
Personalization Metalink Notes
429011.1
429136.1
550349.1
Add a security Profile to a responsibility
a) Create Security Profile.
b) Attach it to the responsibility using HR: Security Profile system profile option.
c) Set Profile option HR: User Type to HR with payroll user
d) Run Security List Maintenance Concurrent Program. If concurrent manager is down run following procedure
DECLARE
L_ERRBUF VARCHAR2(100);
L_RETCODE NUMBER;
BEGIN
PAY_PYUCSLIS_PKG.SUBMIT_SECURITY
(ERRBUF => L_ERRBUF,RETCODE => L_RETCODE,
P_EFFECTIVE_DATE => FND_DATE.DATE_TO_CANONICAL(SYSDATE),
P_GENERATION_SCOPE => 'SINGLE_PROF', -- FOR SINGLE PROFILE
P_BUSINESS_GROUP_ID => NULL,
P_SECURITY_PROFILE_ID => 6064, -- PROFILE ID OF THE SECURITY PROFILE YOU CREATED
P_WHO_TO_PROCESS => 'ALL', -- FOR CURRENT AND TERMINATED EMPLOYEES
P_ACTION_PARAMETER_GROUP_ID => NULL);
END;
e) Following tables will be populated
PER_PERSON_LIST
PER_ORGANIZATION_LIST
For complete reference on Security Profile, refer to the metalink note: 394083.1
If you want to add an eligibility criteria for any function in the menu for different employees then personal actions feature of benefits can be used.
This is possible using the SSHR Actions eligibility model.
You need to create an eligibility profile for your Change Grade action using the Employment > Grade criteria for grades D, E and F, i.e. not including grades A, B, and C.
This eligibility profile then needs to be attached to a performance plan linked to your SSHR function for Change Grade; this is done in the 'Plan Eligibility' form task flowed from the 'Plan' form.
Note: The Miscellaneous tab in the Plan form is where you enter the SSHR function name for your Change Grade action. You need to ensure Plan Type has an 'Option Type' of 'Personnel Actions'.
Create a Reporting Group with 'Purpose' of 'Personnel Action' and a 'Function' equal to your Change Grade function name. (Note: If you are using one launch, points for all your Manager Actions then instead enter that function name here for the launching function from the menu.) In the components section enter the plan name you previously created.
Last step you need to set the system profiles:
HR:Allow use of eligibility for Self Service actions - Yes
HR:Allow processing of ineligible Self Service actions - No
HR:Run BENMNGLE when processing a Self Service action – Yes
Notes for Forms Personalization
Note: 279034.1 - Information About the Oracle Applications Form Personalization Feature in 11i
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=279034.1
Note: 468657.1 - How To Do Forms Personalization
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=468657.1
Forms Personalization - Get It While It's Hot!
http://blogs.oracle.com/schan/2006/07/18
Personalization Metalink Notes
429011.1
429136.1
550349.1
Add a security Profile to a responsibility
a) Create Security Profile.
b) Attach it to the responsibility using HR: Security Profile system profile option.
c) Set Profile option HR: User Type to HR with payroll user
d) Run Security List Maintenance Concurrent Program. If concurrent manager is down run following procedure
DECLARE
L_ERRBUF VARCHAR2(100);
L_RETCODE NUMBER;
BEGIN
PAY_PYUCSLIS_PKG.SUBMIT_SECURITY
(ERRBUF => L_ERRBUF,RETCODE => L_RETCODE,
P_EFFECTIVE_DATE => FND_DATE.DATE_TO_CANONICAL(SYSDATE),
P_GENERATION_SCOPE => 'SINGLE_PROF', -- FOR SINGLE PROFILE
P_BUSINESS_GROUP_ID => NULL,
P_SECURITY_PROFILE_ID => 6064, -- PROFILE ID OF THE SECURITY PROFILE YOU CREATED
P_WHO_TO_PROCESS => 'ALL', -- FOR CURRENT AND TERMINATED EMPLOYEES
P_ACTION_PARAMETER_GROUP_ID => NULL);
END;
e) Following tables will be populated
PER_PERSON_LIST
PER_ORGANIZATION_LIST
For complete reference on Security Profile, refer to the metalink note: 394083.1
Wednesday, June 4, 2008
Setting up AME (Approval Management) in R12
I am working with a customer for upgrading there HCM to R12 from 11.5.10 . R12 include lot of technology changes and one of them includes AME. Newer version of AME, AME.B is also available in latest 11i releases.
AME.A was built in mod PL/SQL and AME.B is developed in OA Framework. Another major change in AME is that it uses RBAC (Role Based Access Control) mechanism to assign responsibilities to a user.
So if you open AME Approvals function with old responsibilities you might get one of the following errors.
The requested URL /pls/VIS/OracleSSWA.Execute was not found on this server.
OR
Can not access function AME_WEB_APPROVALS. This installation does not manage mode pl/sql. Please contact your System Administrator.
Both the errors saying that mod PL/SQL is not available (OracleSSWA is a program that executes the PL/SQL Package to generate the html output for pages).
Following are the steps to implement AME in R12 instance.
1. Login with SYSADMIN User.
2. Go to User Management Responsibility.
3. Click on Users and query for the username to which you want to assign AME Responsibilities.
4. One you query the User, Update the User from search results region and then click on Assign Roles.
5. Assign following Roles to the User
a) Approval Management Administrator
b) Approval Management System Administrator
c) Approval Management Process Owner
d) Approval Management System Viewer
e) Approval Management Business Analyst
Once finished with the Roles we will grant a permission set to the user from Functional Administrator Responsibility. Following are the steps for that
6. Go to Functional Administrator Responsibility => Security => Grants => Create Grant.
7. Enter Name of the "Grant" and effective start date. In Security Context select Grantee Type as Specific User and enter the User name whom you want to assign AME Responsibilities (Same user to which we assigned AME Roles).
8. Select Object as AME Transaction Types and Press Next.
9. Select Data Context as All Rows and assign "AME Calling Applications" permission set and Save the Information.
We need to set the AME: Installed Profile option as well to make it work. Set this profile option to Yes at application level (Human Resources).
Try to login with the user and you can see the following responsibilities listed there
Approvals Management Administrator
Approvals Management Business Analyst
What should existing AME Users do to make it work ?
It is pretty easy. Oracle provides a concurrent program "Approvals Management Post Upgrade Process". Run this program and thats all.
Drop a comment if you have any question regarding the above article. Hope this will help.
njoy
Gaurav
AME.A was built in mod PL/SQL and AME.B is developed in OA Framework. Another major change in AME is that it uses RBAC (Role Based Access Control) mechanism to assign responsibilities to a user.
So if you open AME Approvals function with old responsibilities you might get one of the following errors.
The requested URL /pls/VIS/OracleSSWA.Execute was not found on this server.
OR
Can not access function AME_WEB_APPROVALS. This installation does not manage mode pl/sql. Please contact your System Administrator.
Both the errors saying that mod PL/SQL is not available (OracleSSWA is a program that executes the PL/SQL Package to generate the html output for pages).
Following are the steps to implement AME in R12 instance.
1. Login with SYSADMIN User.
2. Go to User Management Responsibility.
3. Click on Users and query for the username to which you want to assign AME Responsibilities.
4. One you query the User, Update the User from search results region and then click on Assign Roles.
5. Assign following Roles to the User
a) Approval Management Administrator
b) Approval Management System Administrator
c) Approval Management Process Owner
d) Approval Management System Viewer
e) Approval Management Business Analyst
Once finished with the Roles we will grant a permission set to the user from Functional Administrator Responsibility. Following are the steps for that
6. Go to Functional Administrator Responsibility => Security => Grants => Create Grant.
7. Enter Name of the "Grant" and effective start date. In Security Context select Grantee Type as Specific User and enter the User name whom you want to assign AME Responsibilities (Same user to which we assigned AME Roles).
8. Select Object as AME Transaction Types and Press Next.
9. Select Data Context as All Rows and assign "AME Calling Applications" permission set and Save the Information.
We need to set the AME: Installed Profile option as well to make it work. Set this profile option to Yes at application level (Human Resources).
Try to login with the user and you can see the following responsibilities listed there
Approvals Management Administrator
Approvals Management Business Analyst
What should existing AME Users do to make it work ?
It is pretty easy. Oracle provides a concurrent program "Approvals Management Post Upgrade Process". Run this program and thats all.
Drop a comment if you have any question regarding the above article. Hope this will help.
njoy
Gaurav
Saturday, May 24, 2008
Useful SQLs/ PLSQLs
I am listing here some of the SQL Queries and PLSQL blocks which we use very often in Oracle Applications. It is cumbersome to see this data in application through delivered screens.
Lot of more stuff following this post. Any doubts regarding any query drop a comment. I will try to see if I have the right answer.
Lot of more stuff following this post. Any doubts regarding any query drop a comment. I will try to see if I have the right answer.
Friday, May 9, 2008
How to run FNDLOAD Command
I have seen some questions many times like how can I run FNDLOAD Command? What are all the entities I can upload/ download with FNDLOAD? What is the syntax for FNDLOAD.
Infact we can find out answers to all our questions by knowing how FNDLOAD works?
FNDLOAD use an lct file (kind of a conguration file) which contains which entity it is used for, what all attributes of the entity it will populate/ download. LCT files also have the SQL Statement using which it will download all the data and API using which it will upload the data into ORACLE APPLICATIONS. So if i want to find out that for which table and entity I can use FNDLOAD, I can search in the ".lct" files in the product code tree and search for table or my entity.
lct files are located in $PROD_TOP/patch/115/import/
Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ] ]
where logon is username/password[@connect] mode is either UPLOAD or DOWNLOAD configfile is the configuration file (lct file) datafile is the data file (for downloading specify any name. A file will be created with that name. For uploading specify you filename) entity is an entity name, or - to specify all values in an upload (Entity name) param is a NAME=VALUE string used for parameter substitution
Heres you can see lct files and choose what all parameters you should pass to run FNDLOAD. I am taking a simple example of a concurrent program.
DEFINE PROGRAM
KEY CONCURRENT_PROGRAM_NAME VARCHAR2(30)
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
CTX OWNER VARCHAR2(4000)
BASE LAST_UPDATE_DATE VARCHAR2(75)
TRANS USER_CONCURRENT_PROGRAM_NAME VARCHAR2(240)
BASE EXEC REFERENCES EXECUTABLE
BASE EXECUTION_METHOD_CODE VARCHAR2(1)
END PROGRAM
PROGRAM is an entity.
KEY Attributes in the entity should be passed for downloading the entity.
CTX Attributes are context parameters which are generally OWNER and LAST_UPDATE_DATE to determine whether to overwrite data or not.
These are generally used for uploadig the data.If owner is other than seed then data will be overriddenif Last_udpate_date is greater than the last_update_date in the database then data will be overridden.
TRANS: THis reperesent that this is transalatable attribute.
So for downloading program "BENLIMOD" I can use following ommand
$FND_TOP\bin\FNDLOAD apps/@ 0 Y DOWNLOAD $FND_TOP\\patch\115\import\afcpprog.lct benmngle.ldt PROGRAM APPLICATION_SHORT_NAME=BEN CONCURRENT_PROGRAM_NAME=BENLIMOD
Using lct file FNDLOAD create a LDT file which contains data. You can find these files in "$PROD_TOP/patch/115/import/US/". You can find LDT files at the above locations. These files contain the delivered data by oracle like concurrent program definitions, request groups etc.
Now I think we can ourself define our own FNDLOAD command for any of the entities for which FNDLOAD is available.
Hope this helps.
Infact we can find out answers to all our questions by knowing how FNDLOAD works?
FNDLOAD use an lct file (kind of a conguration file) which contains which entity it is used for, what all attributes of the entity it will populate/ download. LCT files also have the SQL Statement using which it will download all the data and API using which it will upload the data into ORACLE APPLICATIONS. So if i want to find out that for which table and entity I can use FNDLOAD, I can search in the ".lct" files in the product code tree and search for table or my entity.
lct files are located in $PROD_TOP/patch/115/import/
Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ] ]
where logon is username/password[@connect] mode is either UPLOAD or DOWNLOAD configfile is the configuration file (lct file) datafile is the data file (for downloading specify any name. A file will be created with that name. For uploading specify you filename) entity is an entity name, or - to specify all values in an upload (Entity name) param is a NAME=VALUE string used for parameter substitution
Heres you can see lct files and choose what all parameters you should pass to run FNDLOAD. I am taking a simple example of a concurrent program.
DEFINE PROGRAM
KEY CONCURRENT_PROGRAM_NAME VARCHAR2(30)
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
CTX OWNER VARCHAR2(4000)
BASE LAST_UPDATE_DATE VARCHAR2(75)
TRANS USER_CONCURRENT_PROGRAM_NAME VARCHAR2(240)
BASE EXEC REFERENCES EXECUTABLE
BASE EXECUTION_METHOD_CODE VARCHAR2(1)
END PROGRAM
PROGRAM is an entity.
KEY Attributes in the entity should be passed for downloading the entity.
CTX Attributes are context parameters which are generally OWNER and LAST_UPDATE_DATE to determine whether to overwrite data or not.
These are generally used for uploadig the data.If owner is other than seed then data will be overriddenif Last_udpate_date is greater than the last_update_date in the database then data will be overridden.
TRANS: THis reperesent that this is transalatable attribute.
So for downloading program "BENLIMOD" I can use following ommand
$FND_TOP\bin\FNDLOAD apps/
Using lct file FNDLOAD create a LDT file which contains data. You can find these files in "$PROD_TOP/patch/115/import/US/". You can find LDT files at the above locations. These files contain the delivered data by oracle like concurrent program definitions, request groups etc.
Now I think we can ourself define our own FNDLOAD command for any of the entities for which FNDLOAD is available.
Hope this helps.
Tuesday, April 15, 2008
Steps to Setup Basic Bonus Plan
Steps to Setup Basic ICD Plan for Bonus
a) Create a plan type of type Individual Compensation Distribution (Total Compensation => Programs and Plans => Plan Types)
b) Create a Plan using the above plan type and following information (Total Compensation => Programs and Plans => Plans)
Usage: May not be in program
Go to Not in Program Tab and enter details Enrollment Rate/ Frequency as per pay period and activity Reference Period as Annually
Go to the Details button and attach plan year period (plan will be available only in those time periods)
c) Define Plan Enrollment Requirements (Total Compensation => Programs and Plans => Plan Enrollment Requirements)
Enrollment Tab
Enrollment Type: Explicit
Allow Unrestricted => Checked
Enroll in Plan and Option => Checked
Enrollment Code => Current, Can Keep or Choose; New, Can Choose
Coverage Tab
Coverage Start Date=> Event
Coverage End Date => Event
Rates Tab
Rate Start Date=> Event
Rate End Date => Event
d) Define a Standard Rate for the Plan (Total Compensation => Rate/ Coverage Definitions => Standard Rates)
General Tab
Level: Plan
Compensation Object:
Activity Type: See from the list you require (for Bonus: Employer Payroll Contribution)
Tax Type: Any
Element Name and Element Input Value (Check Element and input value required if you attach the element here)
Calculation Tab
Enter Value at enrollment: Checked
Calculate for Enrollment: Checked
Give min, max values
Processing Information
Assign on Enrollment: Checked
Display on Enrollment: Checked
Compensation Category: Bonus
Value passed to Payroll: Defined Amount
That is all for setup.
Go to self-service and go to Individual Compensation Distribution and then go as application says . .. you can see this bonus. If you have attached element entry in standard rate, you can see the same in the bonus history also.
What happens internally
it process the unrestricted life event (Advanced Benefits) on the person and create enrollment and rates (and elements if defined).
njoy
Gaurav Sehgal
a) Create a plan type of type Individual Compensation Distribution (Total Compensation => Programs and Plans => Plan Types)
b) Create a Plan using the above plan type and following information (Total Compensation => Programs and Plans => Plans)
Usage: May not be in program
Go to Not in Program Tab and enter details Enrollment Rate/ Frequency as per pay period and activity Reference Period as Annually
Go to the Details button and attach plan year period (plan will be available only in those time periods)
c) Define Plan Enrollment Requirements (Total Compensation => Programs and Plans => Plan Enrollment Requirements)
Enrollment Tab
Enrollment Type: Explicit
Allow Unrestricted => Checked
Enroll in Plan and Option => Checked
Enrollment Code => Current, Can Keep or Choose; New, Can Choose
Coverage Tab
Coverage Start Date=> Event
Coverage End Date => Event
Rates Tab
Rate Start Date=> Event
Rate End Date => Event
d) Define a Standard Rate for the Plan (Total Compensation => Rate/ Coverage Definitions => Standard Rates)
General Tab
Level: Plan
Compensation Object:
Activity Type: See from the list you require (for Bonus: Employer Payroll Contribution)
Tax Type: Any
Element Name and Element Input Value (Check Element and input value required if you attach the element here)
Calculation Tab
Enter Value at enrollment: Checked
Calculate for Enrollment: Checked
Give min, max values
Processing Information
Assign on Enrollment: Checked
Display on Enrollment: Checked
Compensation Category: Bonus
Value passed to Payroll: Defined Amount
That is all for setup.
Go to self-service and go to Individual Compensation Distribution and then go as application says . .. you can see this bonus. If you have attached element entry in standard rate, you can see the same in the bonus history also.
What happens internally
it process the unrestricted life event (Advanced Benefits) on the person and create enrollment and rates (and elements if defined).
njoy
Gaurav Sehgal
Thursday, April 10, 2008
IE Crash while Opening Forms
Hi All
Issue: While opening forms from IE, IE crashes.
I have faced this issue many times. First after upgrading to IE7 .. then upgrading to Vista .. recently installed a proprietry software using JRE ..
Also Oracle Applications Forms client is not supported on vista unless you apply some recommended paches. You can refer metalink note: 285218.1 for all the details on all patchset levels.
So whats the easiest way
Copy the jvm.dll file from you JRE - bin - client to Jinitiator jvm.dll
i.e.
C:\Program Files\Java\ \bin\client\jvm.dll file to C:\Program Files\Oracle\\bin\hotspot\jvm.dll
It solved the problem for me in all above cases on lot of machines.
Another easy way is use firefox when you need to open the forms. Some times when you are using IE for long your forms will not get opened from Firefox. It will install jinitiator again and again when you open the forms.
Go to Control panell and remove all the versions of Jinitiator you have there. Now open firefox. Open the form you want to open. It will install Jinitiator and you can open the forms.
But you cant go to every user and ask them to install and use firefox for using Oracle Apps .. so i prefer the first workaround.
Hope this will help you as well.
njoy
Gaurav Sehgal
Issue: While opening forms from IE, IE crashes.
I have faced this issue many times. First after upgrading to IE7 .. then upgrading to Vista .. recently installed a proprietry software using JRE ..
Also Oracle Applications Forms client is not supported on vista unless you apply some recommended paches. You can refer metalink note: 285218.1 for all the details on all patchset levels.
So whats the easiest way
Copy the jvm.dll file from you JRE - bin - client to Jinitiator jvm.dll
i.e.
C:\Program Files\Java\
It solved the problem for me in all above cases on lot of machines.
Another easy way is use firefox when you need to open the forms. Some times when you are using IE for long your forms will not get opened from Firefox. It will install jinitiator again and again when you open the forms.
Go to Control panell and remove all the versions of Jinitiator you have there. Now open firefox. Open the form you want to open. It will install Jinitiator and you can open the forms.
But you cant go to every user and ask them to install and use firefox for using Oracle Apps .. so i prefer the first workaround.
Hope this will help you as well.
njoy
Gaurav Sehgal
Subscribe to:
Posts (Atom)
