USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[rep_pegawai_eligible_yang_melamar_lowongan_jabatan_melamar]    Script Date: 25/05/2023 15:50:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





-- by Pribadi Pamungkas. 2021-02-16
--ALTER PROCEDURE [dbo].[rep_pegawai_eligible_yang_melamar_lowongan_jabatan_melamar]
DECLARE
	@mpp_id varchar(10) = 'MPP-000009',
	@kd_bidang VARCHAR(3) = '102',
	@kd_gol VARCHAR(2)


SELECT
	a.NIP,
	a.NM_PEG,
	a.BIDANG,
	a.SATKER,
	a.FORMASI,
	a.TANGGAL_APPLY,
	room.NM_ROOM,
	a.PRIORITAS,
	-- dibawah ini adalah lowongannya
	b.nm_bidang,
	b.kantor,
	b.satuan_kerja,
	b.posisi_jabatan
FROM
	dbo.REC_PELAMAR_INTERNAL AS a
	INNER JOIN
	--dbo.REC_PTK AS b ON a.ID_FORMASI = b.ID_FORMASI
	(
		SELECT
			x.ID_FORMASI,
			x.KD_UNIT_ORG,
			g.NM_UNIT_ORG AS nm_bidang,
			c.NM_KANTOR AS kantor,
			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,
			j.POSISI AS posisi_jabatan
		FROM
			dbo.REC_RENCANA_PEMENUHAN_PEG AS x INNER JOIN
			dbo.REC_PTK AS y ON x.ID_FORMASI = y.ID_FORMASI LEFT JOIN	-- harusnya sih inner join kali ya, tapi ngikutin yang lain aja deh
			dbo.REC_PERMINTAAN_ADVERTISING AS z ON y.PTK_ID = z.REQ_ID LEFT JOIN 
			dbo.SPG_08_KANTOR AS c ON y.KD_KANTOR = c.KD_KANTOR LEFT JOIN 
			dbo.SPG_08_UNIT_ORGANISASI AS d ON y.KD_UNIT_ORG = d.KD_UNIT_ORG LEFT JOIN 
			dbo.SPG_08_UNIT_ORGANISASI AS f ON d.KD_UNIT_ES2 = f.KD_UNIT_ORG LEFT JOIN 
			dbo.SPG_08_UNIT_ORGANISASI AS g ON d.KD_UNIT_ES1 = g.KD_UNIT_ORG LEFT JOIN 
			dbo.SPG_08_JABATAN_UNIT AS j ON (y.KD_JABATAN = j.KD_JABATAN AND y.KD_UNIT_ORG = j.KD_UNIT_ORG)
		WHERE
			z.RAM_ID = 'RAM-000020' 
			AND z.RAC_ID = 'RAC-000001'
			AND (x.MPP_ID = @mpp_id OR ISNULL(@mpp_id, '') = '')
			AND x.STATUS_KIRIM = 1
			AND x.STATUS_AKTIF = 1
			AND z.JAM_SELESAI IS NOT NULL	-- sudah posting
			AND (x.KD_GOL = @kd_gol OR ISNULL(@kd_gol, '') = '')	--varchar(2)
	) AS b ON a.ID_FORMASI = b.ID_FORMASI
	LEFT JOIN
	dbo. REC_ROOM_FORMASI_FUNCTIONAL AS room ON a.ID_FORMASI = room.ID_FORMASI and a.ID_ROOM = room.ID
WHERE
	(LEFT(a.KD_UNIT_ORG, 3) = @kd_bidang OR ISNULL(@kd_bidang, '') = '')
	--AND COALESCE(a.NIP_REKOMENDASI, '') <> ''	-- sudah rekomendasi
	--AND COALESCE(a.REKOMENDASI_PIMPINAN,'') = 'Y'
ORDER BY
	LEFT(a.KD_UNIT_ORG, 3),
	a.NM_PEG,
	a.NIP,
	a.PRIORITAS

--END
