Formula

Download as txt, pdf, or txt
Download as txt, pdf, or txt
You are on page 1of 5

/***

*
* Formula Name: TCC_ACCOMMODATION_ALLOWANCE
* Formula Type: Oracle Payroll
* Created: 26-Jun-2010
* Author: MHELOW
*
* Description: Calculate Accommodation for related employee
*
*/
/******************************************************************************
Site : TABUK Cement Company - TCC
Formula Name: TCC_ACCOMMODATION_ALLOWANCE
Date : 1-AUG-2015
Change Hosuing structure by using People Group
********************************************************************************/
ALIAS ASG_SALARY AS Salary
DEFAULT FOR Salary IS 0
/*****
ALIAS GROUP_KF_ACCOMMODATION_IN_TCC_BUILDING AS Accommodation
DEFAULT FOR Accommodation IS 'No'
******/
ALIAS GROUP_KF_HOUSING_TYPE AS HOUSING_TYPE
DEFAULT FOR HOUSING_TYPE is ' '

ALIAS MINIMUM_ACCOMMODATION_AMOUNT AS Minimum_Amount


DEFAULT FOR Minimum_Amount IS 0

default for PAY_PROC_PERIOD_END_DATE is '4712/12/31 00:00:00' (date)


ALIAS PAY_PROC_PERIOD_END_DATE AS payroll_end_date
default for PAY_PROC_PERIOD_START_DATE is '0001/01/01 00:00:00' (date)
ALIAS PAY_PROC_PERIOD_START_DATE AS payroll_start_date

ALIAS EMP_TERM_DATE AS L_Emp_Actual_Term_Date


ALIAS EMP_HIRE_DATE AS L_Emp_Hire_Date
DEFAULT FOR L_Emp_Actual_Term_Date IS '4712/01/31 00:00:00'(Date)
DEFAULT FOR L_Emp_Hire_Date IS '1900/01/01 00:00:00'(Date)

DEFAULT FOR ABSENCES_RESUMPTION_DATE IS '0001/01/01 00:00:00 '


ALIAS ABSENCES_RESUMPTION_DATE AS Resumption_Date
ALIAS ABSENCES_ADVANCE_PAYMENT AS Advance_Flag
DEFAULT FOR ABSENCES_ADVANCE_PAYMENT is 'No'

ALIAS ADVANCE_PAYMENT_WITH_LEAVE_END_DATE_ENTRY_VALUE AS end_date


DEFAULT FOR end_date IS '4712/12/31 00:00:00'(Date)
ALIAS ADVANCE_PAYMENT_WITH_LEAVE_START_DATE_ENTRY_VALUE AS start_date
DEFAULT FOR start_date IS '0001/01/01 00:00:00'(Date)

default for ADVANCE_PAYMENT_WITH_LEAVE_START_DATE_START_DATE is '4712/12/31


00:00:00' (date)

ALIAS EMP_TERM_DATE AS termination_date


DEFAULT FOR termination_date IS '4712/12/31 00:00:00'(Date)

/*inputs are ACCOMMODATION_AMOUNT,PAY_ON_ADVANCE*/

/* CALCULATIONS */
Resume_date = to_date (Resumption_Date,'YYYY/MM/DD HH24:MI:SS')

msg1 = 'Resumption_Date' + (Resumption_Date) + 'Advance' + (Advance_Flag)

L_Current_Month_Days = DAYS_BETWEEN(payroll_end_date, payroll_start_date) + 1


/********************* Disbaled by Ali Osman
* For Advance Payment with Leave*
if Accommodation = 'No' and (start_date >= payroll_start_date and start_date <=
payroll_end_date) then
(
ACCOMMODATION_AMOUNT = ((Salary*3)/12)

if ACCOMMODATION_AMOUNT < Minimum_Amount then


(
ACCOMMODATION_AMOUNT = Minimum_Amount
)
*****************/
/
*----------------------------------------------------------------------------------
-----*/
if (start_date >= payroll_start_date and start_date <= payroll_end_date) then
(
IF HOUSING_TYPE = '2' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*2)/12)
)
ELSE IF HOUSING_TYPE = '3' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*3)/12)
)
ELSE IF HOUSING_TYPE = '4' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*4)/12)
)
ELSE
(
ACCOMMODATION_AMOUNT = 0
)

ACCOMMODATION_AMOUNT = GREATEST(ACCOMMODATION_AMOUNT,Minimum_Amount)
L_PAY_VALUE1 = round(ACCOMMODATION_AMOUNT)
/
*----------------------------------------------------------------------------------
-----*/

/* The latest date on end date of leave month*/


LDED = last_day(end_date)
/* Days between last day on month of leave and actual last day on leave*/
DBLD = days_between(LDED ,end_date)
/* First day after last day of month of leave*/
FDLM = ADD_DAYS(LDED ,1)
/*First day on the previous month of end date for leave*/
FDPM= add_months(FDLM ,-1)
/*Months between function to return the number of months of leave except last month
of leave*/
MB = round_up(Months_between(FDPM,start_date))
/* Nnumber of days that should be paid to employee on resumption month*/
RDML = 30 - DBLD
L_PAY_VALUE2 = (L_PAY_VALUE1/30)*RDML

L_PAY_VALUE = L_PAY_VALUE1 + L_PAY_VALUE2

msg30 = 'DBLD'+to_char(DBLD)+'RDML'+to_char(RDML)+to_char(L_PAY_VALUE1)
/*months_of_leave = ROUND_UP(months_between(end_date,start_date))
L_PAY_VALUE = round(L_PAY_VALUE * months_of_leave)*/
/*msg30 = 'months_of_leave' + to_char(months_of_leave)*/

return L_PAY_VALUE,msg30
)

/* Recovery Amount*/
else if (Resume_date > payroll_end_date)
AND(Advance_Flag = 'Yes')then
(
L_PAY_VALUE = 0
return L_PAY_VALUE
)
else if (Resume_date>= payroll_start_date AND Resume_date<= payroll_end_date)
AND(Advance_Flag = 'Yes') then
(

days_between_resumption_date = days_between(payroll_end_date,Resume_date)+1
msg = 'Recovery If' +TO_CHAR(days_between_resumption_date)
ACCOMMODATION_AMOUNT = ROUND(((Salary*3)/12))

if ACCOMMODATION_AMOUNT < Minimum_Amount then


(
ACCOMMODATION_AMOUNT = Minimum_Amount
)

L_PAY_VALUE = ((ACCOMMODATION_AMOUNT/30)*days_between_resumption_date)

return L_PAY_VALUE,msg
)
else
/*******
if Accommodation = 'No' and (termination_date >= payroll_start_date and
termination_date <= payroll_end_date) then
******/

if (start_date >= payroll_start_date and start_date <= payroll_end_date) then


(
IF HOUSING_TYPE = '2' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*2)/12)
)
ELSE IF HOUSING_TYPE = '3' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*3)/12)
)
ELSE IF HOUSING_TYPE = '4' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*4)/12)
)
ELSE
(
ACCOMMODATION_AMOUNT = 0
)
ACCOMMODATION_AMOUNT = GREATEST(ACCOMMODATION_AMOUNT,Minimum_Amount)

Number_of_days_on_current_month = days_between
(payroll_end_date,payroll_start_date)+1

Number_of_working_days = days_between (termination_date,payroll_start_date)+1


/****hhhhh****/
ACCOMMODATION_AMOUNT = (((Salary / 12 ) * 3)/Number_of_days_on_current_month)*
Number_of_working_days
/****hhhhh***/
L_PAY_VALUE = round(ACCOMMODATION_AMOUNT,2)

return L_PAY_VALUE
)

else
/***********
if Accommodation = 'No' and (L_Emp_Hire_Date >= payroll_start_date and
L_Emp_Hire_Date <= payroll_end_date) then
********/
if (L_Emp_Hire_Date >= payroll_start_date and L_Emp_Hire_Date <= payroll_end_date)
then
(
IF HOUSING_TYPE = '2' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*2)/12)
)
ELSE IF HOUSING_TYPE = '3' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*3)/12)
)
ELSE IF HOUSING_TYPE = '4' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*4)/12)
)
ELSE
(
ACCOMMODATION_AMOUNT = 0
)
ACCOMMODATION_AMOUNT = GREATEST(ACCOMMODATION_AMOUNT,Minimum_Amount)
Number_of_days_on_current_month = days_between
(payroll_end_date,payroll_start_date)+1

Number_of_working_days = days_between (payroll_end_date,L_Emp_Hire_Date)+1


/*****HHHHH*********/

ACCOMMODATION_AMOUNT = (((Salary / 12 ) * 3)/Number_of_days_on_current_month)*


Number_of_working_days
/******hhhhhh*******/
L_PAY_VALUE = round(ACCOMMODATION_AMOUNT,2)

return L_PAY_VALUE
)

else
(
/** ACCOMMODATION_AMOUNT = ((Salary*3)/12) **/
IF HOUSING_TYPE = '2' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*2)/12)
)
ELSE IF HOUSING_TYPE = '3' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*3)/12)
)
ELSE IF HOUSING_TYPE = '4' THEN
(
ACCOMMODATION_AMOUNT = ((Salary*4)/12)
)
ELSE
(
ACCOMMODATION_AMOUNT = 0
)

ACCOMMODATION_AMOUNT = GREATEST(ACCOMMODATION_AMOUNT,Minimum_Amount)

L_PAY_VALUE = round(ACCOMMODATION_AMOUNT)

msg1 = ' L_PAY_VALUE' + to_char(L_PAY_VALUE)+'ACCOMMODATION_AMOUNT' +


to_char(ACCOMMODATION_AMOUNT)

return L_PAY_VALUE,msg1
)

You might also like