CUSTOM IDC_BTA

         select distinct a.person_id, 
	 B.full_name as Name,
	 C.location_code as "Duty Location",
	 D.job_id as "Job Opening ID",
	 D.status as "Job Opening Status",
	 to_char(D.date_from, 'DD-Mon-RRRR') as "Issued On",
	 D.name as "Jobcode/Title",
	 e.name || ' ' || d.name as "Job Pay Plan",
	 f.attempt_id as "Attempt ID",
	 g.score Result, 
	 decode(g.attempt_status,'P','Passed','F', 'Failed') AS "Test Results",
	 null as "Social Security Number",
	 A.vacancy_id || '1' as "Certificate Number"
	 from PER_ALL_ASSIGNMENTS_F A, 
	 PER_ALL_PEOPLE_F B,
	 HR_LOCATIONS_ALL_VL C,
	 PER_ALL_VACANCIES D,
	 per_grades e,
	 IRC_ASSIGNMENT_DETAILS_F F,
	 OTA_ATTEMPTS G,
	 per_all_people_d h
	 WHERE A.vacancy_id = :param1
	 AND A.ASSIGNMENT_TYPE != 'E'
	 AND A.EFFECTIVE_END_DATE > SYSDATE--person_id = 23206
	 AND B.person_id(+) = A.person_id
	 AND B.effective_end_date > SYSDATE
	 AND C.location_id(+) = A.location_id
	 AND d.vacancy_id(+) = A.vacancy_id
	 and e.grade_id(+) = d.grade_id
	 AND f.assignment_id(+) = A.assignment_id
	 AND g.attempt_id(+) = f.attempt_id
	 and h.person_id(+) = A.person_id
	 and h.effective_end_date > sysdate
	 order by g.score desc