/*###############################################################################

# NAME : AZ_ANC_MARRIAGE_LEAVE_VALIDATION
# TYPE : Global Absence Entry Validation
# DESCRIPTION : This formula used for sick leave validation
################################################################################*/
DEFAULT FOR iv_start_date is '0001/01/01 [Link]' (date)
DEFAULT FOR iv_end_date is '4712/12/31 [Link]' (date)
DEFAULT FOR iv_totalduration is 0
DEFAULT FOR iv_absence_reason is 'X'
DEFAULT FOR iv_abs_start_duration is 0
DEFAULT FOR iv_abs_end_duration is 0
DEFAULT FOR iv_attribute_1 is 'X'
DEFAULT FOR iv_attribute_2 is 'X'
DEFAULT FOR iv_attribute_3 is 'X'
DEFAULT FOR iv_attribute_4 is 'X'
DEFAULT FOR iv_attribute_5 is 'X'
DEFAULT FOR iv_attribute_date1 is '4712/12/31 [Link]' (date)
DEFAULT FOR anc_abs_entrs_start_date is '4712/12/31 [Link]' (date)
DEFAULT FOR anc_abs_entrs_end_date is '4712/12/31 [Link]' (date)
DEFAULT FOR anc_abs_entrs_per_absence_entry_id is 0
DEFAULT FOR anc_abs_entrs_absence_status_cd is 'X'
DEFAULT FOR anc_abs_entrs_approval_status_cd is 'X'
DEFAULT FOR anc_abs_entrs_absence_reason_id is 0
DEFAULT FOR anc_abs_entrs_absence_type_reason_id is 0
DEFAULT FOR anc_abs_entrs_attribute_date1 is '4712/12/31 [Link]' (date)
DEFAULT FOR anc_abs_entrs_attribute1 is 'X'
DEFAULT FOR PER_ASG_PEOPLE_GROUP_SEGMENT3 is 'XX'
DEFAULT FOR per_legal_employer_country is 'XX'
DEFAULT_DATA_VALUE for anc_per_abs_entrs_absence_entry_id_arr is 0
DEFAULT FOR per_asg_person_number is 'X'
DEFAULT FOR anc_abs_entrs_end_date_duration is 0
DEFAULT FOR anc_abs_entrs_start_date_duration is 0
DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '0001/01/01 [Link]' (DATE)
DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '0001/01/01 [Link]' (DATE)
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID IS 0
DEFAULT FOR PER_ASG_ASSIGNMENT_NUMBER IS '0'

inputs are iv_start_date(date), iv_end_date (date), iv_totalduration,

iv_absence_reason, iv_start_duration, iv_end_duration, iv_abs_start_duration,
iv_abs_end_duration, iv_attribute_1, iv_attribute_2, iv_attribute_3,
iv_attribute_4, iv_attribute_5, iv_attribute_date1, IV_ACCRUALPERIODSTARTDATE
(DATE), IV_ACCRUALPERIODENDDATE (DATE)

valid = 'Y'
l_error_message = ' '
l_abs_start_date = trunc(iv_start_date)
l_abs_end_date = trunc(iv_end_date)
l_abs_dur = iv_totalduration
l_abs_entry_id = get_context(absence_entry_id,0)
l_abs_stat_cd = 'X'
l_abs_apprv_cd = 'X'
l_resump_flag = iv_absence_reason
l_resump_date = iv_attribute_date1
l_half_day_flag = iv_attribute_1
l_substitute_emp = iv_attribute_2
l_contact_no = iv_attribute_3
l_contact_email = iv_attribute_4
l_contact_address = iv_attribute_5
l_start_duration = iv_start_duration
l_end_duration = iv_end_duration
l_future_leave_start_duration = 0
l_past_leave_end_duration = 0
l_past_leave_start_duration = 0
l_future_leave_half_flag = 'X'
l_past_leave_half_flag = 'X'
l_abs_end_date_orig = l_abs_end_date
l_abs_start_date_orig = l_abs_start_date
l_abs_resump_flag_orig = l_resump_flag
l_abs_resump_date_orig = l_resump_date
l_person_number = per_asg_person_number

l_year_start = to_date('01-01-'||to_char(l_abs_start_date,
'yyyy'))
l_year_end = to_date('31-12-'||to_char(l_abs_start_date,
'yyyy'))
l_trx_type = 'C'
l_next_year_end = add_years(l_year_end, 1)
l_prev_year_start = add_years(l_year_start, -1)
l_person_id = get_context(person_id,0)
l_absence_entry_id = get_context(absence_entry_id,0)
l_emp_category = PER_ASG_PEOPLE_GROUP_SEGMENT3
l_closest_match = 730

l_migration_date = to_date('31-07-2022')
CUR_SYS_DATE_DD = to_number
(substr(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE ,7,2))
CUR_SYS_DATE_MM = to_number
(substr(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE ,5,2))
CUR_SYS_DATE_YY = to_number
(substr(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE ,1,4))
l_sys_date = TO_DATE(to_char(CUR_SYS_DATE_DD)||'-'||
to_char(CUR_SYS_DATE_MM)||'-'||to_char(CUR_SYS_DATE_YY),'DD-MM-YYYY')

L_S_LEAVE_TYPE = 'Marriage Leave'

L_S_MAX_ELIG_DAYS = 0
L_S_TOTAL_ABS_DAYS_IN_YEAR = 0

L_S_ABS_START_DATE = TRUNC(IV_START_DATE)
L_S_ABS_END_DATE = TRUNC(IV_END_DATE)

L_S_YEAR_START = TO_DATE('01-01-'||
TO_CHAR(L_S_ABS_START_DATE,'YYYY'))
L_S_YEAR_END = TO_DATE('31-12-'||
TO_CHAR(L_S_ABS_START_DATE,'YYYY'))

L_S_NEXT_YEAR_START = ADD_YEARS(L_S_YEAR_START, 1)
L_S_NEXT_YEAR_END = ADD_YEARS(L_S_YEAR_END, 1)
L_S_PREV_YEAR_START = ADD_YEARS(L_S_YEAR_START, -1)
L_S_PREV_YEAR_END = ADD_YEARS(L_S_YEAR_END, -1)

L_S_CURRENT_SAVED_DURATION = 0
L_S_CURRENT_SAVED_DURATION_NEXT_YEAR = 0
L_S_CURRENT_SAVED_DURATION_PREV_YEAR = 0

L_S_ABSENCE_TYPE_ID = GET_CONTEXT(ABSENCE_TYPE_ID,0)
L_S_PERSON_ID = GET_CONTEXT(PERSON_ID,0)
L_S_ABSENCE_ENTRY_ID = GET_CONTEXT(ABSENCE_ENTRY_ID,0)
/*Determine Eligibility*/
L_S_EMP_COUNTRY = PER_LEGAL_EMPLOYER_COUNTRY
L_ASSIGN_NO = PER_ASG_ASSIGNMENT_NUMBER

IF L_S_EMP_COUNTRY = 'SA' THEN

(
L_S_MAX_ELIG_DAYS = 5
)

l_shift_start_date= GET_VALUE_SET('BALUBAID_SHIFT_START_DATE','|
=ASSIGNMENT_NUMBER='''||L_ASSIGN_NO||''''||'|EFFECTIVE_DATE='''||
TO_CHAR(L_S_ABS_START_DATE,'YYYY/MM/DD')|| '''')
l_shift_end_date= GET_VALUE_SET('BALUBAID_SHIFT_END_DATE','|
=ASSIGNMENT_NUMBER='''||L_ASSIGN_NO||''''||'|EFFECTIVE_DATE='''||
TO_CHAR(L_S_ABS_START_DATE,'YYYY/MM/DD')||'''')

IF l_shift_start_date = l_shift_end_date THEN

(
L_S_ABS_START_DATE = L_S_ABS_START_DATE
l_abs_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END)
l_abs_prev_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END)
l_abs_next_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END)

)
ELSE
(
L_S_ABS_START_DATE = add_days(L_S_ABS_START_DATE,1)
l_abs_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END) -1
l_abs_prev_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END) -1
l_abs_next_year =
GET_ABSENCE_DAYS_PER_TYPE(L_S_LEAVE_TYPE,L_S_YEAR_START,L_S_YEAR_END) -1

CHANGE_CONTEXTS(START_DATE = L_S_PREV_YEAR_START, END_DATE = L_S_NEXT_YEAR_END,

PERSON_ID = L_S_PERSON_ID)
(
I = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.FIRST(-1)

WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.EXISTS(I) LOOP

(
CHANGE_CONTEXTS(ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[I])
(
IF ((ANC_ABS_ENTRS_ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN') OR
(ANC_ABS_ENTRS_ABSENCE_STATUS_CD <> 'SAVED') OR (anc_abs_entrs_approval_status_cd
<> 'DENIED'))
AND L_S_ABSENCE_TYPE_ID = ANC_ABS_ENTRS_ABSENCE_TYPE_ID AND
ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[I] = L_S_ABSENCE_ENTRY_ID THEN (

L_S_CURRENT_SAVED_ABS_START_DATE = TRUNC(ANC_ABS_ENTRS_START_DATE)
L_S_CURRENT_SAVED_ABS_END_DATE = TRUNC(ANC_ABS_ENTRS_END_DATE)
 IF l_shift_start_date = l_shift_end_date THEN
(
L_S_CURRENT_SAVED_DURATION =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_YEAR_END),GREATEST(L_S_CURREN
T_SAVED_ABS_START_DATE,L_S_YEAR_START))+ 1
)
ELSE
(
L_S_CURRENT_SAVED_DURATION =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_YEAR_END),GREATEST(L_S_CURREN
T_SAVED_ABS_START_DATE,L_S_YEAR_START))
)

/* E_LOG = ESS_LOG_WRITE('L_S_CURRENT_SAVED_DURATION =' ||

TO_CHAR(L_S_CURRENT_SAVED_DURATION)) */

IF (L_S_CURRENT_SAVED_ABS_END_DATE > L_S_YEAR_END) THEN

(
IF l_shift_start_date = l_shift_end_date THEN
(
L_S_CURRENT_SAVED_DURATION_NEXT_YEAR =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_NEXT_YEAR_END),GREATEST(L_S_C
URRENT_SAVED_ABS_START_DATE,L_S_NEXT_YEAR_START))+ 1
)
ELSE
(
L_S_CURRENT_SAVED_DURATION_NEXT_YEAR =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_NEXT_YEAR_END),GREATEST(L_S_C
URRENT_SAVED_ABS_START_DATE,L_S_NEXT_YEAR_START))
)
/* E_LOG = ESS_LOG_WRITE('L_S_CURRENT_SAVED_DURATION_NEXT_YEAR =' ||
TO_CHAR(L_S_CURRENT_SAVED_DURATION_NEXT_YEAR)) */
)

IF (L_S_CURRENT_SAVED_ABS_START_DATE < L_S_YEAR_START) THEN

(
IF l_shift_start_date = l_shift_end_date THEN
(
L_S_CURRENT_SAVED_DURATION_PREV_YEAR =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_PREV_YEAR_END),GREATEST(L_S_C
URRENT_SAVED_ABS_START_DATE,L_S_PREV_YEAR_START))+ 1
)
ELSE
(
L_S_CURRENT_SAVED_DURATION_PREV_YEAR =
DAYS_BETWEEN(LEAST(L_S_CURRENT_SAVED_ABS_END_DATE,L_S_PREV_YEAR_END),GREATEST(L_S_C
URRENT_SAVED_ABS_START_DATE,L_S_PREV_YEAR_START))
)

/* E_LOG = ESS_LOG_WRITE('L_S_CURRENT_SAVED_DURATION_PREV_YEAR =' ||

TO_CHAR(L_S_CURRENT_SAVED_DURATION_PREV_YEAR)) */
)
)
)
I=I+1
)
)

L_S_TOTAL_ABS_DAYS_IN_YEAR = l_abs_year - L_S_CURRENT_SAVED_DURATION +

(DAYS_BETWEEN(LEAST(L_S_ABS_END_DATE, L_S_YEAR_END), GREATEST(L_S_ABS_START_DATE,
L_S_YEAR_START)) + 1)

IF (L_S_TOTAL_ABS_DAYS_IN_YEAR > L_S_MAX_ELIG_DAYS) THEN (

VALID = 'N'
)

/*check maximum day rule for the next year when the leave runs beyond the current
year*/
IF (L_S_ABS_END_DATE > L_S_YEAR_END) THEN
(
L_S_ABS_DAYS = l_abs_next_year - L_S_CURRENT_SAVED_DURATION_NEXT_YEAR +
(DAYS_BETWEEN(LEAST(L_S_ABS_END_DATE, L_S_NEXT_YEAR_END),
GREATEST(L_S_ABS_START_DATE, L_S_NEXT_YEAR_START)) + 1)

IF (L_S_ABS_DAYS > L_S_MAX_ELIG_DAYS) THEN (

VALID = 'N'
)
)

/*check maximum day rule for the prev year when the leave continues from previous
year*/
IF (L_S_ABS_START_DATE < L_S_YEAR_START) THEN
(
L_S_ABS_DAYS = l_abs_prev_year - L_S_CURRENT_SAVED_DURATION_PREV_YEAR +
(DAYS_BETWEEN(LEAST(L_S_ABS_END_DATE, L_S_PREV_YEAR_END),
GREATEST(L_S_ABS_START_DATE, L_S_PREV_YEAR_START)) + 1)

IF (L_S_ABS_DAYS > L_S_MAX_ELIG_DAYS) THEN (

VALID = 'N'
)
)

L_BALANCE = L_S_MAX_ELIG_DAYS - l_abs_year

/*IF L_S_TOTAL_ABS_DAYS_IN_YEAR > L_S_MAX_ELIG_DAYS THEN

(
L_BALANCE = 0
)
ELSE
(
L_BALANCE = L_BALANCE
) */

/*return error message*/

IF (VALID = 'N') THEN
(
ERROR_MESSAGE = 'AZ_MARR_LEAVE_MAX_DUR_VALID'
TOKEN_NAME[1] = 'MAX_DUR'
TOKEN_NAME[2] = 'BALANCE'
TOKEN_VALUE[1] = TO_CHAR(L_S_MAX_ELIG_DAYS)
TOKEN_VALUE[2] = TO_CHAR(L_BALANCE)

RETURN VALID, ERROR_MESSAGE, TOKEN_NAME, TOKEN_VALUE

)

return valid