--<th align=center width=10px>NO</th>
--<th align=center width=50px>LEVEL JABATAN</th>
--<th align=center width=100px>BIDANG</th>
--<th align=center width=300px>SATUAN KERJA</th>
--<th align=center width=500px>FORMASI</th>
--<th align=center>DAMPAK</th>
--<th align=center>KEBUTUHAN</th>
--<th align=center>SAAT INI</th>
--<th align=center>PEMENUHAN</th>
--<th align=center>FUNGSI</th>
SELECT 
	ROW_NUMBER() OVER (
	ORDER BY a.bidang ASC, 
	a.kd_satker ASC, 
	a.jns_jabatan DESC, 
	a.kd_gol DESC, 
	a.kd_unit_org ASC
   ) as [NO],
	a.nm_gol AS [LEVEL JABATAN], 
	a.bidang AS BIDANG, 
	a.satker AS [SATUAN KERJA],
	CASE
		WHEN ISNULL(a.penambahan_manual, 0) = 1
		THEN a.formasi + ' <i style=\"color: red;\">(Penambahan Manual)</i>'
		ELSE a.formasi
	END AS FORMASI, 
	COALESCE(a.alasan_perubahan_prev,'') AS DAMPAK,
	CASE
		WHEN jml.jumlah_mpp IS NULL
		THEN CASE
				WHEN a.jns_jabatan = 'struktural' THEN 1
				WHEN a.JNS_JABATAN = 'fungsional' AND a.JUMLAH_PEGAWAI = 0 THEN 1
				ELSE a.JUMLAH_PEGAWAI
			END
		ELSE jml.jumlah_mpp
	END AS KEBUTUHAN,
	CASE
		WHEN a.jns_jabatan = 'struktural' AND ISNULL(a.penambahan_manual, 0) = 0 THEN 0
		WHEN	a.JNS_JABATAN = 'fungsional' AND a.JUMLAH_PEGAWAI = 0 AND ISNULL(a.penambahan_manual, 0) = 0 THEN 0
		ELSE aktual.jml
	END AS [SAAT INI],
	CONVERT(VARCHAR, a.CREATED_DATE, 120) AS CREATED_DATE
----a.id, 
--a.kd_kantor, 
--a.kd_jabatan_str, 
--a.kd_unit_org,
--CASE
--	WHEN a.jns_jabatan = 'struktural'
--	THEN 1
--	WHEN	a.JNS_JABATAN = 'fungsional'AND a.JUMLAH_PEGAWAI = 0
--	THEN 1
--	ELSE a.JUMLAH_PEGAWAI
--END AS JUMLAH_PEGAWAI,
--a.kd_gol, 
--a.kd_satker, 
--a.direktorat, 
--a.periode_pemenuhan, 
--a.jns_jabatan, 
--CASE
--	WHEN b.JAM_SELESAI IS NOT NULL
--	THEN 1
--	ELSE 2
--END AS sts_posting
FROM 
	SPG_FORMASI_STRUKTURAL AS a
	LEFT JOIN
	REC_PERMINTAAN_ADVERTISING AS b ON a.ID = b.ID_FORMASI
	OUTER APPLY
	(
		SELECT 
			jumlah_mpp
		FROM 
			rec_mpp_target_count
		WHERE
			kd_jabatan_str = a.kd_jabatan_str
			AND kd_unit_org = a.kd_unit_org
			AND kd_kantor = a.kd_kantor
			AND mpp_id = a.periode_pemenuhan
	) AS jml
	OUTER APPLY
	(
		SELECT 
			COUNT(1) AS jml
		FROM 
			spg_data_current AS x
		WHERE
			x.sts_pensiun = '0'
			AND x.kd_jabatan_str = a.kd_jabatan_str
			AND x.kd_unit_org = a.kd_unit_org
	) AS aktual
WHERE 
	a.periode_pemenuhan = 'MPP-000003'
ORDER BY 
	a.bidang ASC, 
	a.kd_satker ASC, 
	a.jns_jabatan DESC, 
	a.kd_gol DESC, 
	a.kd_unit_org ASC;

--select * from SPG_FORMASI_STRUKTURAL