
SELECT 
-- p.*,
RPAD( IF(LENGTH(p.id_number) = 13,COALESCE(p.id_number,''), ''),15,' ') as National_Id,
RPAD( IF(LENGTH(p.id_number) != 13,COALESCE(p.id_number,''), ''),20,' ') as Person_Alternate_Id,
RPAD(l_alt_id.che_lookup,3,' ') as Alternative_Id_Type,
RPAD(l_staff_category_id.che_lookup,5,' ') as Staff_Category_Id,
RPAD(COALESCE(DATE_FORMAT(p.staff_information->>'$.appointment_date', "%Y%m%d"),''),8,' ') as Appointment_Date,
RPAD(COALESCE(DATE_FORMAT(p.staff_information->>'$.termination_date', "%Y%m%d"),''),8,' ') as Termination_Date,
RPAD(l_staff_emp_status_id.che_lookup,10,' ') as Employment_Status_Id,
'  ' as Filler_02,
RPAD('CHED-13',20,' ') as Provider_Code,
RPAD(l_staff_highest_qualification_id.che_lookup,15,' ') as Highest_Qualification_Type_Id,
RPAD(l_staff_app_type_id.che_lookup,10,' ') as Appointment_Type_Id,
RPAD(ROUND(p.staff_information->>'$.working_hours_per_month' / 40,2),5,' ') as FTE,

RPAD(DATE_FORMAT(p.updated_at, "%Y%m%d"),8,' ') as Date_Stamp

FROM users u
LEFT JOIN profiles p
ON u.id = p.user_id
LEFT JOIN lookups l_alt_id ON l_alt_id.id = p.identification_type_id
LEFT JOIN lookups l_staff_category_id ON l_staff_category_id.id =  p.staff_information->>'$.staff_category_id'
LEFT JOIN lookups l_staff_emp_status_id ON l_staff_emp_status_id.id =  p.staff_information->>'$.employment_status_id'
LEFT JOIN lookups l_staff_highest_qualification_id ON l_staff_highest_qualification_id.id =  p.staff_information->>'$.highest_qualification_id'
LEFT JOIN lookups l_staff_app_type_id ON l_staff_app_type_id.id =  p.staff_information->>'$.appointment_type_id'

WHERE u.type_id = 2
;
