SELECT DISTINCT papf.EMPLOYEE_NUMBER,
                  papf.full_name,
                  pbv.balance_name Loan_Detail,
                  MAX (pbv.VALUE) Loan_Remaining_Amount,
                  hou.name Department_Name
    FROM per_all_people_f papf,
         per_all_assignments_f paaf,
         PAY_ASSIGNMENT_ACTIONS pyaa,
         PAY_BALANCE_VALUES_V pbv,
         hr_all_organization_units hou
   WHERE     papf.PERSON_ID = paaf.PERSON_ID
         AND paaf.ASSIGNMENT_ID = pbv.ASSIGNMENT_ID
         AND paaf.organization_id = hou.organization_id
         AND pyaa.ASSIGNMENT_ACTION_ID = pbv.ASSIGNMENT_ACTION_ID
         AND pyaa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
         AND paaf.EFFECTIVE_END_DATE = TO_DATE ('31-12-4712', 'DD-MM-YYYY')
         AND pbv.BALANCE_NAME LIKE '%Loan%T%'
         AND pbv.EFFECTIVE_DATE = TO_DATE ('30/04/2021', 'DD/MM/YYYY')
GROUP BY papf.EMPLOYEE_NUMBER,
         papf.full_name,
         pbv.balance_name,
         hou.name
ORDER BY papf.EMPLOYEE_NUMBER, pbv.balance_name