ORA : Transfer to e-SPT

script dibawah hanya untuk menampilkan kebutuhan dari e-SPT, dan dari haasil eksekusi script dibawah, dapat di copy paste ke Excell dan lakukan import dari e-SPT.

------------------------------------
select a.NAME "Nama Pegawai",a.NRP, a.ADDRESS1 Alamat, c.ref_name Jabatan,
d.ref_name "Status Kepegawaian", a.MARITAL_STATUS "Status Kawin",a.GENDER "Jenis Kelamin",
a.DEPENDANT_OF_CHILD "Jumlah Anak", decrypt(a.nrp,e.amount) "Upah",
decrypt(a.nrp,f.amount) "Tunjangan Lain"
from HERRU_general_datas a, HERRU_employments b, HERRU_references c, HERRU_references d,
HERRU_taxes e, HERRU_taxes f
where b.gendat_nrp=a.nrp and c.ref_code=b.POSITION and d.ref_code=b.EMP_STATUS
and e.gendat_nrp=a.nrp and e.res_code='SP01' and to_char(e.PAYRESULT_DATE,'YYYY') = :vYEAR
and f.gendat_nrp=a.nrp and f.res_code='SP03' and to_char(f.PAYRESULT_DATE,'YYYY') = :vYEAR
order by a.nrp asc;

-- mulai dari A4-A5
select a.NRP, decrypt(a.nrp,c.amount) "Honorarium", decrypt(a.nrp,d.amount) "Premi Asuransi"
from HERRU_general_datas a, HERRU_employments b, HERRU_taxes c, HERRU_taxes d
where b.gendat_nrp=a.nrp
and c.gendat_nrp=a.nrp and c.res_code='SP04'and to_char(c.PAYRESULT_DATE,'YYYY') = :vYEAR
and d.gendat_nrp=a.nrp and d.res_code='SP05'and to_char(d.PAYRESULT_DATE,'YYYY') = :vYEAR
order by a.nrp asc;

-- mulai dari A6,A8
select a.NRP, decrypt(a.nrp,c.amount) "Natura", decrypt(a.nrp,d.amount) "Tantiem,Bonus"
from HERRU_general_datas a, HERRU_employments b, HERRU_taxes c, HERRU_taxes d
where b.gendat_nrp=a.nrp
and c.gendat_nrp=a.nrp and c.res_code='SP06'and to_char(c.PAYRESULT_DATE,'YYYY') = :vYEAR
and d.gendat_nrp=a.nrp and d.res_code='SP08'and to_char(d.PAYRESULT_DATE,'YYYY') = :vYEAR
order by a.nrp asc;

-- mulai dari A12,A15
select a.NRP, decrypt(a.nrp,c.amount) "Iuran THT", decrypt(a.nrp,d.amount) "Penghasilan Net"
from HERRU_general_datas a, HERRU_employments b, HERRU_taxes c, HERRU_taxes d
where b.gendat_nrp=a.nrp
and c.gendat_nrp=a.nrp and c.res_code='SP12'and to_char(c.PAYRESULT_DATE,'YYYY') = :vYEAR
and d.gendat_nrp=a.nrp and d.res_code='SP15'and to_char(d.PAYRESULT_DATE,'YYYY') = :vYEAR
order by a.nrp asc;

-- mulai dari A20,A24
select a.NRP, decrypt(a.nrp,c.amount) "PPH21 Dipotong Masa Sebelumnya",
decrypt(a.nrp,d.amount) "PPH21 Terpotong & Terlunasi"
from HERRU_general_datas a, HERRU_employments b, HERRU_taxes c, HERRU_taxes d
where b.gendat_nrp=a.nrp
and c.gendat_nrp=a.nrp and c.res_code='SP20'and to_char(c.PAYRESULT_DATE,'YYYY') = :vYEAR
and d.gendat_nrp=a.nrp and d.res_code='SP21'and to_char(d.PAYRESULT_DATE,'YYYY') = :vYEAR
order by a.nrp asc;