report_di_per_berkas_okeh

--SQL Master dari Joksin
select r2.berkasid,r2.prosedurcode,
max(case when r2.dikode='DI301' then r2.dinomor end) nomor301,
max(case when r2.dikode='DI301' then r2.ditanggal end) tgl301,
max(case when r2.dikode='DI208' then r2.dinomor end) nomor208,
max(case when r2.dikode='DI208' then r2.ditanggal end) tgl208
from berkas,
(select r1.berkasid,r1.prosedurcode,regdi.* from regdi,
(select *
from dibook
where (dikode='DI208' OR dikode='DI301')) r1
where regdi.DIKODE=r1.DIKODE
and regdi.dinomor=r1.dinomor
and regdi.ditahun=r1.ditahun) r2
where berkas.berkasid=r2.berkasid
and creationdate>to_date('19/01/2007','dd-mm-yyyy')
group by r2.berkasid,r2.prosedurcode
order by r2.berkasid;

--SQL jonih
select a.berkasyear,a.berkasnomor,r2.prosedurcode, b.prosedurdesc,a.berkasstatus,
max(case when r2.dikode='DI301' then r2.dinomor end) nomor301,
max(case when r2.dikode='DI301' then r2.ditanggal end) tgl301,
max(case when r2.dikode='DI208' then r2.dinomor end) nomor208,
max(case when r2.dikode='DI208' then r2.ditanggal end) tgl208
from berkas a, prosedur b,
(select r1.berkasid,r1.prosedurcode,regdi.* from regdi,
(select *
from dibook
where (dikode='DI208' OR dikode='DI301')) r1
where regdi.DIKODE=r1.DIKODE
and regdi.dinomor=r1.dinomor
and regdi.ditahun=r1.ditahun) r2
where a.berkasid=r2.berkasid
and a.kodespopp = b.kodespopp
and a.creationdate between to_date('01/01/2007','dd-mm-yyyy') and to_date('31/01/2007','dd-mm-yyyy')
group by a.berkasyear,a.berkasnomor,r2.prosedurcode, b.prosedurdesc,a.berkasstatus
order by a.berkasyear,a.berkasnomor;


--SQL asep
SELECT
b.berkasnomor,b.berkasyear,b.prosedurdesc,di.dinomor DI301Nomor,to_char(di.ditanggal,'dd-MM-yyyy')DI301Tgl,
DI208.dinomor,DI208.ditanggal,
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
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 = 'DI208')DI208
WHERE di.berkasid = b.berkasid
AND di.dikode = 'DI301'
AND di.dikode = rd.dikode(+)
AND di.dinomor = rd.dinomor(+)
AND di.ditahun = rd.ditahun(+)
AND rd.regid = DI208.regid(+)