Function Name

Contexts / Parameter

Cursor SQL

GET_PLAN_BALANCE

Mandatory Contexts:

p_person_id,p_assignment_id,p_effective_date,

legislative_data_group

Parameter:p_pl_name

select sum(acrl1.end_bal)
    from anc_per_accrual_entries acrl1
   where person_id = P_PERSON_ID
     and work_term_asg_id in
            (select WORK_TERMS_ASSIGNMENT_ID
                 from per_all_assignments_m
                where assignment_id = p_assignment_id )
     and plan_id in   (p_plan_id)
      and accrual_period =
            (select max(acrl2.accrual_period)
               from anc_per_accrual_entries acrl2
              where person_id = P_PERSON_ID
                and acrl2.plan_id = p_plan_id
                and acrl2.accrual_period <= p_effective_date)

GET_ACCRUAL_BALANCE

In the above sql the following variables are supplied as Mandatory Contexts.

p_assignment_id , p_pl_id , p_person_id ,p_effective_date

select a.end_bal into l_accrual_balance
        from anc_per_accrual_entries a,
             anc_per_plan_enrollment b
          where  a.per_plan_enrt_id=b.per_plan_enrt_id
          and a.accrual_period=b.last_accrual_run
          and b.work_term_asg_id=p_assignment_id
          and b.plan_id=p_pl_id
          and b.person_id=p_person_id
          and p_effective_date between b.enrt_st_dt and b.enrt_end_dt

SQL to Get Plan Balance and Get Accrual Balance Value for a Specific Plan for Speciifc Person on Specific Date

select papf.person_number,

           paam.person_id,

            paam.assignment_id,

            paam.work_terms_assignment_id,

appe.plan_id,

(select a.end_bal 

from anc_per_accrual_entries a,

             anc_per_plan_enrollment b

          where  a.per_plan_enrt_id=b.per_plan_enrt_id

          and a.accrual_period=b.last_accrual_run

          and b.work_term_asg_id=paam.work_terms_assignment_id 

          and b.plan_id=appe.plan_id

          and b.person_id=paam.person_id

          and :p_effective_date between b.enrt_st_dt and b.enrt_end_dt

) accrual_balance,

(select sum(acrl1.end_bal)

from anc_per_accrual_entries acrl1

where person_id = paam.person_id

and work_term_asg_id in

            (select WORK_TERMS_ASSIGNMENT_ID

                 from per_all_assignments_m

                where assignment_id = paam.assignment_id)

and plan_id in   (appe.plan_id)

and accrual_period =

            (select max(acrl2.accrual_period)

               from anc_per_accrual_entries acrl2

              where person_id = paam.person_id

                and acrl2.plan_id = appe.plan_id

                and acrl2.accrual_period <= :p_effective_date)

) plan_balance

from    per_all_assignments_m paam,

            per_all_people_f papf,

anc_per_plan_enrollment appe,

anc_absence_plans_f_tl aapft

where     paam.person_id = papf.person_id

and       appe.person_id = papf.person_id

and       aapft.absence_plan_id = appe.plan_id

and       aapft.language = 'US'

and       aapft.name = nvl(:p_absence_plan_name,aapft.name)

and       trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

and       trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

and       trunc(sysdate) between aapft.effective_start_date and aapft.effective_end_date

and       papf.person_number = nvl(:p_person_number,papf.person_number)

and       paam.primary_flag = 'Y'

and       paam.assignment_type = 'E'

GET_PAID_VACATION_ACCRUAL_BALANCE

/******************************************************************************

FORMULA NAME: GET_PAID_VACATION_ACCRUAL_BALANCE

FORMULA TYPE: Global Absence Accrual

DESCRIPTION: This formula returns the leave balance for Paid Vacation Accrual Absence Plan

Change History:

Name Date Comments

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

Ashish Harbhajanka 28-AUG-2020 Initial Version

*******************************************************************************/

INPUTS ARE hr_person_id,hr_assign_id,ld_effective_date (DATE)

ln_accrual = 0

ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))

L_Leave_Balance = 0

ln_paid_vacation_plan_id = 300000204197416 /*Accrual Plan Id for Paid Vacation */

CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_paid_vacation_plan_id,HR_ASSIGNMENT_ID = hr_assign_id,PERSON_ID = hr_person_id,EFFECTIVE_DATE =ld_effective_date)

(

L_Leave_Balance = GET_ACCRUAL_BALANCE()

)

return L_Leave_Balance

GET_ABSENCE_BALANCE_VALUE_VAL

/******************************************************************************

FORMULA NAME: GET_ABSENCE_BALANCE_VALUE_VAL

FORMULA TYPE: Global Absence Entry Validation

DESCRIPTION: Custom Global Absence Entry Validation Formula Created to display GET_PLAN_BALANCE 

             and GET_ACCRUAL_BALANCE value as on Absence Start Date on Absence Recording Screen as a pop up message

Change History:

Name               Date         Version  Comments

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

Ashish Harbhajanka     29-Aug-2020    DRAFT 1A   Initial Version

**********************************************************************/

DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_START_TIME IS '00:00'

DEFAULT FOR IV_END_TIME IS '23:59'

DEFAULT FOR IV_TERM_ID is 0

DEFAULT FOR PER_PER_LATEST_REHIRE_DATE IS '1951/01/01 00:00:00' (date)

DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '1951/01/01'

DEFAULT FOR PER_ASG_WORK_TERMS_ASSIGNMENT_ID is 0

INPUTS ARE IV_START_DATE (date), 

  IV_END_DATE (date), 

  IV_START_TIME(text),

  IV_END_TIME(text)

ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)

ld_latest_hire_date = PER_PER_LATEST_REHIRE_DATE

ln_los_in_days = DAYS_BETWEEN(ld_current_date,ld_latest_hire_date)

lc_valid = 'Y'

ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)

ln_work_term_asg_id = PER_ASG_WORK_TERMS_ASSIGNMENT_ID

ln_person_id = GET_CONTEXT(PERSON_ID,0)

ld_effective_date= GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date)) /* If you want to get accrual balance base on another date, you can change the effective date*/

ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)

lc_plan_name ='Paid Vacation' /* your accrual plan name, please correct the plan name*/

ld_bal_date = IV_START_DATE  /* Trying to get balance as on Absence Start Date */

ln_paid_vacation_plan_id = 300000204197416 /* Absence Plan ID for 'Paid Vacation' */

L_DATA = ESS_LOG_WRITE('Within Validation Formula: Value of ln_hr_assignment_id: ' || to_char(ln_hr_assignment_id))

L_DATA = ESS_LOG_WRITE('Within Validation Formula: Value of ln_work_term_asg_id: ' || to_char(ln_work_term_asg_id))

L_DATA = ESS_LOG_WRITE('Within Validation Formula: Value of ln_person_id: ' || to_char(ln_person_id))

L_DATA = ESS_LOG_WRITE('Within Validation Formula: Value of ld_bal_date: ' || to_char(ld_bal_date))

CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = ln_person_id,EFFECTIVE_DATE = ld_bal_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)

(

ln_get_plan_balance = GET_PLAN_BALANCE(lc_plan_name)

)

call_formula('GET_PAID_VACATION_ACCRUAL_BALANCE',

                 ln_work_term_asg_id          > 'hr_work_term_asg_id' ,

ln_person_id                 > 'hr_person_id' ,

ld_bal_date                  > 'ld_effective_date' ,

ln_get_accrual_balance       < 'ln_accrual_balance' default 0)

L_DATA = ESS_LOG_WRITE('ln_get_plan_balance: ' || to_char(ln_get_plan_balance))

L_DATA = ESS_LOG_WRITE('ln_get_accrual_balance: ' || to_char(ln_get_accrual_balance))

VALID = 'N'

ERROR_MESSAGE = 'ld_bal_date: ' || to_char(ld_bal_date,'YYYY/MM/DD')

|| ' ln_get_plan_balance: ' || to_char(ln_get_plan_balance)

|| ' ln_get_accrual_balance: ' || to_char(ln_get_accrual_balance)

RETURN VALID,ERROR_MESSAGE

VALID = lc_valid

RETURN VALID