-- 1 = Promosi
-- 2 = Mutasi
-- 3 = Demosi
-- 5 = Pegawai Baru
-- 6 = Promosi Mutasi
-- 8 = Rotasi
--10 = Reorganisasi
--11 = Penetapan Kembali
--12 = Orientasi

DECLARE
	@tanggal_buat date = GETDATE(),
	@nama_buat varchar(18) = 'SYSTEM',
	@kd_bidang_baru varchar(3) = '000',
	@kd_jns_mutasi int = 2,
	@no_sk varchar(255) = '',
	@tgl_sk date = '2023-03-01',
	@tgl_mulai date = '2023-03-01',	-- berlaku tanggal
	@tgl_efektif date = '2023-03-01',
	@kd_status_validasi char(1) = '1'

DECLARE @nip varchar(8)

DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT NIP FROM SPG_DATA_CURRENT WHERE LEFT(KD_UNIT_ORG, 3) = '107' AND STS_PENSIUN = '0' AND NIP NOT IN (SELECT NIP FROM SPG_PMK WHERE LEFT(KD_UNIT_ORG_BARU, 3) = '107' AND TGL_MULAI >= '2023-03-01' AND KD_STATUS_VALIDASI = '1') AND NIP = '01300'

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @nip  

WHILE @@FETCH_STATUS = 0  
BEGIN  

	---- insert PMK --
	DECLARE 
		@pmk_id VARCHAR(10),
		@prefix VARCHAR(3),
		@max_num BIGINT,
		@id_pmk BIGINT
	SELECT @prefix = COALESCE(MAX(LEFT(PMK_ID, 3)), 'PMK') FROM SPG_PMK WHERE PMK_ID NOT LIKE 'TMP-%'
     SELECT @max_num = COALESCE(CAST(RIGHT(MAX(PMK_ID), 6) AS BIGINT), 0) FROM SPG_PMK WHERE LEFT(PMK_ID, 4) = @prefix + '-' AND PMK_ID NOT LIKE 'TMP-%'
     SELECT @pmk_id = dbo.func_generate_nomorkolom(@prefix, @max_num)

	INSERT INTO [dbo].[SPG_PMK]
			 ([PMK_ID]
			 ,[NM_PEG]
			 ,[NIP]
			 ,[TGL_MASUK]
			 ,[CMP_ID]
			 ,[CMP_ID_BARU]
			 ,[KD_KANTOR]
			 ,[KD_KANTOR_BARU]
			 ,[KD_ESELON]
			 ,[KD_ESELON_BARU]
			 ,[KD_UNIT_ORG]
			 ,[KD_UNIT_ORG_BARU]
			 ,[NM_UNIT_ORG]
			 ,[KD_JABATAN_STR]
			 ,[KD_JABATAN_STR_BARU]
			 ,[KD_GOL]
			 ,[KD_GOL_BARU]
			 ,[KD_PANGKAT]
			 ,[KD_PANGKAT_BARU]
			 ,[NM_GOLONGAN]
			 ,[NM_GOLONGAN_BARU]
			 ,[KD_STATUS_KEPEG]
			 ,[KD_STATUS_KEPEG_BARU]
			 ,[TGL_MULAI]
			 ,[KETERANGAN]
			 ,[KSO_KASUSNOMOR]
			 ,[TANGGAL_BUAT]
			 ,[NAMA_BUAT]
			 ,[KD_JNS_MUTASI]
			 ,[NO_SK]
			 ,[TGL_SK]
			 ,[KD_STATUS_VALIDASI]
			 ,[KD_SKALA_GAJI_BARU]
			 ,[TGL_EFEKTIF]
			 ,[FlagNewChange])
		SELECT
			  @pmk_id	--<PMK_ID, char(10),>
			 ,sdc.NM_PEG	--<NM_PEG, varchar(255),>
			 ,sdc.NIP	--<NIP, varchar(18),>
			 ,sdc.TGL_MASUK	--<TGL_MASUK, date,>
			 ,sdc.CMP_ID	--<CMP_ID, varchar(10),>
			 ,sdc.CMP_ID	--<CMP_ID_BARU, varchar(10),>
			 ,sdc.KD_KANTOR	--<KD_KANTOR, varchar(32),>
			 ,sdc.KD_KANTOR	--<KD_KANTOR_BARU, varchar(32),>
			 ,sdc.KD_ESELON	--<KD_ESELON, varchar(2),>
			 ,sdc.KD_ESELON	--<KD_ESELON_BARU, varchar(2),>
			 ,sdc.KD_UNIT_ORG	--<KD_UNIT_ORG, varchar(32),>
			 ,@kd_bidang_baru + RIGHT(sdc.KD_UNIT_ORG, 21)	--<KD_UNIT_ORG_BARU, varchar(32),>
			 ,suo.NM_UNIT_ORG	--<NM_UNIT_ORG, varchar(255),>
			 ,sdc.KD_JABATAN_STR	--<KD_JABATAN_STR, varchar(16),>
			 ,sdc.KD_JABATAN_STR	--<KD_JABATAN_STR_BARU, varchar(16),>
			 ,sdc.KD_GOL	--<KD_GOL, varchar(2),>
			 ,sdc.KD_GOL	--<KD_GOL_BARU, varchar(2),>
			 ,sdc.KD_PANGKAT	--<KD_PANGKAT, varchar(2),>
			 ,sdc.KD_PANGKAT	--<KD_PANGKAT_BARU, varchar(2),>
			 ,sdc.NM_GOL	--<NM_GOLONGAN, varchar(20),>
			 ,sdc.NM_GOL	--<NM_GOLONGAN_BARU, varchar(20),>
			 ,sdc.KD_STATUS_KEPEG	--<KD_STATUS_KEPEG, varchar(8),>
			 ,sdc.KD_STATUS_KEPEG	--<KD_STATUS_KEPEG_BARU, varchar(8),>
			 ,@tgl_mulai	--<TGL_MULAI, date,>
			 ,''	--<KETERANGAN, varchar(255),>
			 ,@pmk_id	--<KSO_KASUSNOMOR, varchar(10),>
			 ,@tanggal_buat	--<TANGGAL_BUAT, date,>
			 ,@nama_buat	--<NAMA_BUAT, varchar(18),>
			 ,@kd_jns_mutasi	--<KD_JNS_MUTASI, int,>
			 ,@no_sk	--<NO_SK, varchar(255),>
			 ,@tgl_sk	--<TGL_SK, date,>
			 ,@kd_status_validasi	--<KD_STATUS_VALIDASI, char(1),>
			 ,sdc.KD_SKALA_GAJI	--<KD_SKALA_GAJI_BARU, varchar(5),>
			 ,@tgl_efektif	--<TGL_EFEKTIF, date,>
			 ,'Y'	--<FlagNewChange, char(1),>
		FROM  
			SPG_DATA_CURRENT AS sdc INNER JOIN
			SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG
		WHERE 
			sdc.NIP = @nip


	IF @kd_status_validasi = '1'
	BEGIN

		DECLARE @max_id bigint

		SELECT @max_id = MAX(ID) FROM SPG_08_RIWAYAT_JABATAN

		---- insert Riwayat Jabatan --
		INSERT INTO [dbo].[SPG_08_RIWAYAT_JABATAN]
				 ([ID]
				 ,[NIP]
				 ,[NO_SK]
				 ,[DIKELUARKAN_OLEH]
				 ,[TMT_JAB_PEG]
				 ,[KD_KANTOR]
				 ,[JNS_JABATAN]
				 ,[KD_JABATAN_STR]
				 ,[KD_UNIT_ORG]
				 ,[KD_PANGKAT]
				 ,[KD_ESELON]
				 ,[STATUS_JABATAN]
				 ,[KD_STATUS_VALIDASI]
				 ,[TMT_PENEMPATAN]
				 ,[NM_JABATAN]
				 ,[TANGGAL_BUAT]
				 ,[NAMA_BUAT]
				 ,[CMP_ID]
				 ,[KSO_KASUSNOMOR]
				 ,[STS_RIWAYAT_JABATAN]
				 ,[TMT_SATKER]
				 ,[NO_URUT_JAB]
				 ,[TANGGAL_RIWAYAT])
			SELECT TOP 1
				 ROW_NUMBER() OVER(ORDER BY ID DESC) + @max_id	--<ID, bigint,>
				 ,@nip	--<NIP, varchar(18),>
				 ,@no_sk	--<NO_SK, varchar(50),>
				 ,'1'	--<DIKELUARKAN_OLEH, varchar(50),>
				 ,TMT_JAB_PEG	--<TMT_JAB_PEG, date,>
				 ,KD_KANTOR	--<KD_KANTOR, varchar(20),>
				 ,JNS_JABATAN	--<JNS_JABATAN, char(1),>
				 ,KD_JABATAN_STR	--<KD_JABATAN_STR, varchar(5),>
				 ,@kd_bidang_baru + RIGHT(KD_UNIT_ORG, 21)	--<KD_UNIT_ORG, varchar(30),>
				 ,KD_PANGKAT	--<KD_PANGKAT, varchar(15),>
				 ,KD_ESELON	--<KD_ESELON, char(2),>
				 ,STATUS_JABATAN	--<STATUS_JABATAN, varchar(5),>
				 ,1	--<KD_STATUS_VALIDASI, smallint,>
				 ,TMT_PENEMPATAN	--<TMT_PENEMPATAN, date,>
				 ,NM_JABATAN	--<NM_JABATAN, varchar(255),>
				 ,@tanggal_buat	--<TANGGAL_BUAT, date,>
				 ,@nama_buat	--<NAMA_BUAT, varchar(18),>
				 ,CMP_ID	--<CMP_ID, varchar(10),>
				 ,@pmk_id	--<KSO_KASUSNOMOR, varchar(10),>
				 ,CASE @kd_jns_mutasi
					WHEN '1' THEN '1'
					WHEN '2' THEN '3'
					WHEN '3' THEN '9'
					WHEN '5' THEN '6'
					WHEN '6' THEN '2'
					WHEN '8' THEN '4'
					WHEN '10' THEN '8'
					WHEN '11' THEN '7'
					WHEN '12' THEN '10'
				  END	--<STS_RIWAYAT_JABATAN, varchar(20),>
				 ,TMT_SATKER	--<TMT_SATKER, date,>
				 ,NO_URUT_JAB + 1	--<NO_URUT_JAB, int,>
				 ,@tgl_mulai	--<TANGGAL_RIWAYAT, date,>
			FROM
				SPG_08_RIWAYAT_JABATAN
			WHERE 
				NIP = @nip 
				AND KD_STATUS_VALIDASI = '1' 
				AND TMT_JAB_PEG <= GETDATE() 
			ORDER BY 
				TMT_JAB_PEG DESC, 
				NO_URUT_JAB DESC

			INSERT INTO [dbo].[SPG_SK]
				 ([NO_SK]
				 ,[JNS_SK]
				 ,[TGL_SK]
				 ,[NIP])
			VALUES
				 (@no_sk
				 ,'01'
				 ,@tgl_sk
				 ,@nip)

	END
		

	FETCH NEXT FROM list_cursor INTO @nip  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 