How to restrict the absence type list of values in Oracle Self-Service Human Resources.R 12.2.8

 

    Oracle Self-Service HR enables users to apply for absences. When applying for an absence, users enter the absence date or time, absence type, absence status, and absence reason. The absence types (for example, Vacation, Sick Leave etc.) are defined in Oracle HRMS application. Currently, all the absence types defined in Oracle HRMS application are displayed to the self-service user. 

    The user may not be eligible for all the absence types displayed in the self-service application.

    Example, In Our Organization, Unauthorized LWA Should not be viewed in the SSHR - Absence entry page. But the same to be displayed for the Payroll User in Enter & maintain - Absence Screen.

Solution :

    Oracle Self-Service HR introduces a custom package that enables customers to write the logic for deriving the absence type values based on their business requirements.

    Use the following the sample steps to derive a restricted list of values for absence type LOV in Absence Management. The custom logic has to be written in the New Package function HR_ABSENCE_RESTRICTED.ABSENCES_RESTRICTED Login person id and selected person id will be passed by default – using these two parameters the rest of the data or parameters can be queried from the tables.

Step 1: Query the HR_ABSENCE_RESTRICTED Package 

Step 2: Add the below function in the Package Body.

 

function absences_restricted(selected_person_id in varchar2,
         login_person_id in varchar2
         )return varchar2 is
l_sex VARCHAR2 (15) := NULL;

begin

IF to_number(selected_person_id) is not null THEN
 
    select sex into l_sex from per_all_people_f where person_id= to_number(selected_person_id)
    and sysdate between effective_start_date and effective_end_date;
 
        if l_sex = 'F' THEN
        return  '66,3061';
    elsif l_sex = 'M' THEN
    return '65,3061';
    else
    return '66,65,3061';
    END IF;

END IF;

end absences_restricted;
 

The above function is to restrict, the Maternity Leave  view access only to the Female Employees & Paternity Leave view access only to the Male Employees & Restrict Unauthorized LWA Leave for the Both female , male & Others.

Note: In this example, ‘66,3061,66’ are the id of the absence type, which has to restricted.

After Complied this, Go to Employee Self Service, Absence --> Create Absence, In the Absence Type you cant find the Unauthorized LWA Leaves.

Exclude Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence

 

If Customer wants to exclude the  Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence while applying the Absence request, then follow the below steps to achieve it Oracle HRMS R12.2.8

Scenario: While applying Causal Leave , System should not consider the In between 2nd & 4th Saturday , Sunday &  Paid Holidays from the Duration count. But for as Other Absence Types, it should include the in between days.

 

Step 1: Create the Function.

 Compile the Below function in the Sql Developer.

create or replace FUNCTION XX_GET_HOLI_WEEK_COUNT(
    p_start_date DATE,
    p_end_date   DATE )
  RETURN NUMBER
IS
  T_DAYS NUMBER (15) := NULL;
BEGIN
  T_DAYS := 0;
  SELECT XX.week_count + XX."Holi_Count" + XX.Saturday
  INTO T_DAYS
  FROM
    (SELECT week_count,
            (
            SELECT COUNT(holi.name)
            FROM hxt_holiday_days holi
            WHERE holiday_date BETWEEN ( p_start_date ) AND ( p_end_date )
            ) AS "Holi_Count",
            (
            SELECT count(*)
            FROM
              (WITH ALL_SATURDAYS AS
              (SELECT TO_CHAR(to_date('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW')                        * (level)                                                                                             AS WEEK_NO,
                NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR')                                   + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT')                        AS SATURDAY_DATE,
                row_number() OVER (PARTITION BY TO_CHAR(NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR') + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT'),'MON') ORDER BY level) AS Pos
              FROM DUAL
                CONNECT BY level<= 52
              ORDER BY 1
              )
            SELECT SATURDAY_DATE ,POS FROM ALL_SATURDAYS WHERE POS IN (2,4) ORDER BY 1,2
              )
            WHERE SATURDAY_DATE BETWEEN p_start_date AND p_end_date
            ) AS Saturday
    FROM
      ( WITH dates AS
      (SELECT TO_DATE(p_start_date) + level - 1 datum
      FROM dual
        CONNECT BY level <= TO_DATE(p_end_date) - TO_DATE(p_start_date) + 1
      )
    SELECT COUNT(datum) AS week_count
    FROM dates
    WHERE TO_CHAR(datum,'dy','nls_date_language = english') = 'sun'
      )
    )XX;
  RETURN T_DAYS;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || sqlerrm);
END;

 

Step 2: Create the Formula Function

Navigation : India HRMS Manager  --> Other Definitions --> Formula Function

Create the Formula Function as per the Screen shot mentioned below,

 

Step 3: Add the Validation in BG Absence Duration Fast Formula

Navigation : India HRMS Manager  --> Total Compensation  --> Write Formula.

Query the BG_ABSENCE_DURATION Formula

 Add the Below conditions in the Fast formula,

If absence_attendance_type_id = 61 then
         (
           L_DAYS = XX_GET_HOLI_WEEK_COUNT (date_start,date_end)
           /*Duration = TO_CHAR(TO_NUMBER(Duration) - L_DAYS)*/
           l_duration = (DAYS_BETWEEN(date_end,date_start) + 1) - L_DAYS
          )
        ELSE
        l_duration = (DAYS_BETWEEN(date_end,date_start) + 1)
       
        duration = to_char( l_duration - l_half_day_leave)
  )

Note : Here 61 is the Absence type ID for which the Exclude logics to be enabled.

 

Adhoc One Time Tax Deduction Upon the One Time Earnings Amount in Oracle HRMS R12.2.8

 

Business Needs:

     In Organization, One Time Earnings amount will be paid to an Employee with addition to the Regular Salary Earnings. In Such case, it is required to be deducted additional direct Tax for the Earnings amount Provided along with the regular Income Tax Deduction.

    Oracle HRMS for India uses the element classification Direct Payments to support the calculation of any ad hoc earnings such as taxable reimbursements.

 Step 1:Create a New Element (Name can be user defined. Please define as per Business Requirement).

As per the Screenshots Provided below, Create an Element for the Direct TDS with primary classification as Direct Payments.

Step 2: Create Input Values:

 Step 3: Add Balances in Feeds

    Pay Value : Direct Payments

    TDS         : TDS on Direct Payments

 

  Step 4: Provide the Element Link as required by the Business.

 Step 5: Assign One Time Earning Amount to the Employee

For giving the Earnings to Employee use the Element - Miscellaneous Earnings 1_New

 Step 6: Assign One Deduction Amount to the Employee

 For giving the Earnings to Employee use the Element - Miscellaneous Deductions 1_New.

 Step 7: For Adhoc Income Tax Deduction use Element - XX_DIRECT_PAY_TDS.

 

 

Step 8: Process Quick Pay Run & Verify the Results.

Key Flex Field & Descriptive Flex Field - Value Set's Options.

 

I) How to Setup - Default Value for a Segment?

Example : 

We have Created EIT with Segment as Employee Name. & In Which Employee Name has to be defaulted automatically based on the FND_Profile Employee Name.

Steps: 

    1.    Create Segment Name Called - Employee Name.

    2.    Click Open

    3.    Click Default Type as SQL Statement.

    4.    Enter the Default Value as 

select FULL_NAME
from per_people_x where 1 = 1
and CURRENT_EMPLOYEE_FLAG = 'Y'
and person_id = FND_PROFILE.value('PERSON_ID')

    5.    Click Save. 

-----------------------------------------------------------------------------------------------------------

 II) How to Setup List of Value - Value Set for a Validation Type Table.

 Example : 

We have created EIT with Segments, In which Values should be Populated from the Table Source.

Family Member Name should be come in LOV against One Segment.

Steps: 

    1.    Create Segment Name Called - Family Member Name.

    2.    Click Value Set

    3.    Value Set Name - XX_PER_DEPENDANTS

    4.    List Type  - List of Values

    5.    Validation Type - Table

    6.    Table Name - per_all_people_f a, PER_CONTACT_RELATIONSHIPS b ,  per_all_people_f c

    7.    Value - C.FULL_NAME

    8.    ID - a.PERSON_ID

    9.    Where/ Order By - a.person_id = b.person_id
and b.CONTACT_PERSON_ID = c.person_id
and sysdate between a.effective_start_date and a.EFFECTIVE_END_DATE
and a.person_id = FND_PROFILE.value('PERSON_ID')

    10.    Click Test & Save.

-----------------------------------------------------------------------------------------------------

 III) How to Input the Previous Segments Value into New Segments.

 Example : 

Planned End Date = 01-JAN-2021

Extension Period = 2 Months

Based on the Selection of the Extension period, New Planned End Date to be Calculated automatically.

Steps :

    1.    Create Segments Names as Planned End Date , Extension Period, New Planned End Date.

    2.    Against New Planned End Date - Click Open

    3.    Click Default Type as SQL Statement.

    4.    Enter the Default Value as 

SELECT add_months(fnd_date.canonical_to_date(:$FLEX$.Probation_End_Date),

to_number(:$FLEX$.Extension_Period)) FROM dual

    5.    Click Save. 

------------------------------------------------------------------------------------------------

 

Solution for User Declared Tax Deduction in Oracle HRMS R12.2.8 – India Legislation.

 

In Oracle eBS HRMS Standard India Legislation, the Monthly Income Tax amount will be calculated based on the Projected Annual Gross & Projected Tenure Month Period.

                But in some Organization, Employees are request the Payroll Officer to deduct the Income Tax based on the Employee’s Input for the first some periods and Remaining balance value to be adjusted in the future period and accordingly actual Income tax will be deducted in the Year End.

                In Standard functionality, It is not possible to accommodate in the System. In the below Steps, it is described how to achieve this process flow in Oracle HRMS R12.2.8.

Step 1:   Create Two Elements as mentioned below,

Element Name 1: User Income Tax

Element Name 2: User Declared Income Tax.

Navigation: India HRMS Manager --> Total Compensation --> Basic --> Element Description

Name		User Income Tax	User Declared Income Tax
Reporting Name		User Income Tax	User Declared Income Tax
Description		User Income Tax	User Declared Income Tax
Date From		User Defined	User Defined
Date To		31-Dec-4712	31-Dec-4712
Primary Classification		Information	Voluntary Deductions
Benefit Classification		Null	Null
Type: Recurring/ Nonrecurring		Non Recurring	Recurring
Termination Rule: Actual Termination/ Final Close		Final Close	Final Close
Multiple Entries Allowed		Unchecked	Unchecked
Additional Entry Allowed		Unchecked	Unchecked
Closed for Entry		Unchecked	Unchecked
Process in Run		Checked	Checked
Once Each Period		Unchecked	Unchecked
Indirect Results		Checked	Unchecked
Adjustment Only		Unchecked	Unchecked
Third Party Payment		Unchecked	Unchecked
Priority		36001	36002
Skip Rule

 Input Values for the above defined Elements,

Element Name : User Income Tax

Name	Sequence	Units	Required	User Enterable	Database Item
Balance Tax	1	Money	Unchecked	Checked	Unchecked
Balance Cess	2	Money	Unchecked	Checked	Unchecked
Balance Sec and HE Cess	3	Money	Unchecked	Checked	Unchecked
Balance Surcharge	4	Money	Unchecked	Checked	Unchecked

Element Name : User Declared Income Tax

Name	Sequence	Units	Required	User Enterable	Database Item
Pay Value	1	Money	Unchecked	Checked	Checked
Tax	2	Money	Unchecked	Checked	Unchecked
Cess	3	Money	Unchecked	Checked	Unchecked
Sec and HE Cess	4	Money	Unchecked	Checked	Unchecked
Surcharge	5	Money	Unchecked	Checked	Unchecked
Allow System to Calculate	6	Character	Unchecked	Checked	Unchecked

 

Step 2: Customize the Standard Package :

 Query the Package Name Called : PAY_IN_TAX_UTILS

***Add the Package Spec at the Bottom of the Above mentioned Package:***

function get_tax_skip_check(  p_assignment_id in number

                        ,p_business_group_id in number

                        ,p_date_earned   in date)

                        return varchar2;

***Add the Package Body at the Bottom of the Above mentioned Package:***

function get_tax_skip_check(  p_assignment_id in number

                        ,p_business_group_id in number

                        ,p_date_earned   in date) return varchar2

  is

  cursor sys_recalc_flag is

  select peev.screen_entry_value from

pay_element_entry_values_f peev,

pay_element_entries_f pee,

pay_element_types_f pet,

pay_input_values_f piv

where pet.element_name = 'User Declared Income Tax'

and pet.business_group_id = p_business_group_id

and piv.element_type_id = pet.element_type_id

and pee.element_type_id = pet.element_type_id

and pee.element_entry_id = peev.element_entry_id

and piv.name='Allow System to Calculate'

and pee.assignment_id = p_assignment_id

and peev.input_value_id = piv.input_value_id

and p_date_earned between pet.effective_start_date and pet.effective_end_date

and p_date_earned between piv.effective_start_date and piv.effective_end_date

and p_date_earned between pee.effective_start_date and pee.effective_end_date

and p_date_earned between peev.effective_start_date and peev.effective_end_date;

 

l_flag varchar2(5);

 

  begin

  open sys_recalc_flag;

  fetch sys_recalc_flag into l_flag;

    if sys_recalc_flag%NOTFOUND THEN

      close sys_recalc_flag ;

      RETURN 'Y';

     END IF;

 

  close sys_recalc_flag ;

  return l_flag;

end get_tax_skip_check;

 

Step 3 : Create Formula Function

Navigation: India HRMS Manager --> Other Definition --> Formula Function

Name : INBG_TAX_CALC

Data Type : Text

Class : External Function

Definition : pay_in_tax_utils.get_tax_skip_check

 

Step 4: Create Fast Formula for the New Elements Created :

Navigation : India HRMS Manager --> Total Compensation --> Basic --> Write Formulas.

Name : USER_INCOME_TAX

Type : Oracle Payroll

Description : To be attached to User Income Tax

Formula Code:

DEFAULT FOR Balance_Tax IS 0
DEFAULT FOR Balance_Surcharge IS 0
DEFAULT FOR Balance_Sec_and_HE_Cess IS 0
DEFAULT FOR Balance_Cess IS 0

INPUTS ARE Balance_Tax, Balance_Surcharge, Balance_Sec_and_HE_Cess, Balance_Cess   

/*Allow System to Calculate is ‘No’ then it means user will provide his tax values .
So skip or nullify those already calculated by system*/                                                            
IF INBG_TAX_CALC() ='N' THEN
(x=IN_DEBUG('****************************************','************')
x=IN_DEBUG('Balance_Tax',TO_CHAR(Balance_Tax))                                         x=IN_DEBUG('Balance_Surcharge',TO_CHAR(Balance_Surcharge))                             x=IN_DEBUG('Balance_Sec_and_HE_Cess',TO_CHAR(Balance_Sec_and_HE_Cess))                 
x=IN_DEBUG('Balance_Cess',TO_CHAR(Balance_Cess))                                       x=IN_DEBUG('***********************************','*********')                                                                      
l_income_tax      =  - Balance_Tax                                                  
l_surcharge       =  - Balance_Surcharge                                            
l_education_cess  =  - Balance_Cess                                                 
l_sec_and_he_cess =  - Balance_Sec_and_HE_Cess                                    
l_total_tax       = l_income_tax + l_surcharge + l_education_cess + l_sec_and_he_cess
 
 
l_balance_tax     =  - l_total_tax                                                                                                                                           x=IN_DEBUG('**************************************','**************')
x=IN_DEBUG('l_income_tax',TO_CHAR(l_income_tax))                                       x=IN_DEBUG('l_surcharge',TO_CHAR(l_surcharge))                                         x=IN_DEBUG('l_education_cess',TO_CHAR(l_education_cess))                               x=IN_DEBUG('l_sec_and_he_cess',TO_CHAR(l_sec_and_he_cess))                             x=IN_DEBUG('l_total_tax',TO_CHAR(l_total_tax))                                         x=IN_DEBUG('l_balance_tax',TO_CHAR(l_balance_tax))                                     
x=IN_DEBUG('*************************************','*************')  
                                                                                                                                                                  RETURN l_income_tax,                                                                          l_surcharge,                                                                           l_education_cess,                                                                      l_sec_and_he_cess,                                                                     l_total_tax,                                                                            
l_balance_tax
)
ELSE
RETURN                                                             
-----------------------------------------------------------------

Name : USER_USER_DEFINED_IT

Type : Oracle Payroll

Description : To be attached to User Declared Income Tax

Formula Code:

DEFAULT FOR Tax IS 0   
DEFAULT FOR Cess IS 0   
DEFAULT FOR Surcharge IS 0
DEFAULT FOR Sec_and_HE_Cess IS 0

INPUTS ARE Tax, Cess, Surcharge, Sec_and_HE_Cess
IF INBG_TAX_CALC()='N' THEN
(
x=IN_DEBUG('********************************','***************')
x=IN_DEBUG('Tax',TO_CHAR(Tax))
x=IN_DEBUG('Cess',TO_CHAR(Cess))
x=IN_DEBUG('Surcharge',TO_CHAR(Surcharge))
x=IN_DEBUG('Sec_and_HE_Cess',TO_CHAR(Sec_and_HE_Cess))
x=IN_DEBUG('***************************','********************')
l_tax             = tax
l_cess            = Cess
l_surcharge       = Surcharge
l_sec_and_he_cess = Sec_and_HE_Cess
l_pay_value       = l_tax + l_cess + l_surcharge + l_sec_and_he_cess
l_balance_tax     =  - l_pay_value
RETURN l_tax,       
l_cess,       
l_surcharge,       
l_sec_and_he_cess,       
l_pay_value,    
l_balance_tax
)
ELSE
RETURN

Step 4 : Create Eligibility Link for User Declared Income Tax Element.

Navigation : India HRMS Manager --> Total Compensation --> Basic --> Link

Step 5 : Create Formula Results Rules.

Navigation : India HRMS Manager --> Total Compensation --> Basic -->  

Formula Results

 Element Name : Income Tax Information

Processing Rules:

Name : Standard

Formula : IN_INCOME_TAX

Add the below mentioned formula results in addition to the existing.

Name	Type	Element	Input Value
CESS_THIS_PAY	Indirect result	User Income Tax	Balance Cess
SEC_AND_HE_CESS_THIS_PAY	Indirect result	User Income Tax	Balance Sec and HE Cess
SURCHARGE_THIS_PAY	Indirect result	User Income Tax	Balance Surcharge
TAX_THIS_PAY	Indirect result	User Income Tax	Balance Tax

----------------------------------------------------- 

Element Name : User Income Tax

Processing Rules:

Name : Standard

Formula : USER_INCOME_TAX

Add the below mentioned formula results in addition to the existing.

Name	Type	Element	Input Value
L_EDUCATION_CESS	Indirect result	Income Tax	Cess
L_INCOME_TAX	Indirect result	Income Tax	Tax
L_SEC_AND_HE_CESS	Indirect result	Income Tax	Sec and HE Cess
L_SURCHARGE	Indirect result	Income Tax	Surcharge
L_TOTAL_TAX	Indirect result	Income Tax	Pay Value
L_BALANCE_TAX	Indirect result	Form16 Income Tax Information	Balance Tax
L_EDUCATION_CESS	Indirect result	Form16 Income Tax Information	Education Cess till Date
L_INCOME_TAX	Indirect result	Form16 Income Tax Information	Income Tax till Date
L_SEC_AND_HE_CESS	Indirect result	Form16 Income Tax Information	Sec and HE Cess till Date
L_SURCHARGE	Indirect result	Form16 Income Tax Information	Surcharge till Date

----------------------------------------------------- 

Element Name : User Declared Income Tax

Processing Rules:

Name : Standard

Formula : USER_USER_DEFINED_IT

Add the below mentioned formula results in addition to the existing.

 

Name	Type	Element	Input Value
L_CESS	Indirect result	Income Tax	Cess
L_PAY_VALUE	Indirect result	Income Tax	Pay Value
L_SEC_AND_HE_CESS	Indirect result	Income Tax	Sec and HE Cess
L_SURCHARGE	Indirect result	Income Tax	Surcharge
L_TAX	Indirect result	Income Tax	Tax
L_BALANCE_TAX	Indirect result	Form16 Income Tax Information	Balance Tax
L_CESS	Indirect result	Form16 Income Tax Information	Education Cess till Date
L_TAX	Indirect result	Form16 Income Tax Information	Income Tax till Date
L_SEC_AND_HE_CESS	Indirect result	Form16 Income Tax Information	Sec and HE Cess till Date
L_SURCHARGE	Indirect result	Form16 Income Tax Information	Surcharge till Date

Step 6 : Assign the Employee Declared Income Tax Amount.

Navigation : India HRMS Manager --> People --> Enter & Maintain --> Assignment 

--> Add Element as

User Declared Income Tax – Effective from 01-Apr-2021

 

Test Case :

Employee Basic Value is 1,00,000 Rs.

Annual Gross = 12,00,000

Standard Exemption Deduction = 50,000

Tax On Income =  1,57,500

Cess = 6300

Total Tax Liability = 1,63,800

As per System Standard Deduction for the Month Apr ’21 = 13,650 .

But Employee requests Payroll to deduct 5200 every month starting from Apr’21 to Dec’21.

And From Jan’22 to Mar’22 the remaining tax value to be deducted from Employee’s Salary.

After completing the Element Entry Input Value as mentioned in the above Screenshot, Run Quick Pay .

Step 7 : Quick Pay Run

Navigation : India HRMS Manager --> People --> Enter & Maintain --> Assignment-->

Quick Pay – Apr’21 Month.

Check Run Results,

System automatically nullify the Standard Deduction value & Updated the User declared Income Tax amount as Monthly Tax for the Month of Apr’21.