USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[proc_eliminasi_jo]    Script Date: 19/04/2022 15:56:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--ALTER PROCEDURE [dbo].[proc_eliminasi_jo]
DECLARE
	@pi_mpp_id VARCHAR(10) = 'MPP-000002',
	@po_message VARCHAR(400) --OUTPUT
--AS
--BEGIN
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	BEGIN TRY
		BEGIN TRANSACTION

			DECLARE 
				@id_pelamar INT,
				@id_formasi INT,
				@prioritas TINYINT,
				@id_room INT

			DECLARE list_cursor CURSOR FOR  
			SELECT
				eliminasi.ID,
				eliminasi.ID_FORMASI,
				eliminasi.PRIORITAS,
				eliminasi.ID_ROOM
			FROM
				(
					SELECT DISTINCT
						a.nip
					FROM
						REC_PELAMAR_INTERNAL AS a
						INNER JOIN
						REC_RENCANA_PEMENUHAN_PEG AS b ON a.ID_FORMASI = b.ID_FORMASI
						INNER JOIN
						REC_PERMINTAAN_ADVERTISING AS c ON b.MPP_ID = c.MPP_ID AND b.ID_FORMASI = c.ID_FORMASI
					WHERE
						a.STATUS = '0'
						AND b.MPP_ID = @pi_mpp_id
						AND CAST(CONVERT(VARCHAR(10), TANGGAL_SELESAI, 126) + ' ' + JAM_SELESAI AS DATETIME) < GETDATE()	-- jika sudah selesai periode melamar
						AND CAST(CONVERT(VARCHAR(10), TGL_BATAS_WAKTU_REKOMENDASI, 126) + ' ' + JAM_BATAS_WAKTU_REKOMENDASI AS DATETIME) >= GETDATE()	-- jika masih dalam periode rekomendasi
				) AS pelamar
				OUTER APPLY
				(
					SELECT
						*
					FROM
						(
							SELECT a.ID, a.ID_FORMASI, a.PRIORITAS, a.ID_ROOM, ROW_NUMBER() OVER(ORDER BY a.PRIORITAS) RowNumber
							FROM
								REC_PELAMAR_INTERNAL AS a
								INNER JOIN
								REC_RENCANA_PEMENUHAN_PEG AS b ON a.ID_FORMASI = b.ID_FORMASI
								INNER JOIN
								REC_PERMINTAAN_ADVERTISING AS c ON b.MPP_ID = c.MPP_ID AND b.ID_FORMASI = c.ID_FORMASI
							WHERE
								a.STATUS = '0'
								AND b.MPP_ID = @pi_mpp_id
								AND CAST(CONVERT(VARCHAR(10), c.TANGGAL_SELESAI, 126) + ' ' + c.JAM_SELESAI AS DATETIME) < GETDATE()	-- jika sudah selesai periode melamar
								AND CAST(CONVERT(VARCHAR(10), c.TGL_BATAS_WAKTU_REKOMENDASI, 126) + ' ' + c.JAM_BATAS_WAKTU_REKOMENDASI AS DATETIME) >= GETDATE()	-- jika masih dalam periode rekomendasi
								AND a.NIP = pelamar.NIP
						) AS kandidat_saring
					WHERE kandidat_saring.RowNumber <> 1
				) AS eliminasi
			WHERE 
				eliminasi.ID IS NOT NULL

			OPEN list_cursor  
			FETCH NEXT FROM list_cursor INTO @id_pelamar, @id_formasi, @prioritas, @id_room

			WHILE @@FETCH_STATUS = 0  
			BEGIN  

				PRINT @id_pelamar + ' ' + @id_formasi + ' ' + @prioritas + ' ' + @id_room
				INSERT INTO dbo.REC_PELAMAR_INTERNAL_HISTORY
				(
					RPI_ID, 
					NIP, 
					NM_PEG, 
					REQ_ID, 
					TANGGAL_APPLY, 
					STATUS, 
					TANGGAL_SELEKSI, 
					NAMA_SELEKSI, 
					TANGGAL_ANGKAT, 
					NAMA_ANGKAT, 
					PEGAWAI, 
					CMP_ID, 
					KD_KANTOR, 
					KD_UNIT_ORG, 
					KD_JABATAN, 
					KD_PANGKAT, 
					KD_SKALA_GAJI, 
					NO_SK, 
					TGL_SK, 
					TGL_MULAI_BERLAKU, 
					TGL_PUBLISH, 
					KSO_KASUSNOMOR, 
					TGL_PINDAH_LOWONGAN, 
					NAMA_PINDAH_LOWONGAN, 
					TOTAL_SKOR, 
					STATUS_PUBLISH, 
					TGL_LIHAT_PUBLISH, 
					PENGANGKATAN, 
					TGL_PINDAH_SELAMBATNYA, 
					STATUS_SEBELUM_BATAL, 
					JUMLAH_PINDAH_LOWONGAN, 
					BIDANG, 
					DEPUTI_KOMISIONER, 
					DEPARTEMEN, 
					DIREKTORAT, 
					SATKER, 
					FORMASI, 
					JABATAN, 
					UNIT_ORGANISASI, 
					KD_GOL, 
					NM_GOL, 
					USIA, 
					MASA_DINAS_LVL_JABATAN, 
					MASA_DINAS_JABATAN, 
					MASA_DINAS_OJK, 
					NILAI_KINERJA_1, 
					NILAI_KINERJA_2, 
					NILAI_KINERJA_3, 
					NILAI_KINERJA_TOTAL, 
					TGL_LAHIR, 
					TEMPAT_LAHIR_PEG, 
					NM_PEND_UMUM, 
					KD_SATKER, 
					KD_JABATAN_STR, 
					ID_FORMASI, 
					ID_PELAMAR, 
					TANGGAL_BATAL, 
					STATUS_BATAL, 
					ALASAN_PINDAH_LOWONGAN, 
					PRIORITAS, 
					ID_ROOM
				)
					SELECT  
						RPI_ID, 
						NIP, 
						NM_PEG, 
						REQ_ID, 
						TANGGAL_APPLY, 
						'11', 
						TANGGAL_SELEKSI, 
						NAMA_SELEKSI, 
						TANGGAL_ANGKAT, 
						NAMA_ANGKAT, 
						PEGAWAI, 
						CMP_ID, 
						KD_KANTOR, 
						KD_UNIT_ORG, 
						KD_JABATAN, 
						KD_PANGKAT, 
						KD_SKALA_GAJI, 
						NO_SK, 
						TGL_SK, 
						TGL_MULAI_BERLAKU, 
						TGL_PUBLISH, 
						KSO_KASUSNOMOR, 
						TGL_PINDAH_LOWONGAN, 
						NAMA_PINDAH_LOWONGAN, 
						TOTAL_SKOR, 
						STATUS_PUBLISH, 
						TGL_LIHAT_PUBLISH, 
						PENGANGKATAN, 
						TGL_PINDAH_SELAMBATNYA, 
						STATUS, 
						JUMLAH_PINDAH_LOWONGAN, 
						BIDANG, 
						DEPUTI_KOMISIONER, 
						DEPARTEMEN, 
						DIREKTORAT, 
						SATKER, 
						FORMASI, 
						JABATAN, 
						UNIT_ORGANISASI, 
						KD_GOL, 
						NM_GOL, 
						USIA, 
						MASA_DINAS_LVL_JABATAN, 
						MASA_DINAS_JABATAN, 
						MASA_DINAS_OJK, 
						NILAI_KINERJA_1, 
						NILAI_KINERJA_2, 
						NILAI_KINERJA_3, 
						NILAI_KINERJA_TOTAL, 
						TGL_LAHIR, 
						TEMPAT_LAHIR_PEG, 
						NM_PEND_UMUM, 
						KD_SATKER, 
						KD_JABATAN_STR, 
						ID_FORMASI, 
						ID, 
						GETDATE(), 
						0, 
						'Tidak Dipilih', 
						PRIORITAS, 
						ID_ROOM
					FROM  
						dbo.REC_PELAMAR_INTERNAL
					WHERE 
						ID = @id_pelamar
				IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					BEGIN
						SET @po_message = 'Failed insert to REC_PELAMAR_INTERNAL_HISTORY'
						GOTO ERROR_OUT
					END

				DELETE FROM dbo.REC_PELAMAR_INTERNAL WHERE ID = @id_pelamar
				IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					BEGIN
						SET @po_message = 'Failed delete REC_PELAMAR_INTERNAL'
						GOTO ERROR_OUT
					END

				INSERT INTO dbo.REC_PELAMAR_AKTIFITAS
								(ID_PELAMAR
								,ID_FORMASI
								,STS_PELAMAR
								,KET_STS_PELAMAR
								,TGL_BUAT_AKTIFITAS
								,NIP_BUAT_AKTIFITAS
								,MPP_ID
								,PRIORITAS
								,ID_ROOM)
						SELECT
							@id_pelamar,
							@id_formasi,
							KD_STATUS_PELAMAR,
							STATUS_PELAMAR,
							GETDATE(),
							'SYSTEM',
							@pi_mpp_id,
							@prioritas,
							@id_room
						FROM
							dbo.REC_STATUS_PELAMAR
						WHERE
							KD_STATUS_PELAMAR = 11
				IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					BEGIN
						SET @po_message = 'Failed insert to REC_PELAMAR_AKTIFITAS'
						GOTO ERROR_OUT
					END


				FETCH NEXT FROM list_cursor INTO @id_pelamar, @id_formasi, @prioritas, @id_room
			END  

			CLOSE list_cursor  
			DEALLOCATE list_cursor 

		--COMMIT TRANSACTION 
		GOTO CLEAN_OUT

	END TRY
		BEGIN CATCH	-- tangkap error message selain yg sudah dideskripsikan
			SET @po_message = LEFT(ERROR_MESSAGE(),400)	-- berhubung variable diatas diset 400, maka potong error message
			GOTO ERROR_OUT
		END CATCH

	ERROR_OUT: 
		ROLLBACK TRANSACTION 
		SET @po_message = LEFT(ERROR_MESSAGE(),400)	-- berhubung variable diatas diset 400, maka potong error message
		--RETURN 1

	CLEAN_OUT:
		COMMIT TRANSACTION 
		--RETURN 0

--END


--select * from REC_RENCANA_PEMENUHAN_PEG where MPP_ID = 'MPP-000002'
