--SELECT
--	*
--FROM
--	SPG_SURAT_TUGAS
--ORDER BY
--	STU_ID

--SELECT
--	*
--FROM
--	SPG_PESERTA_SURAT_TUGAS
--ORDER BY
--	STU_ID
----------------------------------------------------------------------
DECLARE 
	@nip VARCHAR(8) = '',	--'00345'
	@nama VARCHAR(100) = '',
	@no_surat VARCHAR(100) = 'ST-23/PM.1/2016'

SELECT
	sdc.NIP,
	sdc.NM_PEG,
	st.*
FROM
	SPG_DATA_CURRENT AS sdc
	OUTER APPLY
	(
		SELECT
			st.STU_ID,
			st.TYPE,
			st.NO_SURAT,
			st.TGL_SURAT,
			pst.KETERANGAN
		FROM
			SPG_SURAT_TUGAS AS st INNER JOIN
			SPG_PESERTA_SURAT_TUGAS AS pst ON st.STU_ID = pst.STU_ID
		WHERE
			(
				(
					st.NAMA_BUAT = sdc.NIP
					OR st.NAMA_UPDATE = sdc.NIP
					OR st.NIP_TTD = sdc.NIP
					OR pst.NIP = sdc.NIP
					OR pst.NAMA_UPDATE = sdc.NIP
				) OR ISNULL(sdc.NIP, '') = ''
			)
			AND (st.NO_SURAT LIKE '%'+@no_surat+'%' OR ISNULL(@no_surat, '') = '')
	) AS st
WHERE
	(sdc.NIP = @nip OR ISNULL(@nip, '') = '')
	AND (sdc.NM_PEG LIKE '%'+@nama+'%' OR ISNULL(@nama, '') = '')
	AND st.STU_ID IS NOT NULL


select fua_name,wia_accessname from tbl_user where nip='03313'--'01845'
select * from wf_inquiry_access where WIA_INQUIRYNAME='UPK'

SELECT
	ia.WIA_INQUIRYACCESS
FROM
	TBL_USER AS tu
	INNER JOIN
	WF_INQUIRY_ACCESS AS ia ON tu.WIA_ACCESSNAME = ia.WIA_ACCESSNAME
WHERE
	ia.WIA_INQUIRYNAME = 'UPK'
	AND tu.NIP = '01845'--'01845'

lokal|wilayah|pusat

- blank = diri sendiri
- local = satkernya dia
- wilayah = bidang
- pusat = all


DECLARE 
	@nip_requester VARCHAR(6) = '01845',	--'H02935',	--'03313',
	@hak_akses VARCHAR(20)
DECLARE @list_nip TABLE (nip VARCHAR(6))
SELECT
	@hak_akses = ia.WIA_INQUIRYACCESS
FROM
	TBL_USER AS tu
	INNER JOIN
	WF_INQUIRY_ACCESS AS ia ON tu.WIA_ACCESSNAME = ia.WIA_ACCESSNAME
WHERE
	ia.WIA_INQUIRYNAME = 'UPK'
	AND tu.NIP = @nip_requester

PRINT @hak_akses
IF COALESCE(@hak_akses, '') = ''
	INSERT INTO @list_nip
	SELECT @nip_requester
ELSE 
	BEGIN
		INSERT INTO @list_nip
		SELECT DISTINCT
			list.NIP
		FROM
			(
				SELECT 
					satker.NIP
				FROM 
					dbo.SplitStrings_CTE(@hak_akses, '|') AS h INNER JOIN
					SPG_DATA_CURRENT AS sdc ON 1 = 1 INNER JOIN
					SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG INNER JOIN
					(
						SELECT
							sdc2.NIP,
							suo2.kode_satker_organisasi
						FROM
							SPG_DATA_CURRENT AS sdc2 INNER JOIN
							SPG_08_UNIT_ORGANISASI AS suo2 ON sdc2.KD_UNIT_ORG = suo2.KD_UNIT_ORG
						WHERE
							sdc2.STS_PENSIUN = '0'
					) AS satker ON suo.kode_satker_organisasi = satker.kode_satker_organisasi
				WHERE
					h.Item = 'lokal'
					AND sdc.NIP = @nip_requester

				UNION ALL

				SELECT 
					sdc2.NIP
				FROM 
					dbo.SplitStrings_CTE(@hak_akses, '|') AS h INNER JOIN
					SPG_DATA_CURRENT AS sdc ON 1 = 1 INNER JOIN
					SPG_DATA_CURRENT AS sdc2 ON LEFT(sdc.KD_UNIT_ORG, 3) = LEFT(sdc2.KD_UNIT_ORG, 3)
				WHERE
					h.Item = 'wilayah'
					AND sdc.NIP = @nip_requester
					AND sdc2.STS_PENSIUN = '0'

				UNION ALL

				SELECT 
					sdc.NIP
				FROM 
					dbo.SplitStrings_CTE(@hak_akses, '|') AS h INNER JOIN
					SPG_DATA_CURRENT AS sdc ON 1 = 1
				WHERE
					h.Item = 'pusat'
					AND sdc.STS_PENSIUN = '0'
			) AS list

	END
SELECT * FROM @list_nip

select NM_GOL from SPG_DATA_CURRENT

select * from SPG_08_UNIT_ORGANISASI
kode_satker_organisasi
103005001000000000000000
NM_UNIT_ORG
Deputi Direktur Informasi, Administrasi, dan Database Pengawasan Terintegrasi
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG LEFT JOIN
	SPG_PEGAWAI AS sp ON sdc.NIP = sp.NIP
WHERE
	sdc.STS_PENSIUN = '0'
	AND suo.nama_satker_organisasi = 'Grup Pengembangan Aplikasi Sistem Informasi'

--{
--    "error": false,
--    "force": false,
--    "total_data": 2,
--    "alert": {
--        "code": 200,
--        "message": "success"
--    },
--    "data": [
--        {
--            "nama": "RAOM",
--            "nip": "00042",
--            "jabatan": "Advisor Strategic Committee",
--            "level_jabatan": "Kepala Departemen",
--            "grade": "",
--            "bidang": "Bidang Manajemen Strategis I",
--            "satuan_kerja": "Strategic Committee",
--            "unit_kerja": "",
--            "md_jabatan": "6 Tahun 8 Bulan",
--            "md_satuan_kerja": "2 Tahun 0 Bulan",
--            "md_ojk": "7 Tahun 11 Bulan",
--            "sisa_md": "6 Tahun 3 Bulan",
--            "usia": "53 Tahun 9 Bulan",
--            "sanksi": "Tidak",
--            "no_telepon": "2xxxxxx0",
--            "no_ekstension": "3xxxxx6",
--            "email": "Rxxxx.xxx@xxx.xx.xd",
--            "keahlian": "",
--            "aktualisasi_diri": "Saudara Hartono, SH adalah kakak kandung saya",
--            "status_pegawai": "Pegawai Tetap",
--            "md_lembaga_asal": "26 Tahun 10 Bulan",
--            "md_level_jabatan": "6 Tahun 8 Bulan",
--            "tmt_berakhir_kontrak": "",
--            "tmt_pensiun": "01-04-2027",
--            "posisi": "Advisor Strategic Committee",
--            "nm_agama": "Islam",
--            "sts_perkawinan": "Belum Menikah",
--            "nm_jns_kelamin_peg": "WANITA",
--            "nm_propinsi_homebase": "DKI JAKARTA",
--            "ket_lembaga_asal": "BAPEPAM-LK",
--            "tmt_lembaga_asal": "01-02-1994",
--            "tmt_level_jabatan": "01-04-2014",
--            "tmt_jabatan": "01-01-2016",
--            "tmt_satker": "01-12-2018",
--            "email_pribadi": "exx_x@xxxxxxx.xxm",
--            "handphone_1": "0xxxxxxxx6",
--            "handphone_2": "0xxxxxxxxxx1",
--            "peserta_surat_tugas": "Tidak",
--            "stu_id": "STU-339746",
--            "kso_kasusnomor": "KSO-679977",
--            "no_surat": "ST-70/MS.4/2019",
--            "tgl_mulai_surat_tugas": "05-03-2018",
--            "tgl_selesai_surat_tugas": "19-12-2019",
--            "keterangan_surat_tugas": "Konsinyering Koordinasi antar Departemen terkait Tugas dan Fungsi Departemen Internasional di Mandarin, Jakarta pada tanggal 18-19 Desember 2019",
--            "lokasi_surat_tugas": "Jakarta",
--            "jenis_surat_tugas": "Dalam Kota Pungutan",
--            "nip_name": "00042 - RAOM"
--        },
--        {
--            "nama": "DHUO",
--            "nip": "02525",
--            "jabatan": "Analis Eksekutif Senior Grup Riset Sektor Jasa Keuangan",
--            "level_jabatan": "Direktur",
--            "grade": "",
--            "bidang": "Bidang Manajemen Strategis I tessttt",
--            "satuan_kerja": "Departemen Riset Sektor Jasa Keuangan",
--            "unit_kerja": "Grup Riset Sektor Jasa Keuangan",
--            "md_jabatan": "4 Tahun 11 Bulan",
--            "md_satuan_kerja": "2 Tahun 0 Bulan",
--            "md_ojk": "6 Tahun 11 Bulan",
--            "sisa_md": "5 Tahun 0 Bulan",
--            "usia": "55 Tahun 0 Bulan",
--            "sanksi": "Tidak",
--            "no_telepon": "2xxxxxx0",
--            "no_ekstension": "3xxxxx2",
--            "email": "e_setijawan@ojk.go.id",
--            "keahlian": "",
--            "aktualisasi_diri": "MAMPU BEKERJA SECARA TIM DAN MANDIRI, KEPEMIMPINAN DENGAN VISI DAN KETELADANAN, MEMILIKI KOMPETENSI DI BIDANG MANAJEMEN STRATEGIS,MANAJEMEN RISIKO, KEUANGAN SYARIAH, SUSTAINABLE FINANCE, GLOBAL GOVERNANCE, PENGATURAN DAN PENGAWASAN BANK, MICROFINANCE, DEVELOPMENT MANAGEMENT DAN MANAJEMEN SDM",
--            "status_pegawai": "Pegawai Tetap",
--            "md_lembaga_asal": "26 Tahun 4 Bulan",
--            "md_level_jabatan": "4 Tahun 11 Bulan",
--            "tmt_berakhir_kontrak": "",
--            "tmt_pensiun": "01-01-2026",
--            "posisi": "Analis Eksekutif Senior Grup Riset Sektor Jasa Keuangan",
--            "nm_agama": "Islam",
--            "sts_perkawinan": "Menikah",
--            "nm_jns_kelamin_peg": "PRIA",
--            "nm_propinsi_homebase": "DKI JAKARTA",
--            "ket_lembaga_asal": "BI",
--            "tmt_lembaga_asal": "09-08-1994",
--            "tmt_level_jabatan": "01-01-2016",
--            "tmt_jabatan": "01-01-2016",
--            "tmt_satker": "01-12-2018",
--            "email_pribadi": "exxxxxxxxxxxxx@xxxxx.xxm",
--            "handphone_1": "0xxxxxxxx1",
--            "handphone_2": "0xxxxxxxxxx0",
--            "peserta_surat_tugas": "Tidak",
--            "stu_id": "STU-339746",
--            "kso_kasusnomor": "KSO-679977",
--            "no_surat": "ST-70/MS.4/2019",
--            "tgl_mulai_surat_tugas": "23-08-2018",
--            "tgl_selesai_surat_tugas": "19-12-2019",
--            "keterangan_surat_tugas": "Konsinyering Koordinasi antar Departemen terkait Tugas dan Fungsi Departemen Internasional di Mandarin, Jakarta pada tanggal 18-19 Desember 2019",
--            "lokasi_surat_tugas": "Jakarta",
--            "jenis_surat_tugas": "Dalam Kota Pungutan",
--            "nip_name": "02525 - DHUO"
--        }
--    ]
--}

[ID], [NIP], [NM_PEG], [NIP_LAMA], [PATH], [NM_STATUS_KEPEG], [KD_AGAMA], [NM_AGAMA], [TGL_LAHIR_PEG], [USIA_SAAT_INI], [GEN], [TEMPAT_LAHIR_PEG], [JNS_KELAMIN_PEG], [NM_KELAMIN_PEG], [STS_PERKAWINAN], [KET_STS_KAWIN], [STS_SIPIL], [KET_STS_SIPIL], [NIP_PASANGAN], [EMAIL], [EMAIL_2], [KD_PROPINSI], [NM_PROPINSI], [KD_KABUPATEN], [NM_KABUPATEN], [KD_KECAMATAN], [NM_KECAMATAN], [KD_KELURAHAN], [NM_KELURAHAN], [JL_TMP_TINGGAL_CUR], [KD_PROPINSI_HOMEBASE], [NM_PROPINSI_HOMEBASE], [KD_KABUPATEN_HOMEBASE], [NM_KABUPATEN_HOMEBASE], [KD_PROPINSI_HOMEBASE_AWAL], [NM_PROPINSI_HOMEBASE_AWAL], [KD_KABUPATEN_HOMEBASE_AWAL], [NM_KABUPATEN_HOMEBASE_AWAL], [KD_PROPINSI_HOMEBASE_AKHIR], [NM_PROPINSI_HOMEBASE_AKHIR], [KD_KABUPATEN_HOMEBASE_AKHIR], [NM_KABUPATEN_HOMEBASE_AKHIR], [NPWP], [PHONE_KANTOR], [PHONE_KANTOR_EXT], [HANDPHONE_1], [HANDPHONE_2], [KD_STS_PENERIMAAN_PEG], [STS_PENERIMAAN_PEG], [ANGKATAN], [TAHUN_PEREKRUTAN], [STS_PENSIUN], [KET_STS_PENSIUN], [KD_TIDAK_AKTIF], [STS_TIDAK_AKTIF], [NO_KTP], [NO_REKENING], [NM_REKENING], [KD_BANK], [BANK], [NO_KARTU_KELUARGA], [KD_KANTOR], [JNS_KANTOR], [NM_KANTOR], [NM_KANTOR_KOTA], [KD_GOL], [NM_GOL], [KD_JABATAN_STR], [NM_JABATAN_STR], [NM_JABATAN_STR_SINGKAT], [KD_ESELON], [NM_ESELON], [JENIS], [KD_GRADE], [KD_COSTCENTER], [FP_ID], [KD_UNIT_ORG_INDUK], [KD_UNIT_ORG], [KD_UNIT_ES1], [KD_UNIT_ES2], [KD_UNIT_ES3], [KD_UNIT_ES4], [KD_UNIT_ES5], [KD_UNIT_ES6], [KD_UNIT_ES7], [KD_SATKER], [KODE_SATKER_ORGANISASI], [NM_UNIT_ES1], [NM_UNIT_ES2], [NM_UNIT_ES3], [NM_UNIT_ES4], [NM_UNIT_ES5], [NM_UNIT_ES6], [NM_UNIT_ES7], [NM_UNIT_ORG], [NAMA_SATKER_ORGANISASI], [SINGKATAN_UNIT_ORG], [TMT_OJK], [TMT_LEMBAGA_ASAL], [KD_LEMBAGA_ASAL], [KET_LEMBAGA_ASAL], [TMT_SATKER], [TMT_PENEMPATAN], [TMT_GOL], [TMT_JABATAN], [MASA_DINAS_JABATAN], [MASA_DINAS_LEVEL_JABATAN], [SISA_MASA_DINAS_OJK], [MASA_TAHUN_OJK], [MASA_BULAN_OJK], [TMT_PANGKAT], [TGL_MASUK], [TGL_BERHENTI], [TMT_PENSIUN], [TGL_MULAI_KONTRAK], [TGL_SELESAI_KONTRAK], [TMT_PENETAPAN], [KD_PEND_UMUM], [NM_PEND_UMUM], [NM_LEMB_PEND_FORMAL], [JURUSAN], [FAKULTAS], [NILAI], [NEGARA], [LOKASI_PEND], [STATUS_LULUS], [TGL_IJAZAH], [PFM_ID1], [TANGGAL_AWAL1], [TANGGAL_AKHIR1], [NILAI1], [LEVEL_KINERJA1], [RANGKING1], [PFM_ID2], [TANGGAL_AWAL2], [TANGGAL_AKHIR2], [NILAI2], [LEVEL_KINERJA2], [RANGKING2], [PFM_ID3], [TANGGAL_AWAL3], [TANGGAL_AKHIR3], [NILAI3], [LEVEL_KINERJA3], [RANGKING3], [HUKUMAN], [TGL_MULAI_HUKUMAN], [TGL_AKHIR_HUKUMAN], [KET_MASALAH], [JNS_TINDAKAN_ADM_LAIN], [KD_JNS_SANKSI], [JENIS_SANKSI], [KD_TINGKAT_SANKSI], [TINGKAT_SANKSI], [KATEGORI_FUNGSI], [NAMA_FUNGSI], [POSISI], [POSISI_SINGKAT], [CUTI_CLTO], [TGL_AWAL_CUTI_PEG_CLTO], [TGL_AKHIR_CUTI_PEG_CLTO], [CUTI_KEAGAMAAN], [TGL_AWAL_CUTI_PEG_KEAG], [TGL_AKHIR_CUTI_PEG_KEAG], [CUTI_MELAHIRKAN], [TGL_AWAL_CUTI_PEG_MELAHIRKAN], [TGL_AKHIR_CUTI_PEG_MELAHIRKAN], [TANGGAL_HPL], [CUTI_TAHUNAN], [TGL_AWAL_CUTI_PEG_TAHUNAN], [TGL_AKHIR_CUTI_PEG_TAHUNAN], [STATUS_CUTI], [PEGAWAI_TUGAS_BELAJAR], [NM_PEND_PTB], [NM_UNIVERSITAS_PTB], [NM_FAKULTAS_PTB], [NM_JURUSAN_PTB], [TGL_MULAI_PTB], [TGL_SELESAI_PTB], [KOTA_PTB], [JNS_BEASISWA], [NM_STATUS_JABATAN], [KET_STATUS_JABATAN], [TANGGAL_END_PLT_PLH], [NM_JABATAN_PLT_PLH], [NM_UNIT_ES_PLT_PLH], [PESERTA_SURAT_TUGAS], [STU_ID], [KSO_KASUSNOMOR], [NO_SURAT_TUGAS], [TGL_MULAI_SURAT_TUGAS], [TGL_SELESAI_SURAT_TUGAS], [KETERANGAN_SURAT_TUGAS], [LOKASI_SURAT_TUGAS], [JENIS_SURAT_TUGAS], [SATUAN_KERJA], [STS_PERUBAHAN], [BULAN_TAHUN], [BULAN], [TAHUN], [CTIME], [MTIME], [CREATE_DATE], [CREATE_TIME]