Get Source System ID of HDL Uploaded Data

Query to get Source System ID of Name which is uploaded by HDL file.

select N.DISPLAY_NAME D_NAME,N.FIRST_NAME,N.LAST_NAME,N.TITLE,

N.EFFECTIVE_START_DATE START_DATE,

P.WORKER_NUMBER W_NO, 

h.*

from fusion.HRC_INTEGRATION_KEY_MAP H, PER_PERSON_NAMES_F N,PER_EMPLOYEES_CURRENT_X P

WHERE 1=1

and N.BUSINESS_GROUP_ID = 1

and N.NAME_TYPE = 'Legislation Code' /* KW, AE */

AND h.surrogate_id=N.person_name_id

AND P.PERSON_ID = N.PERSON_ID

Execute SQL Query in Fast Formula or Get Value Set Value in Fast Formula

Database Items are limited for any kind of Fast Formula.Apart from Oracle offered database items, We are not able to get the values. 

For this kind of values which are not able to get by using database items, We can get it by executing query using table type value set. 

Requirement:

Check Element Entry Value on Particular Effective Date.

Solution:

Step 1: 

Create Table Type value set with query which needed to be executed from Fast Formula. 

Where Clause: 

Parameter which needed to pass from fast formula should be in “:{PARAMETER.PARAMETER_NAME}”

i.e.,

PEEF.person_id=:{PARAMETER.PERSON_ID}

AND PEEF.ELEMENT_TYPE_ID in (300000001208613,300000001344773)

    AND to_date(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE

    

Step 2: Fast Formula  Code to get the value from the query

l_element_entry_exists = GET_VALUE_SET('ADIB_HOUSING_ELEMENT_EXIST','|=PERSON_ID='''||L_Person_Id_char||''''||'|EFF_DATE='''||l_effective_date1||'''')

Limitation:

Value set is not bringing proper value in Fast Formula, if query is exceeding certain amount of characters.

 
 

HIRE/TERMINATION Absence Accrual Proration

We usually face issue with accrual proration that proration formula is working for HIRE action but the same formula is not working for TERMINATION.

This is the case when Matix Accrual has been setup. 

If accrual setup has been done by Fast Formula then we can handle proration in accrual formula itself.

Kindly go through below steps and explanation to setup proration for absence accrual for HIRE/TERMINATION. 

Explanation:-

Accrual Definition : Matrix

Case : 

Employee accrual - 2.5 per month

Employee Termination Date - 26-APR-2017

Accrual should be for 26 days only.

Oracle Calculation : 

Total Accrual = Accrual Proration * Accrual From Partial Accrual Period(Optional) * Accrual value defined in Accrual Matrix.

Here, In our case

Accrual value defined in Accrual Matrix = 2.5 

Accrual Proration = handled by Proration Formula

Accrual From Partial Accrual Period = handled by Partial Period Formula(Optional)

As termination case is not being handled by Accrual Proration Formula, we have handled HIRE/TERMINATION both the cases in Absence Partial Accrual Period Accrual Rate Formula.

Calculation :

Total Accrual = 1* (26/30) * 2.5

Please go through below setup and attached formula to know configuration to achieve proration for this case.

 

Formula 1: GIC Leave Proration FF

/*****************************************************************
FORMULA NAME: GIC Leave Proration FF
FORMULA TYPE: Global Absence Proration

DESCRIPTION:
Change History:
Name                 Date        Comments
Jigar Makwana        26/04/2017     
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
*****************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT for ANC_ABS_PLN_NAME is 'A'
DEFAULT for ANC_ABS_PLN_PLAN_UOM is 'D'



prorationFactor = 1


return prorationFactor

 Formula 1: GIC Absence Partial Period Accrual Rate

/*****************************************************************
FORMULA NAME: GIC Absence Partial Period Accrual Rate
FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula

DESCRIPTION:
Change History:
Name                 Date        Comments
Jigar Makwana        26/04/2017
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
*****************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT for ANC_ABS_PLN_NAME is 'A'
DEFAULT for ANC_ABS_PLN_PLAN_UOM is 'D'

INPUTS ARE IV_ACCRUAL,IV_CALEDARSTARTDATE (date),IV_CALEDARENDDATE(date)


l_no_of_days=DAYS_BETWEEN(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)),PER_ASG_REL_ORIGINAL_DATE_OF_HIRE)


l_accrual = 1

l_length_of_service=(l_no_of_days)
hire_year_number = to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'yyyy'))
current_year_number =  to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'yyyy'))
hire_month_no= to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'mm'))
current_month_no = to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'mm'))
no_of_days_month = to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'dd'))
Last_month_day = to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'dd'))
l_last_date_of_term_month = to_num(to_char(LAST_DAY(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))),'dd'))


term_year_number = to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'yyyy'))
term_month_no= to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'mm'))
no_of_days_term_month = to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'dd'))


  if( term_year_number=current_year_number AND term_month_no = current_month_no) then
         (
           l_accrual = (no_of_days_term_month/l_last_date_of_term_month)
                   
            )
   
   
 if(  hire_year_number = current_year_number AND hire_month_no = current_month_no) then

        (
          l_accrual = l_no_of_days/Last_month_day
            )
           
accrual = l_accrual*IV_ACCRUAL

return accrual

Absence Entry Validation Fast Formula Dependents on Another Leave's Accrual Balance

Requirement :
     Employee is allowed to apply for unpaid leave only when his/her Annual leave balance is 0(Zero).


STEP 1:
      Create Fast Formula as Mentioned Below 

--------------------------------------------------------------------------------------------------------------------
NAME                           : XX_GET_PLAN_BALANCE
TYPE                             : Global Absence Accrual

Legislative Data Group : Choose Your Legislation

Formula Text : 

/*************  Retrieve the accrual balance in the formula *********************/

INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)

DEFAULT FOR IV_PERSON_ID IS 0
DEFAULT FOR IV_TERM_ID IS 0
DEFAULT FOR IV_EFF_DT IS '2014/01/01' (DATE)

L_Leave_Balance = 0
L_Accrual_Plan_ID = 300000001679015 /* Accrual Plan ID */
L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT

CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = L_Accrual_Plan_ID,
HR_ASSIGNMENT_ID = L_Term_Id,
PERSON_ID = L_Person_Id,
EFFECTIVE_DATE =L_Eff_Dt )

(
  L_Leave_Balance = GET_ACCRUAL_BALANCE()
)

return L_Leave_Balance 
/****************************************************************************/

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





STEP 2:
    Create Fast Formula as Mentioned Below 

---------------------------------------------------------------------------------------------------------------------
NAME                           : XX_VALIDATION_FF
TYPE                             : Global Absence Entry Validation
Legislative Data Group  : Choose Your Legislation 

Formula Text : 
/********************validation formula***********************/

INPUTS ARE IV_START_DATE(DATE),iv_end_date (date)

DEFAULT FOR IV_START_DATE IS '2014/01/01' (DATE)
DEFAULT FOR IV_END_DATE IS '4712/01/01' (DATE)

L_Acrl_Balance = 0
L_Person_Id = GET_CONTEXT(PERSON_ID,0)
L_Term_Id = GET_CONTEXT(HR_TERM_ID,0)
L_Eff_Dt = IV_START_DATE
L_occurence = 0

SET_INPUT('IV_PERSON_ID', L_Person_Id)
SET_INPUT('IV_TERM_ID', L_Term_Id)e
SET_INPUT('IV_EFF_DT', L_Eff_Dt)

EXECUTE('XX_GET_PLAN_BALANCE')

L_Acrl_Balance = GET_OUTPUT('L_Leave_Balance', 0)

IF (L_Acrl_Balance < 1) THEN
(
VALID = 'Y'
)
ELSE
(
VALID = 'N'
ERROR_MESSAGE = 'ERROR -  Current Leave Balance: '+to_text(L_Acrl_Balance)+'  You can take this type of leave only when your annual leave balance is 0(Zero) '
)

return VALID, ERROR_MESSAGE

/****************************************************************************/
---------------------------------------------------------------------------------------------------------------------



STEP 3:
     Attach Validation formula at Absence Type. 
---------------------------------------------------------------------------------------------------------------------
Absence Type > Select 'Validation Formula' 

i.e. 
As per the current requirement 

Go to UNPAID leave > Select 'XX_VALIDATION_FF' formula as validation formula.

Navigation : Absence Administration > Absence Type > Search UNPAID Leave

 NOTE:-  Make sure Absence Type and Formulas must be fall under same Legislation.

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

Fusion HCM Absence Approver Based on Requester's Role

ADD PAYLOAD VALUES :-

            Add Values to Common Lookup: ‘ORA_ANC_ADDL_PAYLOAD’

 Values:

1.  EMP

2.  HR

3.  MGR

          NOTE:

          Make sure that there will be no space in Lookup Code as well as in the 

          Meaning.

SETUP PAYLOAD AT LEAVE LEVEL :-

 Navigation:

  

 Absence Administration > Absence Types > choose particular Absence >  Display Features

     Select Additional Payload Attribute as below