Create Potential Life Event in Oracle Advanced Benefits(OAB)

DECLARE

   v_ptnl_ler_for_per_id   NUMBER;

   v_obv_number            NUMBER;

   v_error                 VARCHAR2 (4000);

BEGIN

   DBMS_OUTPUT.ENABLE (1000000);

   apps.ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per

                             (p_validate                      => FALSE,

                              p_ptnl_ler_for_per_id           => v_ptnl_ler_for_per_id,

                              p_lf_evt_ocrd_dt                => SYSDATE

                                                            --v_lf_evt_ocrd_dt

                                                                        ,

                              p_ptnl_ler_for_per_stat_cd      => 'UNPROCD',

                              p_ler_id                        => 17,

                              p_person_id                     => 2596,

                              p_business_group_id             => 0,

                              p_unprocd_dt                    => SYSDATE,

                              p_ntfn_dt                       => SYSDATE,

                              p_object_version_number         => v_obv_number,

                              p_effective_date                => SYSDATE

                             );

EXCEPTION
   WHEN OTHERS
   THEN
      v_error := SUBSTR (SQLERRM, 1, 250);
      DBMS_OUTPUT.put_line (v_error);
END;

Self Service: Using Eligibility Profiles to determine access in Employee Self Service

General Requirement - I am trying out a general approach to restricting access to functions by eligibility profile.

For example, let's say we have an organization with 6 grades A,B,C,D,E,F where A is lowest and F is highest. I want to prevent a user from being able to use the Change Grade function in self service is they are grade A, B or C.

Solution:

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 point 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

How to Set Eligibility so that an Employee is not Eligible for a Spousal Life Insurance Plan unless they have a Spouse

How to set eligibility in Standard and Advanced Benefits so that an employee is not found eligible for the Spousal Life Insurance plan unless they have an eligible Spouse contact.

Solution:
Navigations:

• Total Compensation > Programs and Plans > Plans
• Total Compensation > Programs and Plans > Plan Enrollment Requirements
• People > Enter & Maintain > query employee > Others button > Contacts

Requirement:
Employees/Participants should not be eligible for the Spousal Life Insurance Plan unless they have a Spouse entered on the Contact form.
Setup:

1. Go to to Plans form for the Spousal Life Insurance plan.
2. On the General tab, select Family Member Code 'Check Designation Requirements'
3. Go to the Plan Enrollment Requirements form for the Spousal Life Insurance plan.
4. On the General tab > Plan tab > Designation Requirements button, enter:

Group Relationship:  Spouse 

Type:  Dependents 

Min: 1 

Max: 1 

Relationship Type:  Spouse

Delete Option from Plan on Test Instance before Go Live - Oracle Advanced Benefits(OAB)

select * from ben_prtt_enrt_rslt_f where oipl_id = 9002
select * from per_all_people_f where person_id = 1497
select * from ben_oipl_f where oipl_id = 9002
select * from ben_opt_f where opt_id = 9003
delete from ben_prtt_enrt_rslt_f where oipl_id = 9002

select * from ben_elig_per_elctbl_chc where oipl_id = 9002
delete from ben_elig_per_elctbl_chc where oipl_id = 9002

Note - This is for my future reference. Oracle does not recommend it.

Disable The Fn + Function Key Combination on HP Laptops

Really wanted to share this.

 

In my case I changed it from Enabled to Disabled and it worked.

On most HP and Compaq notebook computers, it is necessary to press and hold the function key (fn) while pressing one of the f1 through f12 keys to activate the default functions; such as, increasing or decreasing the brightness of the display, the sound volume, sleep, keyboard lock, etc. On the HP Envy notebooks and other notebooks with advanced BIOS options, the function keys can be configured so that it is not necessary to press and hold the fn key.

Figure 1: Location of function keys

1 - Function Keys f1 through f12

2 - Configurable Function key fn

Disable or enable fn in the BIOS

To disable, or enable, the function key (fn) in the BIOS, do the following steps.

1. Press the power button to turn on the computer.
2. Press the f10 key to open the BIOS setup window.
3. Press the right-arrow or left-arrow keys to navigate to the System Configuration option.
   Figure 2: BIOS setup window

   
4. Press the up-arrow or down-arrow keys to navigate to the Action Keys Mode option, and then press the enter key to display the Enable / Disable menu.
5. Select the desired mode:

   Disabled : Requires pressing and holding the function key (fn) while pressing one of the f1 through f12 keys to use the action indicated on the action key.
   For example, on some computer models, if the Action Keys Mode is Disabled , pressing the f11 key will minimize and maximize a web browser if open. Alternatively, pressing and holding the fn + f11 keys will mute the sound.
   The action keys may vary depending on the model of notebook.
   Enabled : Requires pressing only one of the f1 through f12 keys to use the action as indicated on the action key.
   For example, on some computer models, if the Action Keys Mode is Enabled , pressing f11 will mute the sound as indicated on the action key. Alternatively, pressing and holding fn + f11 will minimize and maximize a web browser if open.
   The action keys may vary depending on the model of notebook.
6. Press the f10 key to save the selection and restart the computer.

The function key (fn) option can be reset at any time.

Oracle Advanced Benefits(OAB) Ad-hoc Queries

1. Enrollment Results:

 

SELECT DECODE (TO_CHAR (enrt_cvg_thru_dt, 'DD-MM-RRRR'),
               '31-12-4712', 'Add',
               'Del'
              ),
       enrt_cvg_thru_dt
  FROM ben_prtt_enrt_rslt_f pen
 WHERE business_group_id = 82
   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 IN (1512,1307)
 
 
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

Reference - http://oracle-apps-tech.blogspot.com/2009/03/oracle-advanced-benefit-oab-queries.html
 

Enrolling Dependent from Benefits self Service in Oracle Advanced Benefits(OAB)

In Oracle Advanced Benefits, if employee select Employee + Spouse option through SSHR, system should ask him to select spouse from available dependents for enrollment.

Solution:
1. Go to Program Enrollment Requirement
2. Select Designation Level as Plan Type in Program
3. Select Plan Type name
4. Select Plan Type Dependent Designation as Required
5. Select Dependent Coverage Start Date and Dependent Coverage End Date
6. Click Dependent Change of Life Event
7. Add Life Event e.g. New Hire and select Change Dependent Coverage as May Either Add or Remove Dependents
8. Save the record
9. Retest Issue from self service.

Enrollment Codes with Descriptions for Standard and Advanced Benefits(OAB)

List of Enrollment Codes
 

Current, Can Keep or Choose; New, Nothing: If a person is currently enrolled in this compensation object, the person can keep their current elections or make new elections. If a person is not yet enrolled, the person cannot make an election.
Current, Can Keep or Choose; New, Can Choose: If a person is currently enrolled in this compensation object, the person can keep their current elections or make new elections. If a person is not yet enrolled, the person can make new elections.
Current, Can Keep or Choose But Starts New; New, Can Choose: If a person is currently enrolled in this compensation object, the person can keep their current elections or make new elections. The coverage ends at the end of the plan year and restarts the next day so that the participant must explicitly re-elect each year, even though the coverage amount may stay the same. If a person is not yet enrolled, the person can make new elections.

Note: You can only select this code from the Program or Plan Enrollment Requirements windows at either the plan type in program, plan in program, or plan level based on a life event. It is recommended that you select this code only for the Open enrollment life event.

Current, Choose Only; New, Can Choose: If a person is currently enrolled in this compensation object, the person must make an explicit election to stay enrolled. If a person is not yet enrolled, the person can make new elections.
Current, Choose Only; New, Nothing: If a person is currently enrolled in this compensation object, the person must make an explicit election to stay enrolled. If a person is not yet enrolled, the person cannot make an election.
Current, Keep Only; New, Can Choose: If a person is currently enrolled in this compensation object, the person must keep their current elections. If a person is not yet enrolled, the person can make new elections.
Current, Keep Only; New, Nothing: If a person is currently enrolled in this compensation object, the person must keep their current elections. If a person is not yet enrolled, the person cannot make an election.
Current, Lose Only; New, Can Choose: If a person is currently enrolled in this compensation object, the person must de-enroll from their current elections. If a person is not yet enrolled, the person can make new elections.
Current, Lose Only; New, Nothing: If a person is currently enrolled in this compensation object, the person must de-enroll from their current elections. If a person is not yet enrolled, the person cannot make new elections.

Automatic Codes:
Current, Assign; New, Assign (Automatic): If the enrollment method code is Automatic, both current and new enrollees automatically enroll and cannot de-enroll.
Current, Nothing; New, Assign (Automatic): If the enrollment method code is Automatic, current enrollees automatically de-enroll; new enrollees automatically enroll and cannot de-enroll.
Current, Assign; New, Nothing (Automatic): If the enrollment method code is Automatic, current enrollees automatically enroll and cannot de-enroll; people not already enrolled cannot make an election.

Rule: Select Rule if you define a FastFormula rule to determine a person's electability based on their current enrollment status. The formula must be of the type Enrollment Opportunity.

List of Default Enrollment Codes

New, Defaults; Current, Nothing: If a person is not yet enrolled in a given benefit, enroll that person in the default enrollment for that benefit. If the person is already enrolled in that benefit, de-enroll the person from that benefit.
New, Defaults; Current, Defaults: If a person is not yet enrolled in a given benefit, enroll that person in the default enrollment for that benefit. If a person is already enrolled in a benefit, enroll the person in the default enrollment for that benefit.
New, Defaults; Current, Same Enrollment and Rates: If a person is not yet enrolled in a given benefit, enroll that person in the default enrollment for that benefit. If a person is already enrolled in a benefit, do not change that enrollment or the activity rate.
New, Defaults; Current, Same Enrollment but Default Rates: If a person is not yet enrolled in a given benefit, enroll that person in the default enrollment for that benefit. If a person is already enrolled in a benefit, do not change the enrollment but assign the default activity rate.
New, Nothing; Current, Same Enrollment and Rates: If a person is not yet enrolled in a given benefit, do not enroll that person in that benefit. If a person is already enrolled in a benefit, do not change that enrollment or the activity rate.
New, Nothing; Current, Same Enrollment but Default Rates: If a person is not yet enrolled in a given benefit, do not enroll that person in that benefit. If a person is already enrolled in a benefit, do not change that enrollment but assign the default activity rate.
New, Nothing; Current, Defaults: If a person is not yet enrolled in a given benefit, do not enroll that person in that benefit. If a person is already enrolled in a benefit, enroll that person in the default enrollment for that benefit.
New, Nothing; Current, Nothing: If a person is not yet enrolled in a given benefit, do not enroll that person in that benefit. If the person is already enrolled in that benefit, de-enroll that person from that benefit.
Rule: Indicates that you will specify a FastFormula rule for this default treatment. The formula must be of the type Default Enrollment.

 

Total Compensation Statement Is Displaying The Thousand Separator As 'U' Instead Of ','

The printable statement version of the Total Compensation Statement is showing the Thousand Separator as 'u' instead of ','.
Fix:

To implement the solution, please execute the following steps:

1. Login to System Administrator responsibility.

2. Go to Profile > System.

3. Search ICX: Numeric characters and set the format with the correct value.(i.e. '10,000.00')

4. Clear the Cache from Functional Administrator

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

The program or plan year enrollment period cannot be found for this compensation object - Oracle Advanced Benefits

Error - The program or plan year enrollment period cannot be found for this compensation object. Suggestion: Define an enrollment period in the Program Enrollment Requirements window or the Plan Enrollment Requirements window for this life event. This error occurred in the following package : ben_enrolment_requirements.enrolment_requirements. Context at time of error : Program ID : 1 Plan ID : 1 Effective Date : 11-NOV-2012 Life Event Occurred Date : 11-NOV-2012


Solution:
Ensure that enrollment period information is set up.
Navigation:  Total Compensation > Programs and Plans > Program
Enrollments Requirements > query your Program > selecting the Timing Tab,
then select the Life Event tab within the Timing tab. 

Set up the Coverage, Rate and Period information entered for every life event
that is listed.  These can be accessed under the button labeled 'General'.

Example: 
Coverage:
Enrollment Coverage Start Date Event
Enrollment Coverage End Date 1 Day Before Event

Rates:
Rate Start Date Event
Rate End Date 1 Day Before Event

Periods:
Enrollment Period Start Date As of Event Date
Enrollment Period End Date 30 Days after Event Date
Cheersss...
Sandip

Employee wise Costing Details Oracle Payroll Costing

SELECT   papf.employee_number, papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date payroll_action,
         pet.element_name, SUM (pc.costed_value) credit, TO_NUMBER ('0')
                                                                        debit,
         pcak.segment1, pcak.segment2, pcak.segment3 ACCOUNT,
         (SELECT description
            FROM fnd_flex_values_vl f
           WHERE flex_value_set_id = 1013546
             AND f.flex_value_meaning = pcak.segment3
             AND ROWNUM < 2) "Account Meaning",
         pcak.segment4, pcak.segment5, pcak.segment6,
         ppa.creation_date run_date, ppa.effective_date
    FROM per_all_people_f papf,
         per_all_assignments_f paf,
         pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_payrolls_f ppay,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea,
         pay_costs pc,
         pay_cost_allocation_keyflex pcak,
         pay_input_values_f piv,
         pay_element_types_f pet,
         pay_consolidation_sets con
   WHERE papf.person_id = paf.person_id
     AND paf.business_group_id = 81
     AND ppa.payroll_id = ppay.payroll_id
     AND paa.assignment_id = paf.assignment_id
     AND paa.payroll_action_id = ppa.payroll_action_id
     AND pc.assignment_action_id = paa.assignment_action_id
     AND pppm.assignment_id(+) = paf.assignment_id
     AND pppm.external_account_id = pea.external_account_id(+)
     AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
     AND pet.element_type_id = piv.element_type_id
     AND piv.input_value_id = pc.input_value_id
     AND con.consolidation_set_id = ppa.consolidation_set_id
     AND pet.business_group_id = paf.business_group_id
     AND papf.business_group_id = paf.business_group_id
     AND ppa.action_status = 'C'
     AND ppa.action_type = 'C'
     --   AND    Trunc(SYSDATE) = Trunc(Ppa.Creation_Date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (pet.effective_start_date)
                             AND TRUNC (pet.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN TRUNC (paf.effective_start_date)
                   AND TRUNC (paf.effective_end_date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                             AND TRUNC (papf.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN NVL (TRUNC (pppm.effective_start_date),
                            TO_DATE ('01-JAN-1980')
                           )
                   AND NVL (TRUNC (pppm.effective_end_date),
                            TO_DATE ('31-DEC-4000')
                           )
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppay.effective_start_date)
                             AND TRUNC (ppay.effective_end_date)
     AND pc.debit_or_credit = 'C'
--      AND    Ppa.payroll_action_id = &P_Pay_Action_Id
--      AND    Paf.payroll_id        = :P_Payroll_Id
GROUP BY papf.employee_number,
         papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date,
         pet.element_name,
         pcak.segment1,
         pcak.segment2,
         pcak.segment3,
         pcak.segment4,
         pcak.segment5,
         pcak.segment6,
         ppa.creation_date,
         ppa.effective_date
UNION
SELECT   papf.employee_number, papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date payroll_action,
         pet.element_name, TO_NUMBER ('0') credit, SUM (pc.costed_value)
                                                                        debit,
         pcak.segment1, pcak.segment2, pcak.segment3 ACCOUNT,
         (SELECT description
            FROM fnd_flex_values_vl f
           WHERE flex_value_set_id = 1013546
             AND f.flex_value_meaning = pcak.segment3
             AND ROWNUM < 2) "Account Meaning",
         pcak.segment4, pcak.segment5, pcak.segment6,
         ppa.creation_date run_date, ppa.effective_date
    FROM per_all_people_f papf,
         per_all_assignments_f paf,
         pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_payrolls_f ppay,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea,
         pay_costs pc,
         pay_cost_allocation_keyflex pcak,
         pay_input_values_f piv,
         pay_element_types_f pet,
         pay_consolidation_sets con
   WHERE papf.person_id = paf.person_id
     AND paf.business_group_id = 81
     AND ppa.payroll_id = ppay.payroll_id
     AND paa.assignment_id = paf.assignment_id
     AND paa.payroll_action_id = ppa.payroll_action_id
     AND pc.assignment_action_id = paa.assignment_action_id
     AND pppm.assignment_id(+) = paf.assignment_id
     AND pppm.external_account_id = pea.external_account_id(+)
     AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
     AND pet.element_type_id = piv.element_type_id
     AND piv.input_value_id = pc.input_value_id
     AND con.consolidation_set_id = ppa.consolidation_set_id
     AND pet.business_group_id = paf.business_group_id
     AND papf.business_group_id = paf.business_group_id
     AND ppa.action_status = 'C'
     AND ppa.action_type = 'C'
     AND TRUNC (SYSDATE) BETWEEN TRUNC (pet.effective_start_date)
                             AND TRUNC (pet.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN TRUNC (paf.effective_start_date)
                   AND TRUNC (paf.effective_end_date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                             AND TRUNC (papf.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN NVL (TRUNC (pppm.effective_start_date),
                            TO_DATE ('01-JAN-1980')
                           )
                   AND NVL (TRUNC (pppm.effective_end_date),
                            TO_DATE ('31-DEC-4000')
                           )
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppay.effective_start_date)
                             AND TRUNC (ppay.effective_end_date)
     AND pc.debit_or_credit = 'D'
--      AND    Ppa.payroll_action_id = &P_Pay_Action_Id
-- AND    Paf.payroll_id        = :P_Payroll_Id
GROUP BY papf.employee_number,
         papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date,
         pet.element_name,
         pcak.segment1,
         pcak.segment2,
         pcak.segment3,
         pcak.segment4,
         pcak.segment5,
         pcak.segment6,
         ppa.creation_date,
         ppa.effective_date
ORDER BY 1, 2

Check the column data contains alphanumeric data in Oracle

select employee_number from per_all_people_f where regexp_like(employee_number,'^[a-zA-Z]');

Output

SC1000
I10001

Effective Date Reminder in Oracle HRMS

Effective Date Reminder

When you are new to DateTrack, you may find it useful to be reminded of your effective date whenever you open a window that contains datetracked information. The reminder appears in a Decision window and asks whether you want to change your effective date. If you choose Yes, the Alter Effective Date window displays.
There is a user profile option called DateTrack:Reminder that determines when the Decision window appears. There are three possible values for this profile option:

• Always

• Never

• Not Today

The Not Today value causes the reminder to appear when you navigate to a datetracked window and your effective date is not today's date.
You can set the value of this profile option in the Personal Profile Values window.

Oracle Apps – How to make a Form as READ ONLY

Follow the following steps in order to make a form as Query only/Read Only.

1. Login into the System Administrator/Application Developer responsibility.

2. Navigate to ‘Security -> Responsibility -> Define’

3. Query for the responsibility for which you want to make the form as ‘Query Only’ and copy the
   ‘Menu’ name.

4. Navigate to ‘Application -> Menu’ and query with the menu name which we got in step 3 in ‘User
    Menu Name’

5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the
   ‘Function’ field w.r.t the form that needs to be made as ‘Query Only’.

6. Navigate to ‘Application -> Funtion’ and query with the function name we got in Step 5 in ‘User    
    Function Name’ field.

7. Identify the correct Function for your form and then naviagate to the ‘Form’ tab.

8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already
    have another value existing in the parameter form then you need to seperate them with a space..
    Example: MODE=”PROJECT” QUERY_ONLY=YES

Oracle Apps Basics - FND Message Application Developer

This is one of several “Oracle Applications Basics” articles that are aimed at oracle applications program developers and cover a number of commonly used development elements when interacting with the E-Business Suite.

This article covers the use of application messages and focuses on the use of the pl/sql package FND_MESSAGE as well as the use of the generic loader to download and upload messages between your environments.
During the development of custom programs it is common to use customised messages in our code to implement error messages, confirmation messages, log entries etc…
These messages will be seen and interpreted by your end users and it is important to ensure that these messages can easily be changed in the event that your customer wishes to do so. The reason for changing them maybe simple such as a spelling mistake or more complex like assigning a generic error code to all error messages used by the system to aid in support activities.
The Oracle E-Business suite provides a message library that allows us to implement our messages and at the same time provides the facility to easily change these messages via the E-Business suite standard user interface.
This article will cover the creation of messages via the E-Business suite user interface and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message
To create a message in the E-Business suite message library you will need the “Application Developer” responsibility. Once logged in navigate to Application Developer > Application > Messages. This will launch a form and it will look like figure 1:

Figure 1

Enter a unique name for your message, for custom messages this is often in the form XX_NN_MM where
XX is the custom schema prefix you are using (Usually just XX)
NN is the module code or short name for the extension that the message belongs
MM the message description
I would advise that you always use a unique NN portion to the message name to make it easy for you to identify your messages i.e. you can query back all messages for XX_MYMOD%, this will help you later when you are compiling your messages for your installation scripts.
Select the language that your message is written in and the application that the message belongs, this will usually be the custom application setup by your development team lead. If you don’t yet have a custom application and you are developing custom application modules then you will need one.
Enter the message text in the “Current Message Text” box.
Click the save icon.
For message naming conventions you should always consult your build standards documentation for the customer you are implementing for to ensure you have observed any standard development approach that may have been implemented at your site.
Example:

Figure 2

Retrieving a message using PL/SQL
Once we have saved the message in E-Business suite via the standard message form we need to interact with the message library via our custom code to enable us to implement the message in the way it was intended to be used.
In order to retrieve the message from the database we need to use a few different standard foundation API’s in the FND_MESSAGE package. An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Not required but good practice)
This process will look like this in your PL/SQL
For more details regarding fnd_global.apps_initialise see my other blog entry “Oracle Apps Basics – Session Context”

set serveroutput on DECLARE my_message VARCHAR2(100); BEGIN --Initialise Apps Session fnd_global.apps_initialize( user_id => 1290 , resp_id => 50257 , resp_appl_id => 10003 ); --Clear the existing session FND_MESSAGE.CLEAR; --Tell ebusiness suite which message you want (custom application short name/message name) FND_MESSAGE.SET_NAME('XX','XX_MYMOD_MESSAGE1'); --Retrieve the message my_message := FND_MESSAGE.GET; --Output the message DBMS_OUTPUT.PUT_LINE(my_message); END; anonymous block completed Test Message 1

Using Tokens
The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time. This is useful for adding things like timestamps and user names to the existing message.
We will expand the example used in the previous section to demonstrate the use of tokens in our messages.
Open the E-Business Suite message creation form and query back your previous message.
In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. modify your message text from “Test Message 1″ to “Test Message Retrieved at &TIMESTAMP by user &USERNAME”
Tokens can be called what ever you want but they must be prefixed with an ampersand and they must be in uppercase.
To demonstrate the use of our new tokens we will add an additional 2 API calls to our previous code which will set the tokens to the desired values and then resolve the message using the substituted values, our code will now look like this:

 
set serveroutput on
 
DECLARE
 my_message VARCHAR2(100);
BEGIN
 --Initialise Apps Session
 fnd_global.apps_initialize( user_id      => 1290
                          ,  resp_id      => 50257
                          ,  resp_appl_id => 10003
                           );
 
 --Clear the existing session
 FND_MESSAGE.CLEAR;
 
 --Tell ebusiness suite which message you want (custom application short name/message name)
 FND_MESSAGE.SET_NAME('XXC','XX_MYMOD_MESSAGE1');
 
 --Set the timestamp message token with the current database timestamp 
 FND_MESSAGE.SET_TOKEN('TIMESTAMP',to_char(SYSDATE,'DD-MM-YYYY HH:MI:SS'));
 
 --Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN('USERNAME',FND_GLOBAL.USER_NAME);
 
 --Retrieve the message
 my_message := FND_MESSAGE.GET;
 
 --Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;
 
anonymous block completed
Test Message Retrieved at 02-04-2009 03:16:59 by user TURLEYK

 




Downloading and Uploading Messages using the Generic Loader


The generic loader can be used to download/upload existing E-Business Suite messages.


To download our example message we would use the following command at the Unix prompt on the mid-tier:





 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt 
FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XX' MESSAGE_NAME="XX_MYMOD_MESSAGE1"
 




To Upload our example message we would use the following command at the Unix prompt on the mid-tier:







 
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt

 





 

Applicant to Employee Query in Oracle HRMS

Query to find Applicant who are hired amd made employees.

SELECT DISTINCT papf.person_id, papf.full_name, pla.location_id,
                         pla.location_code, ppos.date_start doj,
                         xxpa.ROWID row_id
                   FROM per_all_people_f papf,
                         per_person_type_usages_f pptu_emp,
                        per_person_type_usages_f pptu_ex_apl,
                         per_person_types ppt_emp,
                         per_person_types ppt_ex_apl,
                         per_periods_of_service ppos
                   WHERE papf.person_id = pptu_emp.person_id
                     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                             AND papf.effective_end_date
                     AND TRUNC (SYSDATE) BETWEEN pptu_emp.effective_start_date
                                             AND pptu_emp.effective_end_date
                     AND TRUNC (SYSDATE)
                            BETWEEN pptu_ex_apl.effective_start_date
                                AND pptu_ex_apl.effective_end_date
                     AND ppt_emp.person_type_id = pptu_emp.person_type_id
                     AND papf.person_id = pptu_ex_apl.person_id
                     AND ppt_ex_apl.person_type_id =
                                                    pptu_ex_apl.person_type_id
                     AND ppt_emp.system_person_type = 'EMP'
                     AND ppt_ex_apl.system_person_type = 'EX_APL'
                     AND papf.person_id = ppos.person_id;

Query_Find Form in Oracle Apps

Link - http://erpschools.com/articles/query_find-form-in-oracle-apps

This is neatly explained steps.

Cheersss...
Sandip

Need steps to setup payslip in SSHR (UAE)

You need to run the following programs after prepayments to generate the payslip:

UAE Payment Output File

after that

UAE Payroll Archiver

Fields and Instructions in OAF Page Persanolization

Steps:
1. Go to Personalisation
2. Select the field.
3. Add CSS Class as OraDataText for flexfields and OraInstructionText for instructions.
4. Apply
5. Return to Application

Fields Readonly in OAF Page personalisation

Steps:
1. Go to Persanalization
2. Filter for Flex
3. Select the correct field
4. In the Segments, Add the Flexfield Segments like

LICENSE_REQUEST|Grade($RO$)|Job Title($RO$)|Department($RO$)|SBU($RO$)|Contact Number|Type of License

Cheersss...

Delete Position Hierarchy in Oracle HRMS

The Lowermost position should be deleted first

DECLARE
   ln_ovn   NUMBER := 1;
   CURSOR c
   IS
      SELECT     LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY, LEVEL,
                 hap.NAME parent_name, pse.parent_position_id,
                 has.NAME child_name, pse.subordinate_position_id,
                 pse.pos_structure_element_id, pse.object_version_number
            FROM (SELECT NAME, position_id
                    FROM hr_all_positions_f_tl
                   WHERE LANGUAGE = USERENV ('LANG')) hap,
                 (SELECT NAME, position_id
                    FROM hr_all_positions_f_tl
                   WHERE LANGUAGE = USERENV ('LANG')) has,
                 per_pos_structure_elements pse
           WHERE pse.business_group_id = 141
             AND hap.position_id = pse.parent_position_id
             AND has.position_id = pse.subordinate_position_id
      --start with pse.parent_position_id =
      CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
             AND PRIOR pse.pos_structure_version_id =
                                                  pse.pos_structure_version_id
             AND PRIOR pse.business_group_id = pse.business_group_id
        ORDER BY 2 DESC, 4;
BEGIN
   FOR i IN c
   LOOP
      BEGIN
         ln_ovn := i.object_version_number;
         hr_pos_hierarchy_ele_api.delete_pos_hierarchy_ele
                   (p_validate                      => FALSE,
                    p_pos_structure_element_id      => i.pos_structure_element_id,
                    p_object_version_number         => ln_ovn,
                    p_hr_installed                  => 'I'
                   );
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;

Create Position Hierarchy in Oracle HRMS

First Create the Hierarchy from Front End

DECLARE
   lo_pos_structure_element_id   NUMBER;
   lo_object_version_number      NUMBER;
BEGIN
   hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele
                  (p_validate                      => FALSE,
                   p_parent_position_id            => 16544,
                   p_pos_structure_version_id      => 62,
                   p_subordinate_position_id       => 16951,
                   p_business_group_id             => 141,
                   p_hr_installed                  => 'I',
                   p_effective_date                => '01-Jan-2000',
                   p_pos_structure_element_id      => lo_pos_structure_element_id,
                   p_object_version_number         => lo_object_version_number
                  );
END;

Check Cycle in Position Hierarchy

SQL> select empno     , mgr    , connect_by_iscycle  
          from scott.emp 
          connect by nocycle mgr  = prior empno  
          start with empno  = 7321  

        

"Function not available to this responsibi​lity" Error While Clicking On Forms Personalis​ation

To resolve the issue:
1. Log in as the System Administrator
2. Navigate -> Profile -> System -> User
3. Select an appropriate level (i.e. Site, Application, Responsibility, User)
4. Query the following profile: Utilities: Diagnostics
5. Set option to Yes and commit
6. You must log off and log on again for the settings to take effect.
Then retest for your issue.

Ref - Metalink Note - 1263970.1

FNDLOAD Form Persanalizations and Menu in Oracle Applications

Create the director like xx_ldts under $FND_TOP/patch/115/import/
Menu
Download
[appldemo@demo XX_LDTS]$ FNDLOAD apps/xxx@xxx 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXX_COLL_SELF_SERV.ldt MENU  MENU_NAME="XXX_COLLEAGUE_DIRECT_ACCESS"

UPLOAD
[appltest@testappn1 XX_LDTS]$ FNDLOAD apps/xxxxx@test 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXX_COLL_SELF_SERV.ldt

Form Persanalization:
Download
FNDLOAD apps/xxxx@dev 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct PERWSDPO_07.ldt FND_FORM_CUSTOM_RULES function_name="PERWSDPO_07"

Upload:
FNDLOAD apps/xxxxx@test 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct xxxx.ldt

Delete Organization Information in Oracle HRMS

DECLARE
   ln_ovn   NUMBER := 1;
   CURSOR c
   IS
      SELECT org_information_id, object_version_number
        FROM hr_organization_information
       WHERE TRUNC (creation_date) = TRUNC (SYSDATE)
         AND org_information_context = 'XX_ORG_ROLES'
      UNION
      SELECT org_information_id, object_version_number
        FROM hr_organization_information
       WHERE org_information_context = 'XX_ORG_ROLES'
         AND TRUNC (creation_date) = '01-May-2012';
BEGIN
   FOR i IN c
   LOOP
      ln_ovn := i.object_version_number;
      hr_organization_api.delete_org_manager
                               (p_validate                   => FALSE,
                                p_org_information_id         => i.org_information_id,
                                p_object_version_number      => ln_ovn
                               );
   END LOOP;
END;

Formatted Text in Oracle SSHR

1. Go to page where you want to add the formatted text.
2. Persanalize Page
3. Expand All
4. Create Item
5. Give Item Code
6. Text as <Font size = "1" face="Verd​ana" Color="Blu​e"> Instructio​ns </font>
7. Return to Application

Check if string contains all zeros in Oracle

select rpad (nvl (decode('X000',lpad('0',length('X000'),'0'),lpad(' ',length('X000'),' '),'X000'), chr (32)), 15, ' ') from dual

Data Loader for Lookup Sample dld file and API

To Load Value to the lookups, try following script. If it works, perfect or use data loader instead of breaking your head to debug the API. It is not public API.

DECLARE
   lr_rowid        ROWID  := NULL;
   l_lookup_code   NUMBER;
   CURSOR cur
   IS
      SELECT DISTINCT position_title
                 FROM xx_position_master
             ORDER BY 1;
BEGIN
   FOR i IN cur
   LOOP
      l_lookup_code := NULL;
      SELECT MAX (lookup_code) + 10
        INTO l_lookup_code
        FROM fnd_lookup_values
       WHERE lookup_type = 'XX_POSITION_TITLE'
         AND LANGUAGE = hr_api.userenv_lang;
      BEGIN
         fnd_lookup_values_pkg.insert_row
                             (x_rowid                    => lr_rowid,
                              x_lookup_type              => 'XX_POSITION_TITLE',
                              x_security_group_id        => 0,
                              x_view_application_id      => 3,
                              x_lookup_code              => l_lookup_code,
                              x_tag                      => NULL,
                              x_attribute_category       => NULL,
                              x_attribute1               => NULL,
                              x_attribute2               => NULL,
                              x_attribute3               => NULL,
                              x_attribute4               => NULL,
                              x_enabled_flag             => 'Y',
                              x_start_date_active        => NULL,
                              x_end_date_active          => NULL,
                              x_territory_code           => NULL,
                              x_attribute5               => NULL,
                              x_attribute6               => NULL,
                              x_attribute7               => NULL,
                              x_attribute8               => NULL,
                              x_attribute9               => NULL,
                              x_attribute10              => NULL,
                              x_attribute11              => NULL,
                              x_attribute12              => NULL,
                              x_attribute13              => NULL,
                              x_attribute14              => NULL,
                              x_attribute15              => NULL,
                              x_meaning                  => i.position_title,
                              x_description              => i.position_title,
                              x_creation_date            => SYSDATE,
                              x_created_by               => apps.fnd_global.user_id,
                              x_last_update_date         => SYSDATE,
                              x_last_updated_by          => apps.fnd_global.user_id,
                              x_last_update_login        => apps.fnd_global.login_id
                             );
         DBMS_OUTPUT.put_line ('Success');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error' || SQLERRM);
      END;
   END LOOP;
END;

DLD Sample File:

Data   tab  Data                                tab  Data                                tab  tab  Date             *dn
10      tab   Administration Manager  tab Administration Manager  tab   tab 01-JAN-1951 *dn
20      tab   Administrator                   tab Administrator                   tab   tab 01-JAN-1951 *dn