Oracle Advanced Benefits APIs Conversion

Oracle Advanced Benefits APIs

Process	API NAME
Create Potential Life Event:	ben_ptnl_ler_for_per_api – Unprocessed
Update Potential Life Event:	ben_ptnl_ler_for_per_api
Create Person Life Event	ben_Person_Life_Event_api
Create Participant Enrollment Event	ben_PRTT_ENRT_RESULT_api
Create Participant Rate Value	ben_prtt_rt_val_api
Create Eligible Covered Dependents	ben_ELIG_CVRD_DPNT_api
Create Beneficiary	ben_PLAN_BENEFICIARY_api
Update Person Life Event	ben_Person_Life_Event_api

 

Conversion Sequencing Steps:

 

Ø Step 1: Create Potential Life Event for Current LE

Ø Step 2: Update Potential Life Event

Ø Step 3: Create Life Event

Ø Step 4: Create Participant Enrollments

Ø Step 5: Create Elements & Rates

Ø Step 6: Create Eligible Covered Dependents

Ø Step 7: Create Eligible Beneficiaries

Ø Step 8: Close Life Event

Ø Step 9: Create Potential Life Event for Conversion LE

Rate Details in Oracle Advanced Benefits

SELECT pap.employee_number, pln.NAME "Plan", opt.NAME "Option",
       prtt.enrt_cvg_strt_dt "Coverage Start Date",
       prtt.bnft_amt "Coverage Amount", rte.rt_val "Defined Amount",
       rte.cmcd_rt_val "Communicated Amount", ann_rt_val "Annual Rate Value",
       acty_typ_cd "Activity Type", tx_typ_cd "Tax Type",
       rt_typ_cd "Rate Type"
  FROM ben_prtt_enrt_rslt_f prtt,
       per_all_people_f pap,
       ben_oipl_f oipl,
       ben_pl_f pln,
       ben_opt_f opt,
       ben_prtt_rt_val rte
 WHERE pap.person_id = prtt.person_id
   AND prtt.oipl_id = oipl.oipl_id
   AND oipl.pl_id = pln.pl_id
   AND opt.opt_id = oipl.opt_id
   AND prtt.prtt_enrt_rslt_id = rte.prtt_enrt_rslt_id
   AND prtt.prtt_enrt_rslt_stat_cd IS NULL
   AND prtt.sspndd_flag = 'N'
   AND pap.person_id = prtt.person_id
   AND SYSDATE BETWEEN prtt.enrt_cvg_strt_dt AND prtt.enrt_cvg_thru_dt
   AND enrt_cvg_thru_dt <= prtt.effective_end_date
   AND SYSDATE BETWEEN pap.effective_start_date AND pap.effective_end_date
   AND SYSDATE BETWEEN pln.effective_start_date AND pln.effective_end_date
   AND SYSDATE BETWEEN oipl.effective_start_date AND oipl.effective_end_date
   AND SYSDATE BETWEEN opt.effective_start_date AND opt.effective_end_date
   AND SYSDATE BETWEEN rte.rt_strt_dt AND rte.rt_end_dt
--and pln.name = ('Health Savings Account')
   AND opt.NAME <> 'Waive'
   AND employee_number = '17578'
   AND acty_typ_cd = 'ERPYC'
 

Total Compensation Statement Sample Setup & Administration in Oracle Advanced Benefits

Please refer to Total Compensation Statement Sample Setup & Administration [ID 376680.1]
 

Send the current updated or inserted record in Oracle Alert

select ler.name,ppf.first_name,ppf.email_address,ppl.dtctd_dt
INTO &life_event,&emp_name,&email,&dete_date
from per_all_people_f ppf,
ben_ler_f ler,
ben_ptnl_ler_for_per ppl
where 1=1
and ppl.rowid = :ROWID
and ppf.person_id = ppl.PERSON_ID
and ler.ler_id = ppl.ler_id
and trunc(sysdate) between ler.effective_start_date
and ler.effective_end_date
and trunc(sysdate) between ppf.effective_start_date
and ppf.effective_end_date
--and ppf.full_name like '%Jaspal%'
and ler.business_group_id = ppf.business_group_id
and ppl.dtctd_dt is not null
--and ppl.ptnl_ler_for_per_stat_cd <> 'VOIDD'
and ppl.ptnl_ler_for_per_stat_cd = 'DTCTD'

Mandatory Attachment - Through Userhook - Leave of Absence Oracle Self Service(SSHR)

            BEGIN
                         SELECT COUNT (*)
                           INTO l_attachment_count
                           FROM hr_api_transactions a,
                                hr_api_transaction_steps b,
                                fnd_attached_documents c
                          WHERE a.transaction_id = b.transaction_id
                            AND TRUNC (a.creation_date) = p_effective_date
                            AND a.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
                            AND c.entity_name='PER_ABSENCE_ATTENDANCES'
                            AND a.status <> 'D'
                            AND a.creator_person_id = p_person_id
                            AND b.information5 = p_absence_attendance_type_id
                            AND to_char(apps.fnd_date.canonical_to_date(b.information1))=p_date_start
                            AND to_char(apps.fnd_date.canonical_to_date(b.information2))=p_date_end
                            AND LTRIM (RTRIM (SUBSTR (c.pk1_value,
                                                      INSTR (c.pk1_value, '_', 1) + 1,
                                                      10
                                                     )
                                             )
                                      ) = a.transaction_id;
                      Exception when others then
                         l_attachment_count := 0;
                      END;
                      --
                      IF nvl(l_attachment_count,0) = 0
                      THEN
                         xx_userhook_pkg.raise_hr_warning('Please attach the Document');
                      ELSE
                         NULL;
                      END IF;
------------
   PROCEDURE raise_hr_warning (l_mesg IN VARCHAR2)
   IS
   BEGIN
      hr_utility.set_message (801, 'HR_ELE_ENTRY_FORMULA_HINT');
      hr_utility.set_message_token ('FORMULA_TEXT', l_mesg);
      hr_utility.raise_error;
   END raise_hr_warning;
--------------

Defining And Using Full Profile System Extracts in Oracle Advanced Benefits(OAB)

Abstract:

The purpose of this document is to describe how to create and use User Defined Full Profile
System Extracts. System Extracts are used to transfer information from HRMS to third party benefit providers.

Purpose:
Defining and Using Full Profile System Extracts

This can be provided as a full profile system extract, or changes only extract. We would be looking into
defining full profile system extracts in detail in this document.

Note:
For details on different types of System Extracts, please refer to the “Types of Extracts”,
which is available in the Note: 367395.1 - Benefits Reporting Focus Area.

For details on setting up Changes Only Extract, please refer to
“Defining and Using Changes Only System Extracts” which is available in the Note: 367395.1- Benefits Reporting Focus Area.

Please refer note -  373293.1 on Metalink.

Make sure that utl file is defined

select * from v$parameter where name like '%utl_file%'

Employee's Potential Life Event Details in Oracle Advanced Benefits(OAB)

To get the Employee's Potential Life Event Details

Modules Involved : HRMS, OAB

Purpose    : To get the Employee's Potential Life Event Details
Description      : It gives the Life Event details for an employee, the Status,the date the life event was Started,
       the date the life event was closed (Processed), as well as Void or Back Out Dates.
Needs two parameters (Person_Id, Business Group ID)
****************************************************************** */
SELECT  PPF.PERSON_ID
       , PPF.FULL_NAME
       , bplp.lf_evt_ocrd_dt                  eventDate
       , BL.NAME || ' (' || BL.LER_ID  || ')' LIFEEVENT
       , BPLP.PTNL_LER_FOR_PER_STAT_CD  STATUS
       , bplp.NTFN_DT                         notified
       , bplp.DTCTD_DT                        detected
       , bplp.UNPROCD_DT                      unprocessed
       , bplp.PROCD_DT                        processed
       , BPLP.VOIDD_DT                        VOIDED
       , bplp.lf_evt_ocrd_dt                  Occured
      FROM
         ben_ptnl_ler_for_per bplp
       , BEN_LER_F            BL
       , PER_ALL_PEOPLE_F     PPF
    WHERE sysdate between ppf.effective_start_date and ppf.effective_end_date
        AND ppf.person_id = bplp.person_id
        AND sysdate between bl.effective_start_date and bl.effective_end_date
        AND bplp.ler_id = bl.ler_id
        and PPF.BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID
        AND PPF.PERSON_ID = :Person_ID
      ORDER BY 1 desc;

How to send FYI notifications to people who have role In SSBEN

Solution

To enable Self-Service Benefits Enrollment Notifications:
1. Query the function that you want to update. Choose one of the following:
Employee Self-Service (BEN_SS_BNFT_ENRT)
Manager Self-Service (BEN_SS_MGR_ENRT)
2. Choose the Form tab.
3. In the Parameters field, verify that the following parameter appears:
displayDate=N&allowEnrt=Y&sessionDate=&sendFYINotification=N&wf
ProcessName=FYI_NOTIFICATION_PRC.
Note: If you do not see this string, you must manually enter the parameter.
4. To enable workflow notifications, replace sendFYINotification=N with sendFYINotification=Y.
5. Replace the wfProcessName=FYI_NOTIFICATION_PRC portion of the parameter with your custom process name.
6. Save your work.

Finally, you must associate a user to the role that will receive the workflow notification:

7. Create a Benefits Administrator role using Transaction Maintenance -> Roles
8. Assign that role to more than 1 user (should be of the same business group)
9. Make some benefit change for an employee using Employee Self Service
10. Login with the details of the Benefits administrator and check for notifications using Workflow User Web Applications -> Notifications