Laporan DI302 - New

-- Lap DI 302
SELECT
di.dinomor DI302Nomor,to_char(di.ditanggal,'dd-MM-yyyy')DI302Tgl,
decode(b.berkasstatus,0,'Selesai',1,'Proses',2,'Blokir',3,'Batal',4,'Ditutup',
55,'Jatuh Tempo',56,'Belum Diumumkan',57,'Sedang Diumumkan',58,'Selesai Diumumkan',
99,'Berkas Manual')Status, b.berkasnomor,b.berkasyear,b.creationdate Tgl_Dftr_Berkas,b.prosedurdesc,
d.desaco8, d.desanama, e.employeenip, e.nama
FROM berkas b,dibook di,regdi rd,
(SELECT rd.regid,rd.dinomor,to_char(rd.ditanggal,'dd-MM-yyyy')ditanggal FROM regdi rd
WHERE rd.dikode = 'DI307')DI307, persillokasi pl, berkasprosedur bp, desa d, petugasukur pu, detilpetugasukur dpu, employee e
WHERE di.berkasid = b.berkasid
AND b.berkasid = bp.berkasid
AND pu.petugasukurid = dpu.petugasukurid
AND dpu.employeenip = e.employeenip
AND bp.berkasprosedurid = pu.berkasprosedurid
AND bp.berkasprosedurid = pl.berkasprosedurid
AND d.desaid = pl.desaid
AND di.dikode in ('DI302')
AND di.dikode = rd.dikode(+)
AND di.dinomor = rd.dinomor(+)
AND di.ditahun = rd.ditahun(+)
AND rd.regid = DI307.regid(+)
--AND b.prosedurdesc like '%Pemecahan%'
AND di.ditanggal between to_date('01/02/2007','dd-mm-yyyy') and to_date('28/02/2007','dd-mm-yyyy')
order by to_number(di.dinomor);