SELECT
	HD.*,
	max( CASE WHEN d.nik = HD.PERNR AND d.tipe = "GLOBAL" THEN d.nilai_akhir END ) nilai_global,
	max( CASE WHEN d.nik = HD.PERNR AND d.tipe = "DOMESTIC" THEN d.nilai_akhir END ) nilai_domestic
FROM
	(
	SELECT
		a.nik PERNR,
		b.nama CNAME,
		b.jabatan OBJNM,
		c.BUSNM
	FROM
		(
		SELECT
			`awal`.`nik`
		FROM
			`V_PRSL` `awal`
			JOIN `assm_kompetensi_domestic` `a` ON a.nik = awal.nik
			JOIN `REKAP_PEMINATAN` `b` ON b.nik = awal.nik
			JOIN `REKAP_PEMINATAN` `c` ON c.nik = awal.nik
			JOIN `REKAP_PEMINATAN` `d` ON d.nik = awal.nik
			JOIN `KELAS_BUMN` `e` ON e.nik = awal.nik
		WHERE
			( ( ( `a`.`rekom_domestic` LIKE '%Dapat Disarankan%' ) OR ( `b`.`OBJCD` LIKE '%TRE%' ) ) OR ( `c`.`OBJCD` LIKE '%KEU%' ) )
			AND ( `d`.`OBJCD` LIKE '%INV%' )
			AND ( `e`.`BUSNM` LIKE '%Pt Bank%' )
		GROUP BY
			`nik`
		) a
		JOIN cv_personal b ON a.nik = b.nik
		JOIN KELAS_BUMN c ON b.nik = c.nik
	WHERE
		b.begda <= CURDATE( ) AND b.enda > CURDATE( )
	GROUP BY
		a.nik
	) HD
	LEFT JOIN assm_kompetensi d ON d.nik = HD.PERNR
GROUP BY
	HD.PERNR