USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[rep_lowongan_jabatan_internal_per_bidang_lowjab]    Script Date: 23/03/2022 09:18:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



--ALTER PROCEDURE [dbo].[rep_lowongan_jabatan_internal_per_bidang_lowjab]
DECLARE
	@mpp_id varchar(10)  = 'MPP-000002',	-- contoh  = 'MPP-000002',
	@kd_bidang varchar(3)  = '',	-- contoh  = '101'
	@kd_gol VARCHAR(2)
--AS
BEGIN

	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.


	SELECT
		lowjab.KD_GOL,
		lowjab.id_formasi,
		lowjab.KD_UNIT_ORG,
		lowjab.bid,
		lowjab.nm_bidang,
		lowjab.depkom,
		lowjab.kantor,
		lowjab.satuan_kerja,
		lowjab.posisi_jabatan,
		lowjab.JNS_JABATAN,
		lowjab.dampak,
		--lowjab.KD_UNIT_ORG,
		lowjab.nm_bidang_sebelumnya,
		lowjab.depkom_sebelumnya,
		lowjab.satuan_kerja_sebelumnya
	FROM
		(
			select
				r.ID_FORMASI,
				LEFT(r.KD_UNIT_ORG, 3) AS bid,
				CASE
					WHEN COALESCE(d.nama_satker_organisasi, '') = '' THEN CASE
																	WHEN COALESCE(f.nm_unit_org, '') = '' THEN g.nm_unit_org
																	ELSE f.nm_unit_org
																END
					ELSE d.nama_satker_organisasi
				END AS satuan_kerja,
				c.nm_kantor as kantor,
				j.posisi as posisi_jabatan,
				l.KD_GOL,
				r.JNS_JABATAN,
				g.NM_UNIT_ORG AS nm_bidang,
				r.dampak,
				r.KD_UNIT_ORG,

				f.nm_unit_org as depkom,

				--t.nm_unit_org AS nm_unit_org_sebelumnya,
				g.NM_UNIT_ORG AS nm_bidang_sebelumnya,
				v.nm_unit_org as depkom_sebelumnya,
				CASE
					WHEN COALESCE(t.nama_satker_organisasi, '') = '' THEN CASE
																	WHEN COALESCE(v.nm_unit_org, '') = '' THEN w.nm_unit_org
																	ELSE v.nm_unit_org
																END
					ELSE t.nama_satker_organisasi
				END AS satuan_kerja_sebelumnya
				--b.req_id,a.ptk_id,d.kode_satker_organisasi as kd_satker,d.nama_satker_organisasi as satker,f.nm_unit_org as depkom,m.masa_dinas_minimal,m.sisa_dinas,m.tanggal_dinas, g.nm_unit_org as bidang,n.NM_PEND_UMUM,
				--isnull(n.KD_PEND_UMUM,0) as kd_pend_umum, a.kd_jabatan,b.tanggal_publish,b.tanggal_selesai,b.jam_selesai,m.kd_gol,a.mpp_id,m.tanggal_buat,h.NM_GOLONGAN,
				--m.nilai_kinerja as nilai1, m.nilai_kinerja_1 as nilai2, m.nilai_kinerja_2 as nilai3,
				--DATEADD(day, 0, DATEDIFF(day, 0, b.tanggal_selesai)) + DATEADD(day, 0 - DATEDIFF(day, 0, b.jam_selesai), b.jam_selesai) as closing_date, d.KD_UNIT_ES1, a.id_formasi
			from 
				dbo.REC_RENCANA_PEMENUHAN_PEG AS r INNER JOIN
				dbo.REC_PTK AS a ON r.ID_FORMASI = a.ID_FORMASI
				left join rec_permintaan_advertising b ON a.ptk_id=b.req_id
				left join SPG_08_KANTOR c on a.kd_kantor=c.kd_kantor
				left join spg_08_unit_organisasi d on a.kd_unit_org=d.kd_unit_org
				left JOIN SPG_08_UNIT_ORGANISASI e on d.KD_UNIT_ES3=e.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI f on d.KD_UNIT_ES2=f.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI g on d.KD_UNIT_ES1=g.KD_UNIT_ORG
				left join SPG_08_JABATAN_UNIT j on (a.KD_JABATAN=j.KD_JABATAN and a.KD_UNIT_ORG=j.KD_UNIT_ORG)
				left join SPG_08_JABATAN_STR k on a.kd_jabatan=k.KD_JABATAN_STR
				left join SPG_JABATAN_PANGKAT l on a.kd_jabatan=l.KD_JABATAN_STR
				left join REC_PERSYARATAN m on (m.KD_GOL=l.KD_GOL and m.periode=a.mpp_id)
				left join spg_golongan h on m.kd_gol=h.kd_gol
				left join spg_eselon i on h.KD_ESELON=i.KD_ESELON
				--left join SPG_JNS_PEND_UMUM n on m.PENDIDIKAN = n.KD_PEND_UMUM
				outer apply dbo.Split(',',d.UNIT_SEBELUMNYA) AS s
				left join spg_08_unit_organisasi t on s.split = t.kd_unit_org
				left JOIN SPG_08_UNIT_ORGANISASI u on t.KD_UNIT_ES3=u.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI v on t.KD_UNIT_ES2=v.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI w on t.KD_UNIT_ES1=w.KD_UNIT_ORG
			where 
				b.ram_id='RAM-000020' and b.rac_id='RAC-000001'
				AND r.MPP_ID = @mpp_id
				AND (LEFT(r.KD_UNIT_ORG, 3) = @kd_bidang OR ISNULL(@kd_bidang, '') = '')
				AND r.STATUS_KIRIM = 1
				AND r.STATUS_AKTIF = 1
				AND b.JAM_SELESAI IS NOT NULL	-- sudah posting
				AND (r.KD_GOL = @kd_gol OR ISNULL(@kd_gol, '') = '')
		) AS lowjab
	GROUP BY
		lowjab.KD_UNIT_ORG,
		lowjab.bid,
		lowjab.satuan_kerja,
		lowjab.kantor,
		lowjab.posisi_jabatan,
		lowjab.KD_GOL,
		lowjab.id_formasi,
		lowjab.JNS_JABATAN,
		lowjab.nm_bidang,
		lowjab.depkom,
		lowjab.dampak,
		--lowjab.KD_UNIT_ORG,
		lowjab.nm_bidang_sebelumnya,
		lowjab.depkom_sebelumnya,
		lowjab.satuan_kerja_sebelumnya
	ORDER BY
		--LEFT(suo.KD_UNIT_ORG,3),
		lowjab.bid,
		lowjab.KD_GOL DESC,
		lowjab.JNS_JABATAN DESC;
END
--281

--select * from dbo.REC_RENCANA_PEMENUHAN_PEG
select * from spg_08_unit_organisasi where UNIT_SEBELUMNYA like '%,%'

