declare @kode_satker_organisasi varchar(50)
declare @KD_UNIT_ORG varchar(50)

--set @kode_satker_organisasi='101013001000000000000000'
--set @KD_UNIT_ORG='105002002001002002003000'

select* into #TempPindahPekerjaanReorg
from (
select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NAMA_BUAT as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_SURAT_TUGAS d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NAMA_BUAT=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_PERMOHONAN_CUTI d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_UPDATE_PROFIL_PEG d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_ABSENSI_MANUAL_APPROVAL d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join PAY_LEMBUR d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_REIMBURSEMENT_PROSES d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_DARMASISWA_PEG d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_IZIN_BELAJAR_PERMOHONAN d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_KONSULTASI_SDM d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_SURAT_KETERANGAN d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_DISPENSASI d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_TUGAS_BELAJAR_DEFINITIF d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join PFM_ILDP d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, '' as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_PERMINTAAN_TRAINING d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_08_UNIT_ORGANISASI f on d.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join REC_PELAMAR_INTERNAL d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_EXT_INTERVIEW d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')

union

select c.MSW_WORKFLOWNAMA, b.AKW_DISTRIBUSI, a.KSO_KASUSNOMOR, d.NIP as NIP, f.KD_UNIT_ORG, f.kode_satker_organisasi,
case when b.AKW_DISTRIBUSI='OperatorAdminSatker' then coalesce(g.OPERATOR, h.OPERATOR) else coalesce(g.VALIDATOR, h.VALIDATOR) end as KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join WF_WORKFLOWAKTIFITAS b on a.KSO_WORKFLOWKODE=b.MSW_WORKFLOWKODE and a.KSO_WORKFLOWAKTIFITASKODE=b.AKW_AKTIFITASKODE
inner join WF_WORKFLOWMASTER c on a.KSO_WORKFLOWKODE=c.MSW_WORKFLOWKODE
inner join SPG_PESERTA_SURAT_TUGAS d on a.KSO_KASUSNOMOR=d.KSO_KASUSNOMOR
inner join SPG_DATA_CURRENT e on d.NIP=e.NIP
inner join SPG_08_UNIT_ORGANISASI f on e.KD_UNIT_ORG=f.KD_UNIT_ORG
left join SPG_ADMIN_SATKER g on f.KD_UNIT_ORG=g.KD_UNIT_ORG
left join SPG_ADMIN_SATKER h on f.kode_satker_organisasi=h.KD_UNIT_ORG
where a.KSO_STATUSKASUS='OPEN' and b.AKW_DISTRIBUSI in ('OperatorAdminSatker', 'ValidatorAdminSatker')
	and coalesce(f.kode_satker_organisasi,'')=coalesce(@kode_satker_organisasi,f.kode_satker_organisasi,'')
	and coalesce(f.KD_UNIT_ORG,'')=coalesce(@KD_UNIT_ORG,f.KD_UNIT_ORG,'')
) x

update WF_OPKASUS
set KSO_USERSEKARANG=b.KSO_USERSEKARANG_Updated
from WF_OPKASUS a
inner join #TempPindahPekerjaanReorg b on a.KSO_KASUSNOMOR=b.KSO_KASUSNOMOR
where coalesce(KSO_USERSEKARANG_Updated,'')<>''

drop table #TempPindahPekerjaanReorg