Query to fetch Domain Name - Oracle HCM

 select external_virtual_host from ask_deployed_domains where deployed_domain_name = 'FADomain'

This will give you the current logged in instance URL of the Oracle HCM pod

Balance Feeds Query - Oracle HCM

 SELECT 

    pbt.BALANCE_NAME,
    pet.ELEMENT_NAME,
    piv.BASE_NAME         AS INPUT_VALUE_NAME,
    pbf.SCALE,
    pbf.EFFECTIVE_START_DATE,
    pbf.EFFECTIVE_END_DATE
FROM PAY_BALANCE_FEEDS_F pbf
JOIN PAY_BALANCE_TYPES_VL pbt
    ON pbf.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
JOIN PAY_INPUT_VALUES_F piv
    ON pbf.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
JOIN PAY_ELEMENT_TYPES_TL pet
    ON piv.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
WHERE TRUNC(SYSDATE) BETWEEN piv.EFFECTIVE_START_DATE 
                         AND piv.EFFECTIVE_END_DATE
AND pet.LANGUAGE = USERENV('LANG')
AND UPPER(pbt.BALANCE_NAME) = UPPER('XX Salary Balance')
ORDER BY pbt.BALANCE_NAME,
         pet.ELEMENT_NAME

Sample HDL for Payroll Balance Name Translation

METADATA|PayrollBalanceDefinitionTranslation|BalanceCode|Language|BalanceName|ReportingName|LegislativeDataGroupName

MERGE|PayrollBalanceDefinitionTranslation|Extended Medical Leave Entitlement Payment|E|Pago del derecho a licencia médica extendida|Pago del derecho a licencia médica extendida|PE Legislative Data Group

Balance Code would be the Base Balance Name from pay_balance_types table

Balance Name and Reporting Name would be the values which you would load in the required language

Get sysdate in fast formula

 L_SYSDATE = '4712/12/31 00:00:00' (date)

 L_SYSDATE = GET_CURRENT_DATE()

HDL Lines

SELECT TEXT

FROM fusion.hrc_dl_file_lines
WHERE data_set_bus_obj_id IN
(SELECT data_set_bus_obj_id
FROM fusion.hrc_dl_data_set_bus_objs
WHERE data_set_id IN
(SELECT data_set_id
FROM fusion.hrc_dl_data_sets
WHERE ucm_content_id = 'UCMFA15238827'))
--and (TEXT like 'METADATA%' or TEXT like '%17019%')
order by SEQ_NUM

HDL errors

Select  fl.text, ml.MSG_TEXT

 from  fusion.hrc_dl_file_lines fl
     , fusion.hrc_dl_data_set_bus_objs dsbo
     , fusion.hrc_dl_file_rows fr
     , fusion.hrc_dl_physical_lines pl
     , fusion.hrc_dl_message_lines ml
where fl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.line_id = fl.line_id
  and pl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.row_id = pl.row_id
  and dsbo.data_set_id in (
       select data_set_id
         from fusion.hrc_dl_data_sets
        where ucm_content_id = 'UCMFA15238827'
--AND ml.MSG_TEXT like '%The values Benefits and Pensions aren't valid for the attribute DeductionGroupId%'
      )
and (ml.MESSAGE_SOURCE_LINE_ID=pl.physical_line_id OR ml.MESSAGE_SOURCE_LINE_ID=fr.logical_line_id)
order by ml.MSG_TEXT

 

Query to pull skills from Job Requisition in Oracle HCM

 SELECT irc.requisition_number
      ,hpb.profile_code
      ,hpt.description profile_desc
      ,hpt.summary,
  hpi.ITEM_TEXT240_1 Job_Requisition_Skill
  FROM HRT_PROFILE_ITEMS hpi
      ,HRT_PROFILES_B hpb
      ,HRT_PROFILES_TL hpt
      ,HRT_PROFILE_RELATIONS hpr
      ,irc_requisitions_vl irc
 WHERE hpi.profile_id = hpb.profile_id
   AND hpb.profile_usage_code = 'R'
   AND hpi.profile_id = hpr.profile_id
   AND hpi.profile_id = hpt.profile_id
   AND irc.requisition_id = hpr.object_id
   AND irc.profile_id = hpt.profile_id
  and irc.requisition_number='12345'
   AND hpt.language = 'US'

Oracle HCM Extract Schedule Query

 SELECT pfv.base_flow_name Base_flow_name
       ,
       pfi.instance_name,
       (SELECT To_char(Min(fti1.creation_date),'DD-MON-YYYY')
        FROM   pay_flow_task_instances fti1
        WHERE  pfi.root_flow_instance_id=fti1.flow_instance_id)
       FIRST_SCHEDULE_DATE,
       To_char(fti.scheduled_date,'DD-MON-YYYY')
       Current_Schedule_Date,
       fti.status
       CURRENT_SCHEDULE_STATUS,
       (SELECT Listagg(fp.parameter_name
                       || '='
                       || pv.flow_param_value,',')
                 within GROUP(ORDER BY fp.parameter_name)
        FROM   pay_flow_parameters_vl fp,
               pay_flow_param_values pv
        WHERE  1 = 1 
               AND fp.base_flow_id=pfv.base_flow_id
               AND pv.flow_instance_id=pfi.flow_instance_id
               AND fp.base_flow_parameter_id=pv.base_flow_parameter_id
               AND flow_param_value IS NOT NULL
               AND pfi.instance_name=pfi.instance_name)
       Flow_parameters_Latest_Run,
       fti.created_by
       Schedule_Created_By
FROM   pay_flow_task_instances fti,
       pay_flow_tasks_vl ft,
       pay_flow_instances pfi,
       pay_flows_vl pfv
WHERE  1 = 1 
       AND pfv.base_flow_id=pfi.base_flow_id
       AND fti.base_flow_task_id=ft.base_flow_task_id
       AND pfi.flow_instance_id=fti.flow_instance_id
       AND fti.scheduled_date IS NOT NULL
       AND fti.status='SCHEDULED'
       AND pfi.status='SCHEDULED'
ORDER  BY flow_name DESC

Element entries query

 SELECT
    'BURSTING_KEY' BUR_KEY,
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date,
    SUM(ELE1)      ELE1,
    SUM(ELE2)      ELE2,
    SUM(ELE3)      ELE3,
    SUM(ELE4)      ELE4
FROM
    (
        SELECT
            papf.person_number,
            paaf.ass_attribute8                                                                       plan_id,
            paaf.assignment_id,
            paaf.person_id,
            to_char(ptp.start_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') start_date,
to_char(ptp.end_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') end_date,
            decode(ele.element_name, 'Ele1',(prrv.result_value), 0) ELE1,
            decode(ele.element_name, 'Ele2',(prrv.result_value), 0) ELE2,
            decode(ele.element_name, 'Ele3',(prrv.result_value), 0) ELE3,
            decode(ele.element_name, 'Ele4',(prrv.result_value), 0) ELE4
        FROM
            pay_element_types_vl     ele,
            pay_run_results          prr,
            pay_run_result_values    prrv,
            pay_input_values_vl      piv,
            pay_payroll_rel_actions  ppra,
            pay_payroll_assignments  ppasg,
            per_all_assignments_f    paaf,
            per_all_people_f         papf,
            pay_payroll_actions      ppa,
            pay_all_payrolls_f       pap,
            pay_time_periods         ptp
        WHERE
                1 = 1
            AND ele.element_type_id = prr.element_type_id
            AND prr.run_result_id = prrv.run_result_id
            AND prrv.input_value_id = piv.input_value_id
            AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
            AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id
            AND ppasg.hr_assignment_id = paaf.assignment_id
            AND paaf.person_id = papf.person_id
            AND ppra.payroll_action_id = ppa.payroll_action_id
            AND ppa.payroll_id = pap.payroll_id
            AND pap.payroll_id = ptp.payroll_id
            AND ((ppra.run_type_id IS NOT NULL AND ppa.action_type in ('R','Q','V')) 
OR ppa.action_type in ('B'))
AND ppra.RETRO_COMPONENT_ID is NULL
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
            AND paaf.ass_attribute_category = 'CA'
            AND paaf.ass_attribute8 IS NOT NULL
            AND ((ele.element_name IN ('Element1','Element2')
            AND piv.name = 'Pay Value')
OR (ele.element_name IN ('Element3','Element4')
            AND piv.name = 'Earnings'))
            AND ptp.period_category = 'E'
            AND :p_eff_date BETWEEN pap.effective_start_date AND pap.effective_end_date
            AND :p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND :p_eff_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND :p_eff_date BETWEEN ele.effective_start_date AND ele.effective_end_date
            AND :p_eff_date BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND :p_eff_date BETWEEN ptp.start_date AND ptp.end_date
            AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
            AND papf.person_number = nvl(:p_person_number, papf.person_number)
    )
GROUP BY
    'BURSTING_KEY',
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date

HCM query to fetch LE,department, position

 SELECT PAPF.person_number
,PPNF.full_name
,To_char(PPOS_MAIN.date_start, 'DD-MON-YYYY', 'nls_date_language=American') hire_date
,dept.NAME dept_name
,pos.NAME pos_name
,HRL.NAME LE_NAME
,hc.value HEADCOUNT
,To_char(PAAF.effective_start_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_start_date
,To_char(PAAF.effective_end_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_end_date
,To_char(PPOS_MAIN.actual_termination_date, 'DD-MON-YYYY', 'nls_date_language=American') termination_date
,pastvl.user_status assignment_status
FROM per_all_people_f PAPF
,per_person_names_f PPNF
,per_all_assignments_f PAAF
,per_periods_of_service PPOS_MAIN
,pay_payroll_assignments PPASG
,per_assignment_status_types_vl pastvl
,per_legal_employers HRL
,HR_ALL_ORGANIZATION_UNITS_F_VL dept
,HR_ALL_POSITIONS_F_VL pos
,PER_ASSIGN_WORK_MEASURES_F_V hc
WHERE PAPF.person_id = PPNF.person_id
AND PPNF.name_type = 'GLOBAL'
AND PPOS_MAIN.date_start = (
SELECT Max(date_start)
FROM per_periods_of_service PPOS
WHERE PPOS.person_id = PAPF.person_id
)
AND PPOS_MAIN.person_id = PAPF.person_id
AND PAAF.person_id = PAPF.person_id
AND PAAF.assignment_type IN ('E')
AND PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id
AND PAAF.assignment_id = PPASG.hr_assignment_id
AND PAAF.legal_entity_id = HRL.organization_id
AND HRL.STATUS = 'A'
AND paaf.organization_id = dept.organization_id
AND paaf.position_id = pos.position_id(+)
AND paaf.assignment_id = hc.assignment_id(+)
AND hc.UNIT(+) = 'HEAD'
AND Nvl(PPOS_MAIN.actual_termination_date, Trunc(SYSDATE)) BETWEEN PPASG.start_date
AND PPASG.end_date
AND PAAF.assignment_status_type_id = PASTVL.assignment_status_type_id --AND PAAF.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
AND Trunc(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PPNF.effective_start_date
AND PPNF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PAAF.effective_start_date
AND PAAF.effective_end_date
AND Trunc(SYSDATE) BETWEEN HRL.effective_start_date
AND HRL.effective_end_date
AND Trunc(SYSDATE) BETWEEN dept.effective_start_date
AND dept.effective_end_date
AND Trunc(SYSDATE) BETWEEN pos.effective_start_date(+)
AND pos.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN hc.effective_start_date(+)
AND hc.effective_end_date(+)
AND PAPF.person_number IN ('12345')
x

Enable Advanced Edit in Oracle HCM Extracts

1. Navigate to Support and Maintenance

2. Go to task Manage common Lookups

3. Open ORA_PER_EXT_CONFIG lookup

4. Add Lookup code - EXT_PUI_ENABLE and meaning as Yes

5. Save and Close.

Adding BI Publisher Add IN in MS word

 Follow the DOC ID 2435208.1

Most important point is Note: The versions of Microsoft Office, BI Publisher Desktop, and the Java JRE must all match as either 32-bit or 64-bit.

Get current logged in user in BIP

select SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER') from dual

This query will give the current logged in user in BIP

Select multiple values in BI Report parameter dropdown

When we want to allow a BI Report parameter to take in multiple values, we need to write the where clause as below.

select * from (select 'a' col1 from dual

union

select 'b' col1 from dual

union

select 'c' col1 from dual

union

select 'd' col1 from dual

union

select 'e' col1 from dual

union

select 'f' col1 from dual

)

where 1=1

--and col1 in (:p_col1)

AND ( ( Coalesce(NULL, :p_col1) IS NULL ) OR ( col1 IN ( :p_col1) ) )

If and col1 in (:p_col1) is used then when we select All, then nothing is printed instead of all the values. 

Coalesce function returns the first non null value from its parameters

HCM Extract - View User Entity Details - Change to old UI

Update the lookup ORA_PER_EXT_CONFIG and add below entry

Add a Lookup Code: EXT_UE_NEW with a Meaning as NO. This enables the Professional UI for View User Entity Details page

UCM Bursting Query BIP

 select to_char(call_id) as KEY,

'RolesRemoval' TEMPLATE,
'en-US' LOCALE,
'TEXT' OUTPUT_FORMAT,
'WCC' DEL_CHANNEL,
'FA_UCM_PROVISIONED' PARAMETER1,  /* Server Name */
'FAFusionImportExport' PARAMETER2,  /* Security Group */
:xdo_user_name PARAMETER3,  /* Author of the File */
'User'||to_char(call_id)||'.txt' PARAMETER5,  /* Title */
'User.dat' PARAMETER6,  /* Output File Name */
:TASK_INSTANCE_ID PARAMETER7,  /* Comments (Optional) */
'User'||to_char(call_id) PARAMETER8,   /* Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one. */
'FALSE' PARAMETER9  /* Custom metadata (true/false). Specify ‘false’.*/
from 
(
select r.call_id
from pay_flow_task_instances fti
,pay_flow_tasks_vl ft
,pay_requests r
where ft.flow_task_name = 'Generate Data'
and ft.base_flow_task_id = fti.base_flow_task_id 
and fti.flow_task_instance_id = r.flow_task_instance_id 
and r.call_type= 'ESS'
and  fti.flow_task_instance_id = :TASK_INSTANCE_ID
union
select 1234
from dual
where :TASK_INSTANCE_ID is null
)

Query for Date Parameter in the format YYYY/MM

with year as (
select yyyy from (SELECT LEVEL yyyy FROM DUAL CONNECT BY LEVEL <= EXTRACT(YEAR FROM SYSDATE)) where yyyy>=1951
),
month as (
SELECT to_char(LEVEL,'fm00') mm FROM DUAL CONNECT BY LEVEL <= EXTRACT(month FROM SYSDATE)
)
select yyyy||'/'||mm param from year y, month m
order by yyyy desc,mm desc

Sample Fast Formula - Global Absence Type Duration Formula

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

FORMULA NAME: XYZ_Client_LWP_Duration_FF
FORMULA TYPE: Global Absence Type Duration Formula
DESCRIPTION: This formula returns the duration for India Client emps for LWP absence type deducting Republic Day, Independence Day and Gandhi Jayanthi
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Aditya  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 PER_ASG_FTE_VALUE IS 1
INPUTS ARE IV_START_DATE (date), IV_END_DATE (date), IV_START_TIME(text),IV_END_TIME(text)
ln_entry_duration_d = 0
ln_subtract_duration = 0
DURATION =0
ld_period_start_date = to_date(to_char(IV_START_DATE,'DD/MM/RRRR')||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_period_end_date = to_date(to_char(IV_END_DATE,'DD/MM/RRRR')||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')

ln_entry_duration_d = days_between(ld_period_end_date,ld_period_start_date) + 1
ln_start_year = to_number(to_char(IV_START_DATE,'RRRR'))
ln_end_year = to_number(to_char(IV_END_DATE,'RRRR'))
while ln_start_year<=ln_end_year loop
(
ld_republic_day = to_date('26/01/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_independence_day = to_date('15/08/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_gandhi_jayanthi = to_date('02/10/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_republic_day,ld_period_start_date,ld_period_end_date)
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_independence_day,ld_period_start_date,ld_period_end_date)
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_gandhi_jayanthi,ld_period_start_date,ld_period_end_date)
ln_start_year = ln_start_year + 1
)
ln_entry_duration_d = ln_entry_duration_d - ln_subtract_duration
if ln_entry_duration_d > 0 then
DURATION = ln_entry_duration_d
else
DURATION = 0

RETURN DURATION

Sample Fast Formula - Global Absence Entry Validation

 /**********************************************************************
FORMULA NAME: XYZ_Client_Absence_Entry_Validation_FF
FORMULA TYPE: Global Absence Entry Validation 
DESCRIPTION : Validate Adoption Leave, Miscellaneous Maternity Leave, Maternity Leave, Sabbatical Leave, Optional Holiday, Leave Without Pay
**********************************************************************
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Aditya 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_ABSENCE_REASON IS ' '
DEFAULT FOR IV_TOTALDURATION  IS 0
DEFAULT FOR PER_ASG_MANAGER_USER_PERSON_ID  IS -1
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT_DATA_VALUE for PER_EXT_PER_ROLE_NAME is 'XYZ'
DEFAULT FOR PER_PERSON_EIT_EFF_USER_IN_Client_SABBATICAL_SABBATICAL_TAKEN is 'N'
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_STATUS_CD IS ' '
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '
DEFAULT FOR ANC_ABS_ENTRS_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ATTRIBUTE_DATE4 IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PERSON_ENTERPRISE_HIRE_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PER_LATEST_REHIRE_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PER_WORK_RELATIONSHIP_MAX_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'
DEFAULT FOR STRT_DT IS '4712/12/31 00:00:00' (date)
DEFAULT for LC_ABS_CHECK IS '0'
DEFAULT FOR l_annual_leave_balance is 0
DEFAULT FOR l_annual_leave_carryover_balance is 0
INPUTS ARE IV_END_DATE (date), IV_START_DATE (date), IV_ABSENCE_REASON, IV_TOTALDURATION
lc_absence_reason1 = 'Adopting Child with age less than 3 months'
lc_absence_reason2 = 'Adopting Child with age more than 3 months'
lc_absence_reason3 = 'Miscarriage Leave'
lc_absence_reason4 = 'Tubectomy Operation'
lc_absence_reason5 = 'Leave for illness arising out of maternity complications'
lc_absence_reason6 = 'Medical termination of pregnancy'
lc_absence_reason7 = 'Premature birth of child'
VALID           = 'Y'
lc_absence_type = ANC_ABS_TYP_NAME
lc_absence_reason = IV_ABSENCE_REASON
ln_absence_duration = IV_TOTALDURATION
lc_err = 'Error-'
LD_START_DATE = to_char(IV_START_DATE,'dd-mm-rrrr')

if lc_absence_type = 'Adoption Leave' then
(
if lc_absence_reason = lc_absence_reason1 THEN
(
if ln_absence_duration <> 84 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 84 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason2 THEN
(
if ln_absence_duration <> 28 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 28 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
)
else if lc_absence_type = 'Miscellaneous Maternity Leave' then
(
if lc_absence_reason = lc_absence_reason3 THEN
(
if ln_absence_duration <> 42 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 42 Calendar Days '
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason4 THEN
(
if ln_absence_duration <> 14 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 14 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason5 or lc_absence_reason = lc_absence_reason6 or lc_absence_reason = lc_absence_reason7 THEN
(
if ln_absence_duration <> 28 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 28 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
)
else if lc_absence_type = 'Maternity Leave' then
(
if PER_REL_ATTRIBUTE_DATE4 was defaulted THEN
(
/*STRT_DT = PER_PERSON_ENTERPRISE_HIRE_DATE*/
/*STRT_DT = PER_PER_LATEST_REHIRE_DATE*/
/*STRT_DT = PER_PER_WORK_RELATIONSHIP_MAX_DATE*/
STRT_DT = PER_PER_LATEST_REHIRE_DATE
)
ELSE
(
STRT_DT = PER_REL_ATTRIBUTE_DATE4
)
ln_days = DAYS_BETWEEN(IV_START_DATE,STRT_DT)
if ln_days < 80 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Employment is less than 80 calendar days, so cannot avail Maternity Leave'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
l_person_id = TO_CHAR(GET_CONTEXT(PERSON_ID,0))
L_SESSION_USER_PERSONID = GET_VALUE_SET('XYZ_SESSION_USER_PERSONID_VS')
LN_CHILD_COUNT = to_number(GET_VALUE_SET('XYZ_IN_CHILD_COUNT_VS','|=EMP_PERSON_ID='''||l_person_id||''''))
/*if ln_absence_duration <> 182 and L_SESSION_USER_PERSONID = l_person_id and LN_CHILD_COUNT <=1 and L_SESSION_USER_PERSONID<>to_char(PER_ASG_MANAGER_USER_PERSON_ID) then*/
if ln_absence_duration <> 182 and (L_SESSION_USER_PERSONID = l_person_id or L_SESSION_USER_PERSONID = to_char(PER_ASG_MANAGER_USER_PERSON_ID)) and LN_CHILD_COUNT <=1 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration should be equal to 26 weeks or 182 calendar days. Based on the start date provided, end date should be '|| to_char(add_days(IV_START_DATE,181),'dd-mon-rrrr')
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
else if ln_absence_duration <> 84 and (L_SESSION_USER_PERSONID = l_person_id or L_SESSION_USER_PERSONID = to_char(PER_ASG_MANAGER_USER_PERSON_ID)) and LN_CHILD_COUNT >= 2 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration should be equal to 12 weeks or 84 calendar days. Based on the start date provided, end date should be '|| to_char(add_days(IV_START_DATE,83),'dd-mon-rrrr')
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
lc_hr_assignment_id = TO_CHAR(GET_CONTEXT(HR_ASSIGNMENT_ID,0))
lc_ABSENCE_ENTRY_ID = TO_CHAR(GET_CONTEXT(ABSENCE_ENTRY_ID,-1))
LN_PREV_ABS_GAP = to_number(GET_VALUE_SET('XYZ_IN_MONTHS_BETWEEN_ABS_VS','|=ASG_ID='''||lc_hr_assignment_id||'''|ABS_NAME='''||lc_absence_type||'''|ABS_START_DATE='''||LD_START_DATE||'''|ABS_ENTRY_ID='''||lc_ABSENCE_ENTRY_ID||''''))
if LN_PREV_ABS_GAP < 7 THEN (
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
/*TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'||LD_START_DATE||lc_absence_type||'-'||lc_hr_assignment_id||'-'||lc_ABSENCE_ENTRY_ID||'-'||to_char(LN_PREV_ABS_GAP)*/
TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
else if lc_absence_type = 'Sabbatical Leave' then
(
if PER_PERSON_EIT_EFF_USER_IN_Client_SABBATICAL_SABBATICAL_TAKEN = 'Y' then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Sabbatical Leave has already been availed'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
l_curr_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
if days_between(IV_START_DATE,l_curr_date) < 30 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Sabbatical Leave can be availed with atleast 30 days notice'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
if PER_REL_ATTRIBUTE_DATE4 was defaulted THEN
(
/*STRT_DT = PER_PERSON_ENTERPRISE_HIRE_DATE*/
/*STRT_DT = PER_PER_LATEST_REHIRE_DATE*/
/*STRT_DT = PER_PER_WORK_RELATIONSHIP_MAX_DATE*/
STRT_DT = PER_PER_LATEST_REHIRE_DATE
)
ELSE
(
STRT_DT = PER_REL_ATTRIBUTE_DATE4
)
if months_between(IV_START_DATE,STRT_DT) < 36 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Employee has not spent minimum tenure in the organization'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
else if lc_absence_type = 'Optional Holiday' then
(
LC_ABS_CHECK = GET_VALUE_SET('XYZ_IN_CAL_EVENT_VALUE_SET','|=ABS_START_DATE='''||LD_START_DATE||'''')
if LC_ABS_CHECK = '0' THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'No Optional Holiday available on the selected date'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Leave Without Pay' then
(
ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date= to_date(to_char(IV_START_DATE,'RRRR')||'/12/31','rrrr/mm/dd')
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = LN_PERSON_ID,EFFECTIVE_DATE = ld_effective_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)
(
lc_plan_name = 'Annual Leave'
l_annual_leave_balance = GET_PLAN_BALANCE(lc_plan_name)
lc_plan_name = 'Annual Leave - Carry Over'
l_annual_leave_carryover_balance  = GET_PLAN_BALANCE(lc_plan_name)
)
if l_annual_leave_balance + l_annual_leave_carryover_balance > 0 THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence cannot be applied as Annual Leave is not exhausted'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Annual Leave' then
(
ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date= to_date(to_char(IV_START_DATE,'RRRR')||'/12/31','rrrr/mm/dd')
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = LN_PERSON_ID,EFFECTIVE_DATE = ld_effective_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)
(
lc_plan_name = 'Annual Leave'
l_annual_leave_balance = GET_PLAN_BALANCE(lc_plan_name)
lc_plan_name = 'Annual Leave - Carry Over'
l_annual_leave_carryover_balance  = GET_PLAN_BALANCE(lc_plan_name)
)
if l_annual_leave_balance + l_annual_leave_carryover_balance < IV_TOTALDURATION THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence duration is more than the Annual Leave Balance available'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Paternity Leave' then
(
lc_hr_assignment_id = TO_CHAR(GET_CONTEXT(HR_ASSIGNMENT_ID,0))
lc_ABSENCE_ENTRY_ID = TO_CHAR(GET_CONTEXT(ABSENCE_ENTRY_ID,-1))
LN_PREV_ABS_GAP = to_number(GET_VALUE_SET('XYZ_IN_MONTHS_BETWEEN_ABS_VS','|=ASG_ID='''||lc_hr_assignment_id||'''|ABS_NAME='''||lc_absence_type||'''|ABS_START_DATE='''||LD_START_DATE||'''|ABS_ENTRY_ID='''||lc_ABSENCE_ENTRY_ID||''''))
if LN_PREV_ABS_GAP < 7 THEN (
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
/*TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'||LD_START_DATE||lc_absence_type||'-'||lc_hr_assignment_id||'-'||lc_ABSENCE_ENTRY_ID||'-'||to_char(LN_PREV_ABS_GAP)*/
TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
return VALID

Sample Fast Formula - Global Absence Rollover

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

FORMULA NAME: XYZ_Annual_Leave_Rollover_FF

FORMULA TYPE: Global Absence Rollover

DESCRIPTION: This is used to calculate the Annual Leave Rollover value to the 'Annual Leave - Carry Over' plan at the end of every year for Edelweiss India emps

Change History:

Name Date Version Comments

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

Aditya Initial Version

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

DEFAULT FOR PER_ASG_BUSINESS_UNIT_NAME IS ' '

DEFAULT FOR l_plan_balance is 0

DEFAULT FOR l_rollover is 0

INPUTS ARE IV_ACCRUAL, 

IV_CARRYOVER,

IV_ACCRUALPERIODSTARTDATE (date), 

IV_ACCRUALPERIODENDDATE (date), 

IV_CALEDARSTARTDATE (date), 

IV_CALEDARENDDATE (date), 

IV_PLANENROLLMENTSTARTDATE (date), 

IV_PLANENROLLMENTENDDATE (date)

lc_plan_name = 'Annual Leave - Carry Over'

ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)

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

ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)

LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of ln_hr_assignment_id: ' || to_char(ln_hr_assignment_id))

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of LN_PERSON_ID: ' || to_char(LN_PERSON_ID))

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of ld_effective_date: ' || to_char(ld_effective_date))

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of ln_leg_group_id: ' || to_char(ln_leg_group_id))

CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = LN_PERSON_ID,EFFECTIVE_DATE = ld_effective_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)

(

l_plan_balance = GET_PLAN_BALANCE(lc_plan_name)

)

MAX_CARRYOVER = 60

l_rollover = MAX_CARRYOVER-l_plan_balance

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of l_plan_balance: ' || to_char(l_plan_balance))

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of l_rollover: ' || to_char(l_rollover))

MAX_ROLLOVER = 10

if l_rollover > MAX_ROLLOVER

THEN

(

ROLLOVER = MAX_ROLLOVER

)

ELSE 

(

ROLLOVER = l_rollover

)

L_DATA = ESS_LOG_WRITE('XYZ_Annual_Leave_Rollover_FF: Value of ROLLOVER: ' || to_char(ROLLOVER))

RETURN ROLLOVER

x