select top.* from (SELECT a.company_id, ifsapp.Company_API.Get_Name(a.company_id) AS "Company Name", a.emp_no, a.employee_status, ifsapp.Company_Employee_Property_API.Get_Property_Value(a.COMPANY_ID, a.emp_no, 'EMPNO') AS "Payroll Number", a.employee_name AS "Employee Name", a.fname AS "First Name", a.lname AS "Last Name", ifsapp.Person_Info_API.GET_TITLE(ifsapp.COMPANY_PERSON_API.Get_Person_Id(a.company_id, a.EMP_NO)) AS "Title", a.POS_CODE as "Position Id", a.FREE_FIELD1 as "Job Title", ifsapp.COMPANY_POSITION_API.Get_Position_Title(a.COMPANY_ID, a.POS_CODE) as "Position Title", ifsapp.COMPANY_WORK_LOCATION_API.Get_Country(a.COMPANY_ID, ifsapp.Employee_Work_Location_API.Get_Current_Work_Location(a.company_id, a.EMP_NO)) AS "Work Country", ifsapp.Employee_Work_Location_API.Get_Current_Work_Location(a.company_id, a.EMP_NO) as "Work Location", a.CF$_REGION as "Region", a.CF$_DIVISION as "Division", a.company_id AS "Employing Entity", a.EMP_CAT_NAME as "Function", -- a.POS_CODE as "Manager Position ID", ifsapp.company_pers_assign_api.get_sup_pos_code(a.company_id, a.emp_no, sysdate, ifsapp.company_pers_assign_api.get_pos_code(a.company_id, a.emp_no, sysdate)) as "Manager Position ID", ifsapp.COMPANY_POSITION_API.Get_Position_Title(a.company_id, ifsapp.company_pers_assign_api.get_sup_pos_code(a.company_id, a.emp_no, sysdate, company_pers_assign_api.get_pos_code(a.company_id, a.emp_no, sysdate))) as "Manager Title", -- a.FREE_FIELD1 as "Manager Title", --a.employee_name AS "Manager name", a.sup_name AS "Manager name", TO_CHAR(ifsapp.PERS_API.Get_Date_Of_Birth(ifsapp.COMPANY_PERSON_API.Get_Person_Id(a.company_id, a.EMP_NO)), 'MM/DD/YYYY') AS "Date of birth", floor(months_between(sysdate, (ifsapp.PERS_API.Get_Date_Of_Birth(ifsapp.COMPANY_PERSON_API.Get_Person_Id(a.company_id, a.EMP_NO)))) / 12) As "Age", TO_CHAR(ifsapp.EMP_EMPLOYED_TIME_API.Get_Date_Of_Employment(a.company_id, a.EMP_NO), 'MM/DD/YYYY') AS "Hire Date", ROUND((months_between(trunc(sysdate), ifsapp.EMP_EMPLOYED_TIME_API.Get_Date_Of_Employment(a.company_id, a.EMP_NO)) / 12), 2) As "Service", /*((trunc(sysdate) - ifsapp.EMP_EMPLOYED_TIME_API.Get_Date_Of_Employment(a.company_id, a.EMP_NO))/365) As "Service",*/ to_NUMBER(pf5.property_field1) as "Notice Period in weeks", a.CF$_EXEMPT as "Exempt / Non-exempt (NA)", DECODE(ifsapp.EMPLOYEE_SALARY_API.Get_Salary_Period_Per(a.company_id, a.emp_no, SYSDATE), 'Annually', 'Salaried', 'Hourly') "Salaried or Hourly", ROUND(ifsapp.EMPLOYEE_SALARY_API.Get_RATE(a.company_id, a.emp_no, 'Hourly'), 2) as "Hourly Rate (Base Currency)", ifsapp.Emp_Employed_Time_API.Get_Employment_Type(a.company_id, a.emp_no, ifsapp.Emp_Employed_Time_API.Get_Person_Date_Of_Employment(a.PERSON_ID)) As "Full Time/Part Time", ifsapp.Company_Employee_Property_API.Get_Property_Value(a.COMPANY_ID, a.emp_no, 'BANDS') AS "Bands", null as "Level", ifsapp.Company_Employee_Property_API.Get_Property_Value(a.COMPANY_ID, a.emp_no, 'GRADE') AS "Grade", ifsapp.EMP_EMPLOYED_TIME_API.Get_Degree_Of_Occupation(a.COMPANY_ID, a.emp_no, sysdate) * 100 as "% FTE", ifsapp.Employee_Salary_API.Get_Currency_Code_Per(a.company_id, a.emp_no, sysdate) AS "Currency Code", ifsapp.Employee_Salary_API.Get_Rate(a.company_id, a.emp_no, 'Annually') as "Annual Base Salary Local Currency", IFSAPP.CURRENCY_RATE_API.Get_Currency_Rate(a.company_id, 'USD', '3', SYSDATE) AS "Exchange Rate USD", ((ifsapp.Currency_Rate_API.Get_Currency_Rate(a.company_id, 'USD', '3', SYSDATE)) * ifsapp.Employee_Salary_API.Get_Rate(a.company_id, a.emp_no, 'Annually')) AS "Annual Base in USD", ROUND(ifsapp.EMPLOYEE_SALARY_API.Get_RATE(a.company_id, a.emp_no, 'Hourly'), 2) as "Hourly rate (Local)", b.CHANGE_PERCENTAGE AS "Last salary increase Amount %", TO_CHAR(b.VALID_FROM, 'MM/DD/YYYY') AS "Last salary increase Date", --b.SALARY_REASON_ID AS "Last salary increase Reason", ifsapp.REASON_FOR_SALARY_CHANGE_API.Get_Salary_Change_Description(b.COMPANY_ID, b.SALARY_REASON_ID) AS "Last salary increase Reason", pf1.property_field1 AS "Bonus Plan", pf1.property_field2 AS "Bonus Target As % Salary", pf2.property_field1 AS "Commission % Salary", pf2.property_field2 AS "Commission Number - Target Value", a.CF$_LIFE_ELIGIBILITY as "Life Eligibility", a.CF$_VACATION_ENTITLE_DAYS as "Vacation Per Annum", a.CF$_DISABILITY_INCOME as "Disability Income continuance eligibility (Yes / No)", a.CF$_PRIVATE_MEDICAL_ELG as "Private Medical Eligible", pf4.property_field1 AS "Private Medical Coverage", a.CF$_EAP_ELIGIBILITY as "EAP Eligibility", a.CF$_COMPANY_CAR_ELIGIBLE as "Company car eligibility (Yes/No)", a.CF$_ON_MOBILITY as "On Mobility", a.CF$_UNION_MEMBER as "Union Member", IFSAPP.EMP_EMPLOYED_TIME_API.Get_Agreement_Id(a.COMPANY_ID, a.emp_no, SYSDATE) as "Agreement ID", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'ACTING-UP' AND SYSDATE between t.start_date and t.end_date) AS "Acting Up Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'CAR' AND SYSDATE between t.start_date and t.end_date) AS "CAR Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'COLA' AND SYSDATE between t.start_date and t.end_date) AS "Cost of Living Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'HEALTH' AND SYSDATE between t.start_date and t.end_date) AS "Health Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'HOUSING' AND SYSDATE between t.start_date and t.end_date) AS "Housing Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'MOBILITY' AND SYSDATE between t.start_date and t.end_date) AS "Mobility Allowance", (select DECODE(t.PAY_TYPE_PERIOD, 'Monthly', t.planned_amount * 12, t.planned_amount) ANNUALLY_AMOUNT from ifsapp.EMP_ADDITIONAL_PAY t where t.company_id = a.company_id and t.emp_no = a.emp_no and t.pay_type = 'PHONE' AND SYSDATE between t.start_date and t.end_date) AS "Phone Allowance", a.CF$_DISABILITY_INCOME as "Disability Income", a.CF$_PENSION_ELIGIBLE as "Pension eligibility", pf3.property_field1 as "Pension Plan Max Employer Contr. As %", pf3.property_field2 as "Pension Employee Actual Contr. As %", pf3.property_field3 as "Pension Employeer Actual Contr. As %", a.FREE_FIELD2 as "Legacy Number" FROM company_person_all_cfv a left join employee_salary b on a.company_id = b. COMPANY_ID and a.emp_no = b.EMP_NO and b.valid_to >= SYSDATE and b.valid_from <= SYSDATE left join ifsapp.COMPANY_EMP_PROPERTY_FIELD_ALL pf1 on pf1.emp_no = a.emp_no and pf1.company_id = a.company_id and pf1.property_code = 'BONUS' and pf1.valid_to >= SYSDATE and pf1.valid_from <= SYSDATE left join ifsapp.COMPANY_EMP_PROPERTY_FIELD_ALL pf2 on pf2.emp_no = a.emp_no and pf2.company_id = a.company_id and pf2.property_code = 'COMMISSION' and pf2.valid_to >= SYSDATE and pf2.valid_from <= SYSDATE left join ifsapp.COMPANY_EMP_PROPERTY_FIELD_ALL pf3 on pf3.emp_no = a.emp_no and pf3.company_id = a.company_id and pf3.property_code = 'PENSION' and pf3.valid_to >= SYSDATE and pf3.valid_from <= SYSDATE left join ifsapp.COMPANY_EMP_PROPERTY_FIELD_ALL pf4 on pf4.emp_no = a.emp_no and pf4.company_id = a.company_id and pf4.property_code = 'PRIVATEMED' and pf4.valid_to >= SYSDATE and pf4.valid_from <= SYSDATE left join ifsapp.COMPANY_EMP_PROPERTY_FIELD_ALL pf5 on pf5.emp_no = a.emp_no and pf5.company_id = a.company_id and pf5.property_code = 'NOTICE' and pf5.valid_to >= SYSDATE and pf5.valid_from <= SYSDATE ORDER BY a.company_id, a.emp_no) top WHERE top.COMPANY_ID = NVL('&Company_ID', ifsapp.user_access_api.is_user_company_id(top.COMPANY_ID)) AND NVL(top."Function", '%') LIKE NVL('&EMP_CAT_NAME', '%') AND NVL(top."Division", '%') LIKE NVL('&Division', '%') AND NVL(top."Work Country", '%') LIKE NVL('&Country', '%') -- Lov referance is removed due to limitations in IFS cloud