SELECT
r.id,
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(COALESCE(c.che_code,''),20,' ') as Qualification_Code,
RPAD(COALESCE(r.year,''),4,' ') as FTE_Year,
RPAD(ROUND(SUM(credits) / 120,2),5,' ') as FTE,
RPAD(COALESCE('CHED-13',''),20,' ') as Provider_Code,
RPAD(COALESCE('621',''),10,' ') as Provider_Etqa_Id,
RPAD(DATE_FORMAT(r.updated_at, "%Y%m%d"),8,' ') Date_Stamp

FROM user_course uc
INNER JOIN registrations r ON r.course_id = uc.course_id AND r.user_id = uc.user_id AND r.deleted_at IS NULL

INNER JOIN users u ON uc.user_id = u.id
INNER JOIN profiles p ON u.id = p.user_id
INNER JOIN courses c on uc.course_id = c.id
INNER JOIN module_registration mr ON mr.registration_id = r.id AND mr.status_id = 1
INNER JOIN modules m ON m.id = mr.module_id AND m.deleted_at IS NULL
LEFT JOIN lookups l_alt_id ON l_alt_id.id = p.identification_type_id
LEFT JOIN lookups l_achieve_status ON l_achieve_status.id = uc.status_id
LEFT JOIN lookups l_achieve_type ON l_achieve_type.id = uc.achievement_type_id
LEFT JOIN lookups l_honors ON l_honors.id = uc.honors_status_id
LEFT JOIN lookups l_cesm ON l_cesm.id = c.che_cesm_code_id
GROUP BY r.id,  National_Id,Person_Alternate_Id,Alternative_Id_Type,Qualification_Code,FTE_Year,Date_Stamp
;
