SELECT
	--a.MPP_ID,
	--b.*,
	--c.*,
	b.PTK_ID,
	a.MPP_ID,
	b.JUMLAH AS realisasi_pemenuhan,
	a.KD_UNIT_ORG,
	a.NM_UNIT_ORG,
	a.KD_KANTOR,
	a.NM_KANTOR,
	a.STATUS_KIRIM,
	a.STATUS_AKTIF,
	a.BIDANG,
	a.KD_SATKER,
	a.SATKER,
	a.DIREKTORAT,
	a.FORMASI,
	a.KD_JABATAN,
	a.NM_JABATAN,
	a.KD_GOL,
	a.NM_GOL,
	a.JNS_JABATAN,
	e.eligible,
	g.jml_pelamar--,
	--(
	--	SELECT
	--		COUNT(1)
	--	FROM
	--		REC_PELAMAR_INTERNAL d
	--		INNER JOIN
	--		REC_PELAMAR_RANKING e ON
	--			d.RPI_ID = e.RPI_ID
	--			AND d.ID_FORMASI = e.ID_FORMASI
	--	WHERE
	--		e.SHORT_LIST = 1
	--		AND d.REQ_ID = b.PTK_ID
	--		AND d.STATUS = '2'
	--		AND COALESCE(d.PENGANGKATAN, 0) <> '1'
	--) AS jumlah_pelamar
FROM
	REC_RENCANA_PEMENUHAN_PEG a
	INNER JOIN
	REC_PTK b ON
		a.ID_FORMASI = b.ID_FORMASI
	INNER JOIN
	REC_PERMINTAAN_ADVERTISING c ON
		b.PTK_ID = c.REQ_ID
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS eligible
		FROM
			SPG_PEGAWAI_ELIGIBLE d
		WHERE
			a.MPP_ID = d.PERIODE_PEMENUHAN
			AND d.STS_PENSIUN = 0
			AND a.NM_GOL = d.LEVEL_JABATAN
			AND a.NM_JABATAN = d.NM_JABATAN	-- ?
	) e 
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS jml_pelamar
		FROM
			REC_PELAMAR_INTERNAL f
		WHERE
			b.PTK_ID = f.REQ_ID
			AND c.REQ_ID = f.REQ_ID
			--AND f.REKOMENDASI_PIMPINAN = '1'
	) g
WHERE
	a.STATUS_KIRIM = 1
	AND a.STATUS_AKTIF = 1
ORDER BY
	a.MPP_ID DESC,
	b.KD_GOL DESC;


SELECT * FROM REC_PELAMAR_RANKING
--SELECT * FROM dbo.REC_PERMINTAAN_ADVERTISING AS rpa

SELECT * FROM REC_PELAMAR_INTERNAL
SELECT * FROM dbo.REC_PTK
SELECT * FROM REC_RENCANA_PEMENUHAN_PEG


SELECT * FROM REC_PELAMAR_INTERNAL WHERE NIP = '02921'

		SELECT
			COUNT(*) AS jml_pelamar
		FROM
			REC_PELAMAR_INTERNAL f


SELECT * FROM REC_RENCANA_PEMENUHAN_PEG
SELECT * FROM SPG_PEGAWAI_ELIGIBLE


SELECT * FROM SPG_08_UNIT_ORGANISASI