USE [HRIS]
GO

	SELECT
		NIP,
		NM_PEG,
		BIDANG,
		SATKER,
		FORMASI
	FROM
		dbo.REC_PELAMAR_INTERNAL
	WHERE
		ID_FORMASI = @id_formasi
		AND COALESCE(NIP_REKOMENDASI, '') <> ''	-- sudah rekomendasi


SELECT 
	a* 
FROM REC_KUNCI_LOWONGAN_JABATAN AS a
cross apply SplitStrings_CTE(a.LIST_FORMASI,',') AS b

SELECT * FROM REC_KUNCI_LOWONGAN_JABATAN

SELECT 
	a.KD_GOL,
	a.NM_GOL,
	a.NIP_BUAT,
	a.TANGGAL_BUAT,
	e.NM_UNIT_ORG AS Bidang,
	d.posisi AS Posisi_Jabatan,
	f.nama_satker_organisasi,
	f.SINGKATAN_UNIT_ORG
FROM 
	dbo.REC_KUNCI_LOWONGAN_JABATAN AS a
	CROSS APPLY 
	dbo.SplitStrings_CTE(a.LIST_FORMASI,',') AS b
	INNER JOIN
	dbo.REC_PTK AS c ON b.Item = c.ID_FORMASI
	INNER JOIN 
	dbo.SPG_08_JABATAN_UNIT AS d on c.KD_JABATAN = d.KD_JABATAN and c.KD_UNIT_ORG = d.KD_UNIT_ORG
	INNER JOIN 
	dbo.SPG_08_UNIT_ORGANISASI AS e ON LEFT(c.KD_UNIT_ORG,3) = LEFT(e.KD_UNIT_ORG,3) AND e.KD_ESELON = '00'
	INNER JOIN 
	dbo.SPG_08_UNIT_ORGANISASI AS f ON c.KD_UNIT_ORG = f.KD_UNIT_ORG
ORDER BY
	a.KD_GOL DESC,
	LEFT(c.KD_UNIT_ORG,3)
