SELECT TMP.PATHCD AS PATHCD , TMP.CLSNM AS CLSNM , (SELECT decode( CLAS.CLSNM, '장례미사', '미사', CLAS.CLSNM ) AS CLSSUPNM FROM AST.AYAMCLAS CLAS WHERE CLAS.CLSCD=TMP.ORDCD ) AS CLSSUPNM ,TMP.ORDCD AS ORDCD , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '01' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '01' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '01' THEN 1 ELSE 0 END ) END AS COUNT01 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '01' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM01 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '02' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '02' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '02' THEN 1 ELSE 0 END ) END AS COUNT02 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '02' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM02 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '03' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '03' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '03' THEN 1 ELSE 0 END ) END AS COUNT03 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '03' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM03 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '04' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '04' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '04' THEN 1 ELSE 0 END ) END AS COUNT04 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '04' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM04 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '05' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '05' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '05' THEN 1 ELSE 0 END ) END AS COUNT05 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '05' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM05 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '06' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '06' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '06' THEN 1 ELSE 0 END ) END AS COUNT06 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '06' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM06 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '07' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '07' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '07' THEN 1 ELSE 0 END ) END AS COUNT07 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '07' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM07 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '08' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '08' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '08' THEN 1 ELSE 0 END ) END AS COUNT08 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '08' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM08 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '09' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '09' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '09' THEN 1 ELSE 0 END ) END AS COUNT09 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '09' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM09 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '10' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '10' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '10' THEN 1 ELSE 0 END ) END AS COUNT10 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '10' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM10 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '11' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '11' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '11' THEN 1 ELSE 0 END ) END AS COUNT11 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '11' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM11 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '12' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '12' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '12' THEN 1 ELSE 0 END ) END AS COUNT12 , CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '12' THEN EFDM.TRGTPSNQTY ELSE 0 END) END AS NUM12 , ( CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '01' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '01' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '01' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '02' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '02' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '02' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '03' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '03' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '03' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '04' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '04' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '04' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '05' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '05' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '05' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '06' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '06' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '06' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '07' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '07' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '07' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '08' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '08' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '08' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '09' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '09' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '09' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '10' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '10' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '10' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '11' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '11' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '11' THEN 1 ELSE 0 END ) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN (CASE WHEN BIZA.CLSCD!='E1018' THEN SUM(CASE SUBSTR(BIZA.FTHACTDD,5,2) WHEN '12' THEN BIZA.ACTTIMS ELSE 0 END) ELSE SUM(CASE SUBSTR(BRCT.BRODCSTDD,5,2) WHEN '12' THEN 1 ELSE 0 END)END) ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '12' THEN 1 ELSE 0 END ) END )AS COUNTTOTAL ,( CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '01' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '02' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '03' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '04' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '05' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '06' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '07' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '08' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '09' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '10' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '11' THEN EFDM.TRGTPSNQTY ELSE 0 END) END + CASE TMP.CLSSUPCD WHEN 'E1003' THEN 0 ELSE SUM(CASE SUBSTR(EFDM.FROMDD,5,2) WHEN '12' THEN EFDM.TRGTPSNQTY ELSE 0 END) END ) AS NUMTOTAL FROM ( SELECT CLAS.CLSCD AS CLSCD , CLAS.CLSNM AS CLSNM , CLAS.CLSSUPCD AS CLSSUPCD , DECODE(CLAS.CLSLEVL,'3',TO_CHAR(CLSCD),TO_CHAR(CLSSUPCD)||'.'||TO_CHAR(CLSCD)) AS PATHCD , CLAS.CLSSUPCD AS ORDCD FROM AST.AYAMCLAS CLAS START WITH CLAS.CLSFLAG ='C' AND CLAS.CLSLEVL = '3' AND CLAS.DELDD = '00000000' AND CLAS.SCRNDISPYN = 'Y' AND CLAS.INSTCD ='{?instcd}' AND CLAS.CLSSUPCD ='C0003' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD UNION ALL SELECT CLAS.CLSCD AS CLSCD , CLAS.CLSNM AS CLSNM , CLAS.CLSSUPCD AS CLSSUPCD , TO_CHAR(CLSCD) AS PATHCD , CLAS.CLSSUPCD AS ORDCD FROM AST.AYAMCLAS CLAS WHERE CLAS.CLSSUPCD='E1003' AND CLAS.DELDD = '00000000' AND CLAS.INSTCD ='{?instcd}' UNION ALL SELECT CLAS.CLSCD AS CLSCD , SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSNM,'*'),2,4) CLSNM , SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSSUPCD,'*'),2,5) CLSSUPCD , 'D'||SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSCD,'*'),2,5) PATHCD , SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSSUPCD,'*'),2,5) ORDCD FROM AST.AYAMCLAS CLAS START WITH CLAS.CLSFLAG='B' AND CLAS.CLSLEVL='2' AND CLAS.DELDD='00000000' AND CLAS.SCRNDISPYN='Y' AND CLAS.INSTCD='012' AND CLAS.CLSSUPCD='B0000' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD )TMP LEFT OUTER JOIN AST.AYAHEFDM EFDM ON(EFDM.CLSCD =TMP.CLSCD AND EFDM.INSTCD='{?instcd}' AND EFDM.DELDD='00000000' AND EFDM.FROMDD BETWEEN '{?entry}'||'0101' AND '{?entry}'||'1231' ) LEFT OUTER JOIN AST.AYAHBRCT BRCT ON(BRCT.INSTCD='{?instcd}' AND BRCT.DELDD='00000000' AND TMP.CLSCD='E1018' AND BRCT.BRODCSTDD BETWEEN '{?entry}'||'0101' AND '{?entry}'||'1231' ) LEFT OUTER JOIN AST.AYAHBIZA BIZA ON(BIZA.CLSCD=TMP.CLSCD AND BIZA.INSTCD='{?instcd}' AND BIZA.FTHACTDD BETWEEN '{?entry}'||'0101' AND '{?entry}'||'1231' ) LEFT OUTER JOIN AST.AYAMCLAS CLAS ON(CLAS.CLSCD=TMP.CLSCD AND CLAS.DELDD='00000000' AND CLAS.INSTCD='{?instcd}' ) WHERE TMP.CLSCD !='C0020' GROUP BY TMP.PATHCD ,TMP.ORDCD ,TMP.CLSNM ,TMP.CLSSUPCD , BIZA.CLSCD ORDER BY PATHCD