ORA : honorarium, natura, iuran THT, PPH 21 dipotong masa sebelumnya

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;

ORA : Menampilkan Data Gaji Pokok dan Tanggal Berlaku Karyawan (All)

Menampilkan Data Gaji Pokok dan Tanggal Berlaku Karyawan (All)
--
select a.nrp NRP, a.name NAMA, b.grade GOLONGAN, b.GRADE_DATE "Tanggal Berlaku Gol",
decrypt(b.gendat_nrp,salary) "GAJI POKOK (GP)",
b.salary_date "Tanggal Berlaku GP"
from herru_general_datas a,
herru_employments b where b.gendat_nrp=a.nrp and
substr(b.grade,0,1) in ('1','2','3')
order by a.nrp asc;

ORA : Gaji Pokok dan Tanggal Berlaku Karyawan Berdasarkan Tahun Grade

Menampilkan Data Gaji Pokok dan Tanggal Berlaku Karyawan Berdasarkan Tahun Grade
--
select a.nrp NRP, a.name NAMA, b.grade GOLONGAN, b.GRADE_DATE "Tanggal Berlaku Gol",
decrypt(b.gendat_nrp,salary) "GAJI POKOK (GP)",
b.salary_date "Tanggal Berlaku GP"
from herru_general_datas a,
herru_employments b where b.gendat_nrp=a.nrp and
substr(b.grade,0,1) in ('1','2','3')
and to_char(b.GRADE_DATE,'YYYY') = :tahun
order by a.nrp asc;

ORA: Gaji Pokok dan Tanggal Berlaku Karyawan Berdasarkan Tahun&Bulan Grade

Menampilkan Data Gaji Pokok dan Tanggal Berlaku Karyawan Berdasarkan Tahun&Bulan Grade
--
select a.nrp NRP, a.name NAMA, b.grade GOLONGAN, b.GRADE_DATE "Tanggal Berlaku Gol",
decrypt(b.gendat_nrp,salary) "GAJI POKOK (GP)",
b.salary_date "Tanggal Berlaku GP"
from herru_general_datas a,
herru_employments b where b.gendat_nrp=a.nrp and
substr(b.grade,0,1) in ('1','2','3')
and to_char(b.GRADE_DATE,'MM') = :bulan
and to_char(b.GRADE_DATE,'YYYY') = :tahun
order by a.nrp asc;

ORA : adjusment SPT PT.HERRU

Perubahan yang dilakukan dengan TOAD untuk koneksi ke ORacle HRis

-- SELECT no 6

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP06'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- UPDATE NO 6

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP06'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- SELECT no 7

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP07'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- UPDATE NO 7

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP07'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- SELECT no 9

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP09'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- UPDATE NO 9

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP09'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS';

-- Lihat data No.10 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP10'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update NO. 10

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP10'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--**************************************************

-- Lihat data No.13 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP13'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No. 13

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP13'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--******************************************************

-- Lihat data No.14 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP14'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.14

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP14'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--********************************************************

-- Lihat data No.16 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP16' and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.16

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP16'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--*****************************************************

-- Lihat data No.17 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP17'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.17

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP17'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--************************************************************

-- Lihat data No.18 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP18'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.18

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP18'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--************************************************************

-- Lihat data No.19 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP19'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.19

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP19'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

--************************************************************

-- Lihat data No.21 sebelumnya

select gendat_NRP,decrypt(:NRP,amount) --into varamount

from herru_taxes

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP21'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'

-- Update No.21

Update herru_taxes set amount =encrypt(:NRP,:Vmount)

where gendat_nrp = :NRP

and gendat_vend_vnd_code = :perush

and res_code = 'SP21'

and to_char(payresult_date,'YYYY') = :tahun

and res_mode = 'MDSS'