그룹웨어 정보목록(기록물 목록)

select
DE.DN “기안부서”,
AP.ApprovalType “상태”,
AP.regclass “등록구분”,
AP.IndexDate “등록일자”,
AP.RegNo “등록번호”,
AP.detacheddocno “첨부번호”,
AP.title “제목”,
AP.draftername “기안자(담당자)”,
AP.lastsignername “결재권자”,
AP.EnforceDate “시행일자”,
AP.Receivers “수신자”,
AP.BroadNo “문서과배부번호”,
AP.OrgRegNo “생산기관등록번호”,
AP.ElectronicDocFlag “전자기록물”,
AP.ClassificationNo “분류번호”,
AP.SpecialDoc “특수기록물”,
AP.Publication “공개여부”,
AP.PublicRestric “공개제한부분표시”,
—      AP.DocRegStatus “편철상태”,
AP.PageCnt “쪽수”
from
(
select rownum SEQ, LPAD(‘* ‘, level*4) || dept_name as DN, dept_id from dept_global where box_f=’1′
connect by PRIOR dept_id = par_id start with dept_id=’000000101′
) DE,
(
select /*+ RULE */
decode(ApprovalType,1,’기안문서’,2,’발신문서’,3,’수신문서’,4,’합의문서’,5,’감사문서’,6,’참조문서’,7,’보고문서’,8,’심사문서’,9,’공람문서’,10,’이첩하달문서’,11,’경유문서’,’미정의’) as ApprovalType,
decode(regclass,1,’전자문서 생산/발송’,2,’일반문서 접수’,3,’도면류생산발송’,4,’도면류접수’,5,’사진필름류’,6,’녹음/동영상류’,7,’카드류 생산/접수’,8,’카드류 이첩/발송’,’미정의’) as regclass,
to_char(indexdate,’yyyy.mm.dd’) as IndexDate,
decode((substr(regno,1,7) || ‘-‘ || substr(regno,8,6)),’-‘,”,(substr(regno,1,7) || ‘-‘ || substr(regno,8,6))) as RegNo,
detacheddocno,
title,
draftername,
lastsignername,
case when to_char(EnforceDate,’yyyy.mm.dd’) = ‘1970.01.01’ then ”
else to_char(EnforceDate,’yyyy.mm.dd’) end as EnforceDate,
Receivers,
BroadNo,
decode((substr(orgregno,1,7) || ‘-‘ || substr(orgregno,8,6)),’-‘,”,(substr(orgregno,1,7) || ‘-‘ || substr(orgregno,8,6))) as OrgRegNo,
decode(ElectronicDocFlag,1,’Y’,2,’N’,’N’) as ElectronicDocFlag,
decode((substr(ClassificationNo,1,7) || ‘-‘ || substr(ClassificationNo,8,8) || ‘-‘ || substr(ClassificationNo,16,4) || ‘-‘ || substr(ClassificationNo,20,6) || ‘(‘ || substr(ClassificationNo,26,3) || ‘)’),’—()’,”,(substr(ClassificationNo,1,7) || ‘-‘ || substr(ClassificationNo,8,8) || ‘-‘ || substr(ClassificationNo,16,4) || ‘-‘ || substr(ClassificationNo,20,6) || ‘(‘ || substr(ClassificationNo,26,3) || ‘)’)) as ClassificationNo,
decode(SpecialDoc,’YNNNN’,’대통령관련 기록물’,’NYNNN’,’비밀기록물’,’NNYNN’,’개별관리기록물’,’NNNYN’,’저작권보호기록물’,’NNNNY’,’특수규격기록물’,”) as SpecialDoc,
decode(Publication,1,’공개’,2,’부분공개’,3,’비공개’,’미정의’) as Publication,
PublicRestric,
decode(DocRegStatus,0,’편철진행’,1,’편철확정’,2,’생산목록보고’,3,’이관연기’,4,’이관목록보고’,6,’파일이관’,7,’이관파일 삭제’,’미정의’) as DocRegStatus,
PageCnt,
apprid,
orgapprid,
decode(wordtype,0,’97문서’,3,’2002문서’,9,’수기등록문서’,’미정의’) as wordtype,
substr(savedeptid,12,9) as dept_id,
draftdeptname,
draftdate,
decode(approvalstatus,1,’진행’,2,’보류’,4,’대기’,8,’협조대기’,16,’개인공람’,32,’삭제’,64,’공람’,128,’경유’,256,’완료’,512,’반송’,1024,’참조’,2048,’후열’,4096,’취소’,8192,’의뢰’,16384,’재의뢰’,32768,’최종결재’,’미정의’) as approvalstatus,
decode(DraftSrc,0,’일반기안’,1,’G/W 접수기안’,2,’유통접수기안’,3,’행정정보연계 접수기안’,’미정의’) as DraftSrc
from approval a,
(select fm.fldrmbrid from fldrmbr2 fm, folder f where f.applid = ‘7010’ and f.fldrid = fm.fldrid) fm2
where regclass in (‘1′,’2′)
and to_char(indexdate,’yyyy.mm.dd-hh24:mi:ss’)
between ‘2011.09.01-00:00:00’ and ‘2011.09.31-23:59:59’    — 검색일자
and fm2.fldrmbrid = a.apprid
and savedeptid in
(
SELECT ‘00000655370’ || dept_id
FROM dept_global
WHERE box_f=’1′)
order by regno asc, indexdate asc
) AP
where DE.dept_id = AP.dept_id(+)
order by SEQ