How to Detect a Life Event for an Employee When A Dependent Ages Out in Oracle Advanced Benefits (OAB)

Goal:

When a dependent ages out, how can a life event be detected on the employee?

Scenario :

• Children of employees are not eligible for benefits after reaching the age of 18.
• A dependent eligibility profile was created and attached to the plan (example: Medical).
• The Maintain Designee Eligibility process is run to recognize that a dependent is no longer eligible, but the preference is to have a life event triggered on the employee's record when the child becomes ineligible. This is so the employee can change their options if needed (example: from Employee+Child to Employee Only).

Solution:

To implement the solution, please execute the following steps:
1. Create a Life event:
Navigation: Total Compensation > General Definitions > Additional Setup > Life Event Reasons

Name: Dependent Ages Out
Type: Personal
Person Changes button | Define Person Changes button
Name: Dependent Ages Out
Table Name: BEN_ELIG_CVRD_DPNT_F
Column Name: CVG_THRU_DT
Old Value : No Value
New Value : Any Value

Then go back to the Person Changes form and add 'Dependent Ages Out' and save.

2. Add this life event to your program.
Navigation: Total Compensation > Programs and Plans > Program Enrollment Requirements > Timing > Life Event.

The above solution is for clients using Oracle Advanced Benefits (OAB).

3. Run the Maintain Designee Eligibility process.

Navigate to Processes and Reports > Submit Processes and Reports > Single Request > Maintain Designee Eligibility. This is run for the employee; not for the dependent.

This should detect that the dependent is no longer eligible, and create a detected life event for the employee.

 

If using Oracle Standard Benefits (OSB), then process an Unrestricted life event via the Non-Flex Enrollment form.
This should end coverage for the dependent, and allow the employee to make election changes.

Metalink Reference - Note ID -279262.1

Absence and Oracle Time and Labor(OTL) Intergretion Setup

 

How to get details about patch applied in Oracle Applications by OAM & SQL

There are some tables in oracle apps (AD tables especially) involved when applying patches. Some of them are very useful when we need specific information about patch already applied. I will show the main tables and afterwards some handy related SQL’s to retrieve patch applied details and how we can also get all this information via OAM. AD_APPLIED_PATCHES – The main table when we are talking about patches that applied in Oracle Apps. This table holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (e.g. "merged" patches), then they are considered distinct and this table will therefore hold 2 records (eTRM). I also found that if the applications tier node is separate from the concurrent manager node, and the patch applied on both nodes, this table will hold 2 records, one for each node. AD_PATCH_DRIVERS – This table holds information about all patch drivers included in specific patch. For example if patch contain only one unified driver like u[patch_name].drv then ad_patch_drivers will hold 1 record. On the other hand, if patch contain more than 1 driver, for example d[patch_name].drv and c[patch_name].drv, this table will hold 2 records. AD_PATCH_RUNS – holds information about each execution of adpatch for a specific patch driver. In case a patch contains more than one driver, this table will hold a record for each driver. This table also holds one record for each node the patch driver has been applied on (column APPL_TOP_ID). AD_PATCH_RUN_BUGS – holds information about all the bugs fixed as a part of specific run of adpatch. AD_BUGS – this table holds information about all bug fixes that have been applied. We have 2 options to view applied patch information:1) via OAM – Oracle Applications Manager 2) Via SQL queries With OAM it’s easy and very intuitive, from OAM site map -> “Maintenance” tab -> “Applied Patches” under Patching and Utilities. Search by Patch ID will get all information about this patch; In addition, drill down by clicking on details will show the driver details. For each driver we can use the buttons (Timing Details, Files Copied, etc.) to get more detailed information. With SQL we can retrieve all the above information, sometimes more easily. For example: How to know which modules affected by specific patch? With OAM: 1) search patch by Patch ID 2) click on Details 3) For each driver click on “Bug Fixes” and look on product column. With SQL: Run the following query, it will show you all modules affected by specific patch in one click… select distinct aprb.application_short_name as "Affected Modules" from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_patch_run_bugs aprb where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and apr.patch_run_id = aprb.patch_run_id and aprb.applied_flag = 'Y' and aap.patch_name = '&PatchName'; Another SQL will retrieve basic information regarding patch applied, useful when you need to know when and where (node) you applied specific patch: select aap.patch_name, aat.name, apr.end_date from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_appl_tops aat where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and aat.appl_top_id = apr.appl_top_id and aap.patch_name = '&PatchName'; To check if specific bug fix is applied, you need to query the AD_BUGS table only. This table contains all patches and all superseded patches ever applied: select ab.bug_number, ab.creation_date from ad_bugs ab where ab.bug_number = '&BugNumber';

Creating Extra Information Types as a Self-Service function

Reference URL - http://oracleappssol.blogspot.com/2012/08/creating-extra-information-types-as.html#!/2012/08/creating-extra-information-types-as.html


http://waqasora.blogspot.com/2012/12/how-to-create-extra-information-type.html

Delete User Hook in Oracle HRMS

select * from hr_api_hook_calls where call_package like 'XX%'

declare
ln_ovn number := 2;
begin
hr_api_hook_call_api.delete_api_hook_call
  (p_validate                          =>  false,
   p_api_hook_call_id                  => 1140,
   p_object_version_number             => ln_ovn
  );
end;

When Processing a New Hire Life Event, Automatic Plans Are Not Assigned - Oracle Advanced Benefits(OAB)

Symptoms
 Find that after running the New Hire life event the Automatic plans are not assigned to the employee.
 EXPECTED BEHAVIOR

Expect that after running the New Hire life event, employees are automatically enrolled in the plans with Automatic Enrollment Method.

Steps To Reproduce:
 The issue can be reproduced at will with the following steps:
 1. Login to HRMS Manager Responsibility.
 2. Process a New Hire life event for an employee. Automatic plans are not assigned.

 Cause
 The issue is caused by the following setup: Automatic Enrollment settings for the Plans.
 The setup causes the issue because correct Enrollment code was not used.
Solution
 To implement the solution, please execute the following steps:
 1. Go into the responsibility HRMS Manager Responsibility.
 2. Navigate to Total Compensation > Plans and Programs > Plan Enrollment Requirements form and confirm the following settings:
General tab > Plan tab:
Method = BLANK
General tab > Plan tab:
Enrollment Code = BLANK
Life Event tab > Option tab > Enrollment drop down:
Life Event = New Hire
Option = Enrolled
Enrollment Code = Current Nothing; New Assign (Automatic)
Automatic Enrollment Flag = NOT CHECKED
01-Oct-2006 to EOD
Option = Enrolled
Enrollment Code = Current Nothing; New Assign (Automatic)
Automatic Enrollment Flag = CHECKED
01-Jan-1965 to 31-Sep-2006
Option = Enrolled01Oct2006
Enrollment Code = Current Nothing; New Assign (Automatic)
Automatic Enrollment Flag = CHECKED
01-Oct-2006 to EOD
Option = Contribute
Enrollment Code = Current, Can Keep or Choose; New, Can Choose
Automatic Enrollment Flag = NOT CHECKED
01-Jan-65 to EOD
 Option = Contribute01oct2006
 Enrollment Code = Current, Can Keep or Choose; New, Can Choose
 Automatic Enrollment Flag = NOT CHECKED
 01-Oct-2006 to EOD
 Option = Waive
 Enrollment Code = Current, Can Keep or Choose; New, Can Choose
 Automatic Enrollment Flag = NOT CHECKED
 01-Jan-1965 to EOD
 4. Retest the issue.
 5. Migrate the solution as appropriate to other environments.

Enrollment of dependents automatically to option based on Life event Detection in Oracle Advanced Benefits.

Business Requirement – As soon as employee gets promoted, if he has his dependents enrolled to options where he is paying for them from his pocket, then he should get enrolled to equivalent employer cost option with all the dependents carried forward.

Based on eligibility, employee will see options for selection and eligible dependents.
Following options will be set in the system:
1. Employee Only
2. Employee & Spouse
3. Employee & Child(ren)
4. Employee & Family
5. Employee & Spouse (Employee Charges)
6. Employee & Child(ren)(Employee Charges)
7. Employee & Family(Employee Charges)

Employees in grade 1 to 10 can see following options only
1. Employee Only
2. Employee & Spouse (Employee Charges)
3. Employee & Child(ren)(Employee Charges)
4. Employee & Family(Employee Charges)

Employee in grade 11 to 16 can see following options
1. Employee Only
2. Employee & Spouse
3. Employee & Child(ren)
4. Employee & Family

so when employee gets promoted, his options also needs to be changed.

Design – It will be scheduled CP. It will run every night. This CP will move old dependents from to new options.

Approach:1. Life event will get detected
2. Process the Promotion Life Event. Based on Life Event, Automatic Enrollment Fast Formula  
    will  be written. This fast formula will do automatic enrollment of Options. For Example if 
    employee is in Employee & Spouse (Employee charge), FF will enroll employee to Employee  
    &  Spouse which is employer charge
3. Now we need to add dependents to new option. Basically this is nothing but moving dependents
    from Employee & Spouse (Employee charge) option to Employee & Spouse.As of now I have not
    tested if same FF can be used to add dependents also. I feel that is possible.

Here one CP needs to be created.

Sample Script:

select * from BEN_ELIG_DPNT where ELIG_DPNT_ID = 34328

DECLARE
   l_out_elig_cvrd_dpnt_id      NUMBER;
   l_out_effective_start_date   DATE;
   l_out_effective_end_date     DATE;
   l_out_ovn                    NUMBER;
BEGIN
   ben_elig_dpnt_api.process_dependent_w
                       (p_validate                      => 'N',
                        p_elig_dpnt_id               => 34328,
                        p_business_group_id      => 82,
                        p_effective_date             => TRUNC(SYSDATE),
                        p_cvg_strt_dt                  => TRUNC(SYSDATE),
                        p_cvg_thru_dt                 => NULL,
                        p_datetrack_mode           => hr_api.g_insert,
                        p_elig_cvrd_dpnt_id       => l_out_elig_cvrd_dpnt_id,
                        p_effective_start_date     => l_out_effective_start_date,
                        p_effective_end_date        => l_out_effective_end_date,
                        p_object_version_number => l_out_ovn,
                        p_multi_row_actn             => 'Y'
                       );
END;

4. In the same program call "Close Action Items Process" seeded concurrent program to close the open action items.
5. Check the enrollment results.
6 .Cheersss...
 

Plan Enrollment Results report in Oracle Advanced Benefits (OAB)

SELECT DISTINCT per.full_name pl_full_name, per.first_name pl_first_name,
                ben_batch_utils.get_pl_name (pen.pl_id,
                                             :p_business_group_id,
                                             :p_report_end_date
                                            ) pl_pl_name,
                DECODE
                   (pen.oipl_id,
                    NULL, NULL,
                    ben_batch_utils.get_opt_name (pen.oipl_id,
                                                  :p_business_group_id,
                                                  :p_report_end_date
                                                 )
                   ) pl_opt_name,
                per.person_id pl_person_id,
                pen.orgnl_enrt_dt pl_orgnl_enrt_dt,
                pen.enrt_cvg_strt_dt pl_enrt_dt, per.last_name pl_last_name,
                pen.uom pl_uom
           FROM per_all_people_f per,
                per_all_assignments_f asg,
                ben_prtt_enrt_rslt_f pen,
                ben_per_in_ler pil
          WHERE (:p_person_id IS NULL OR per.person_id = :p_person_id)
            AND :p_report_end_date BETWEEN per.effective_start_date
                                       AND per.effective_end_date
            AND per.business_group_id = :p_business_group_id
            AND asg.person_id = per.person_id
            AND asg.assignment_type <> 'C'
            AND asg.primary_flag = 'Y'
            AND pen.person_id = per.person_id
            AND pen.prtt_enrt_rslt_stat_cd IS NULL
            AND pen.business_group_id = :p_business_group_id
            AND (   pen.enrt_cvg_thru_dt >= pen.effective_end_date
                 OR pen.enrt_ovridn_flag = 'Y'
                )
            AND (   :p_report_start_date BETWEEN pen.enrt_cvg_strt_dt
                                             AND enrt_cvg_thru_dt
                 OR :p_report_end_date BETWEEN pen.enrt_cvg_strt_dt
                                           AND enrt_cvg_thru_dt
                 OR (    :p_report_start_date >= pen.enrt_cvg_strt_dt
                     AND :p_report_end_date <= enrt_cvg_thru_dt
                    )
                 OR (    :p_report_start_date <= pen.enrt_cvg_strt_dt
                     AND :p_report_end_date >= enrt_cvg_thru_dt
                    )
                )
            AND (   (    pen.effective_end_date < pen.enrt_cvg_thru_dt
                     AND (   :p_report_start_date
                                BETWEEN pen.effective_start_date
                                    AND pen.effective_end_date
                          OR :p_report_end_date BETWEEN pen.effective_start_date
                                                    AND pen.effective_end_date
                          OR (    :p_report_start_date >=
                                                      pen.effective_start_date
                              AND :p_report_end_date <= pen.effective_end_date
                             )
                          OR (    :p_report_start_date <=
                                                      pen.effective_start_date
                              AND :p_report_end_date >= pen.effective_end_date
                             )
                         )
                    )
                 OR pen.effective_end_date >= pen.enrt_cvg_thru_dt
                )
            AND pen.sspndd_flag = 'N'
            AND pen.per_in_ler_id = pil.per_in_ler_id
            AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
            AND pil.person_id = per.person_id
            AND ben_batch_utils.get_pl_name (pen.pl_id,
                                             :p_business_group_id,
                                             :p_report_end_date
                                            ) LIKE 'Life%UAE%'

ORA-04068: existing state of packages () has been discarded in Oracle Advanced Benefits

Goal

How does one resolve the below errors occurring when attempting to process life events after applying an upgrade?
ORA-04068/ORA-04065/ORA-06508/ORA-06512 error on package "APPS.BEN_PTNL_LER_FOR_PER_API" after RUP5 upgrade.
When trying process life events "New Hire", "Termination", etc. - we get error which says:
ORA-04068: existing state of packages () has been discarded
ORA-04065: not executed, altered or dropped stored procedure "APPS.BEN_PTNL_LER_FOR_PER_API"
ORA-06508: PL/SQL: could not find program unit being called:"APPS.BEN_PTNL_LER_FOR_PER_API"
ORA-06512: at "APPS.BEN_ON_LINE_LF_EVT", line 2097

Fix

Issue apparently occurs where valid packages are generating "invalid package" errors.
Clear out system as much as possible, including bouncing the database, application server processes, workflow processes, concurrent managers, etc.
Shut down the application server processes, clear the Java cache, shutdown the database, shutdown the database listener, restart the database listener, and restart the database.
No errors occur after following these steps

Metalink Reference - How to Resolve Several ORA Errors Occurring After RUP5 When Attempting to Process Life Events [ID 1511181.1]

Solution:

Perform the following as SYSDBA:
1. ALTER SYSTEM set _disable_fast_validate=TRUE scope=spfile;
2. Shut down the database:
   SHUTDOWN IMMEDIATE
3. Start the database in upgrade mode:
   STARTUP UPGRADE
4. Oracle provides scripts in $ORACLE_HOME/rdbms/admin that, when run as sys, will invalidate and revalidate all PL/SQL objects so any timestamp mismatches should be resolved. The script utlirp.sql invalidates all PL/SQL based objects, recreates STANDARD and DBMS_STANDARD, invalidates all views and synonyms dependent on now invalid objects and then does some clean up. The script utlrp.sql calls UTL_RECOMP.RECOMP_PARALLEL which performs dependency based recompilation, in parallel where resources allow. Please use these two scripts:
 a) Invalidate all the objects - utlirp.sql
 b) Recompile all the objects - utlrp.sql
5. ALTER SYSTEM set _disable_fast_validate=FALSE scope=spfile;
6. Shut down the database:
   SHUTDOWN IMMEDIATE
7. Start the database:
   STARTUP
8. Retest the issue.
9. Check with dba if you have this 11.1.0.7 patch 7284151 on your instance ?
reference : note 7284151.8 Bug 7284151 - Dependency timestamp mismatch after recompiling invalid packages (Doc ID 7284151.8)