----이크레더블 SELECT TO_CHAR(T00.RESNO) RESNO ,--사업자번호 TO_CHAR(T00.EENTNM) EENTNM ,--영문업체명 TO_CHAR(case when T00.OPEDT is null then null else substr(T00.OPEDT,1,4)||'.'||substr(T00.OPEDT,5,2)||'.'||substr(T00.OPEDT,7) end) OPEDT ,--설립일 TO_CHAR(T03.REPR_NM) REPR_NM ,--대표명 TO_CHAR(T00.TYSCALE) TYSCALE ,--대기업해당여부 TO_CHAR(T00.RELCMP) RELCMP ,--소속그룹계열 TO_CHAR(T00.ADR) ADR ,--본사주소 TO_CHAR(T00.LISTYN) LISTYN ,--상장등록법인여부(기업형태) TO_CHAR(T00.GOODSNM) GOODSNM ,--주요취급품목1 TO_CHAR(T00.TELNO) TELNO ,--본사전화번호 TO_CHAR(T00.FAXNO) FAXNO,--본사팩스번호 TO_CHAR(T12.PANMAE_DEALRAT) FSTRDRT ,--매출당사의존율(거래비중) TO_CHAR(MO_1) MO_1 , --매출순위1 TO_CHAR(MO_4) MO_4 , --매출지분1 TO_CHAR(MO_2) MO_2 , --매출순위2 TO_CHAR(MO_5) MO_5 , --매출지분2 TO_CHAR(MO_3) MO_3 , --매출순위3 TO_CHAR(MO_6) MO_6 , --매출지분3 TO_CHAR(MI_1) MI_1 , --매입순위1 TO_CHAR(MI_4) MI_4 , --매입지분1 TO_CHAR(MI_2) MI_2 , --매입순위2 TO_CHAR(MI_5) MI_5 , --매입지분2 TO_CHAR(MI_3) MI_3 , --매입순위3 TO_CHAR(MI_6) MI_6 , --매입지분3 TO_CHAR(GIBUN_RL_1) GIBUN_RL_1, --지분관계성명1 TO_CHAR(LSH_STK_RATE1) LSH_STK_RATE1, --지분관계지분1 TO_CHAR(GIBUN_RL_2) GIBUN_RL_2, --지분관계성명2 TO_CHAR(LSH_STK_RATE2) LSH_STK_RATE2, --지분관계지분2 TO_CHAR(GIBUN_RL_3) GIBUN_RL_3, --지분관계성명3 TO_CHAR(LSH_STK_RATE3) LSH_STK_RATE3, --지분관계지분3 TO_CHAR(T02.HAPGYE) HAPGYE ,--종업원수 TO_CHAR(MNE.NOTICE) NOTICE ,--공고사항 (회생/파산정보) TO_CHAR(MNE.NOTICE_DT) NOTICE_DT ,--공고게시일 (회생/파산일자) TO_CHAR(HPI.HPGBNCR_TY) HPGBNCR_TY ,--휴폐업여부 TO_CHAR(HPI.FCLOSDT) FCLOSDT ,--폐업일자 TO_CHAR(T00.LASTGRD) LASTGRD , --신용등급 TO_CHAR(T11.DECISION) DECISION , -- 현금흐름등급 TO_CHAR(RMM.FRISKRV) FRISKRV ,--RMIS레벨 TO_CHAR(RMM.EXPIRE_GB) EXPIRE_GB, --공고사항 TO_CHAR(WAT.FWATCHD) FWATCHD , --WATCH 등급 TO_CHAR(case when T00.SUVDT is null then null else substr(T00.SUVDT,1,4)||'.'||substr(T00.SUVDT,5,2)||'.'||substr(T00.SUVDT,7) end ) SUVDT ,--신용조사일(평가기준일) TO_CHAR(case when T00.ESETDT is null then null else substr(T00.ESETDT,1,4)||'.'||substr(T00.ESETDT,5,2)||'.'||substr(T00.ESETDT,7) end ) ESETDTT ,--결산일 TO_CHAR(case when T06.BS_ESETDT_0 is null then null else substr(T06.BS_ESETDT_0,1,2)||'.'||substr(T06.BS_ESETDT_0,3,2)||'.'||substr(T06.BS_ESETDT_0,5) end) bs_dt0, --재무년월 당년 TO_CHAR(case when T06.BS_ESETDT_1 is null then null else substr(T06.BS_ESETDT_1,1,2)||'.'||substr(T06.BS_ESETDT_1,3,2)||'.'||substr(T06.BS_ESETDT_1,5) end) bs_dt1, --재무년월 전년 TO_CHAR(case when T06.BS_ESETDT_2 is null then null else substr(T06.BS_ESETDT_2,1,2)||'.'||substr(T06.BS_ESETDT_2,3,2)||'.'||substr(T06.BS_ESETDT_2,5) end) bs_dt2, --재무년월 전전년 TO_CHAR(case when T06.BS_ESETDT_0 is null then null else substr(T06.BS_ESETDT_0,1,2)||'.'||substr(T06.BS_ESETDT_0,3,2)||'.'||substr(T06.BS_ESETDT_0,5) end) bs_dt3, --재무년월 당년 TO_CHAR(case when T06.BS_ESETDT_1 is null then null else substr(T06.BS_ESETDT_1,1,2)||'.'||substr(T06.BS_ESETDT_1,3,2)||'.'||substr(T06.BS_ESETDT_1,5) end) bs_dt4, --재무년월 전년 TO_CHAR(case when T06.BS_ESETDT_2 is null then null else substr(T06.BS_ESETDT_2,1,2)||'.'||substr(T06.BS_ESETDT_2,3,2)||'.'||substr(T06.BS_ESETDT_2,5) end) bs_dt5, --재무년월 전전년 trim(case when T06.BS0059_0 < 1 AND T06.BS0059_0 <> 0 then trim(to_char(T06.BS0059_0,'999,999,999,999,999.0')) when T06.BS0059_0 = 0 then '0' else to_char(T06.BS0059_0,'999,999,999,999,999') end) bs59_0, --총자산 당년 (단위:백만원) trim(case when T06.BS0059_1 < 1 AND T06.BS0059_1 <> 0 then trim(to_char(T06.BS0059_1,'999,999,999,999,999.0')) when T06.BS0059_1 = 0 then '0' else to_char(T06.BS0059_0,'999,999,999,999,999') end) bs59_1, --총자산 전년 trim(case when T06.BS0059_2 < 1 AND T06.BS0059_2 <> 0 then trim(to_char(T06.BS0059_2,'999,999,999,999,999.0')) when T06.BS0059_2 = 0 then '0' else to_char(T06.BS0059_2,'999,999,999,999,999') end) bs59_2, --총자산 전년 trim(case when T06.BS0091_0 < 1 AND T06.BS0091_0 <> 0 then trim(to_char(T06.BS0091_0,'999,999,999,999,999.0')) when T06.BS0091_0 = 0 then '0' else to_char(T06.BS0091_0,'999,999,999,999,999') end) bs91_0, --부채총계 당년 trim(case when T06.BS0091_1 < 1 AND T06.BS0091_1 <> 0 then trim(to_char(T06.BS0091_1,'999,999,999,999,999.0')) when T06.BS0091_1 = 0 then '0' else to_char(T06.BS0091_1,'999,999,999,999,999') end) bs91_1, --부채총계 전년 trim(case when T06.BS0091_2 < 1 AND T06.BS0091_2 <> 0 then trim(to_char(T06.BS0091_2,'999,999,999,999,999.0')) when T06.BS0091_2 = 0 then '0' else to_char(T06.BS0091_2,'999,999,999,999,999') end) bs91_2, --부채총계 전전년 trim(case when T06.BS0113_0 < 1 AND T06.BS0113_0 <> 0 then trim(to_char(T06.BS0113_0,'999,999,999,999,999.0')) when T06.BS0113_0 = 0 then '0' else to_char(T06.BS0113_0,'999,999,999,999,999') end) bs113_0, --자본총계 당년 trim(case when T06.BS0113_1 < 1 AND T06.BS0113_1 <> 0 then trim(to_char(T06.BS0113_1,'999,999,999,999,999.0')) when T06.BS0113_1 = 0 then '0' else to_char(T06.BS0113_1,'999,999,999,999,999') end) bs113_1, --자본총계 전년 trim(case when T06.BS0113_2 < 1 AND T06.BS0113_2 <> 0 then trim(to_char(T06.BS0113_2,'999,999,999,999,999.0')) when T06.BS0113_2 = 0 then '0' else to_char(T06.BS0113_2,'999,999,999,999,999') end) bs113_2, --자본총계 전전년 trim(case when T07.PL0001_0 < 1 AND T07.PL0001_0 <> 0 then trim(to_char(T07.PL0001_0,'999,999,999,999,999.0')) when T07.PL0001_0 = 0 then '0' else to_char(T07.PL0001_0,'999,999,999,999,999') end) pl01_0, --매출액_당기 trim(case when T07.PL0001_1 < 1 AND T07.PL0001_1 <> 0 then trim(to_char(T07.PL0001_1,'999,999,999,999,999.0')) when T07.PL0001_1 = 0 then '0' else to_char(T07.PL0001_1,'999,999,999,999,999') end) pl01_1, --매출액_전기 trim(case when T07.PL0001_2 < 1 AND T07.PL0001_2 <> 0 then trim(to_char(T07.PL0001_2,'999,999,999,999,999.0')) when T07.PL0001_2 = 0 then '0' else to_char(T07.PL0001_2,'999,999,999,999,999') end) pl01_2, --매출액_전전기 trim(case when T07.PL0027_0 < 1 AND T07.PL0027_0 <> 0 then trim(to_char(T07.PL0027_0,'999,999,999,999,999.0')) when T07.PL0027_0 = 0 then '0' else to_char(T07.PL0027_0,'999,999,999,999,999') end) pl27_0, --영업이익_당기 trim(case when T07.PL0027_1 < 1 AND T07.PL0027_1 <> 0 then trim(to_char(T07.PL0027_1,'999,999,999,999,999.0')) when T07.PL0027_1 = 0 then '0' else to_char(T07.PL0027_1,'999,999,999,999,999') end) pl27_1, --영업이익_전기 trim(case when T07.PL0027_2 < 1 AND T07.PL0027_2 <> 0 then trim(to_char(T07.PL0027_2,'999,999,999,999,999.0')) when T07.PL0027_2 = 0 then '0' else to_char(T07.PL0027_2,'999,999,999,999,999') end) pl27_2, --영업이익_전전기 trim(case when T07.PL0071_0 < 1 AND T07.PL0071_0 <> 0 then trim(to_char(T07.PL0071_0,'999,999,999,999,999.0')) when T07.PL0071_0 = 0 then '0' else to_char(T07.PL0071_0,'999,999,999,999,999') end) pl71_0, --당기순이익_당기 trim(case when T07.PL0071_1 < 1 AND T07.PL0071_1 <> 0 then trim(to_char(T07.PL0071_1,'999,999,999,999,999.0')) when T07.PL0071_1 = 0 then '0' else to_char(T07.PL0071_1,'999,999,999,999,999') end) pl71_1, --당기순이익_전기 trim(case when T07.PL0071_2 < 1 AND T07.PL0071_2 <> 0 then trim(to_char(T07.PL0071_2,'999,999,999,999,999.0')) when T07.PL0071_2 = 0 then '0' else to_char(T07.PL0071_2,'999,999,999,999,999') end) pl71_2, --당기순이익_전전기 (T10.TR0053) TR0053 ,--부채비율당기 (T10.TR0052) TR0052 ,--부채비율전기 (T10.TR0051) TR0051 ,--부채비율전전기 (T10.TR0513) TR0513 ,--차입금의존도당기 (T10.TR0512) TR0512 ,--차입금의존도전기 (T10.TR0511) TR0511 ,--차입금의존도전전기 (T10.PL0053) TR0523 ,--매출액 영업이익율당기 (T10.PL0052) TR0522 ,--매출액 영업이익율전기 (T10.PL0051) TR0521 ,--매출액 영업이익율전전기 (T10.TR0103) TR0103 ,--매출순이익율당기 (T10.TR0102) TR0102 ,--매출순이익율전기 (T10.TR0101) TR0101 ,--매출순이익율전전기 (T10.TR0223) TR0223 ,--매출액증가율당기 (T10.TR0222) TR0222 ,--매출액증가율전기 (T10.TR0221) TR0221 ,--매출액증가율전전기 (T10.TR0013) TR0013 ,--유동비율당기 (T10.TR0012) TR0012 ,--유동비율전기 (T10.TR0011) TR0011 --유동비율전전기 FROM ( SELECT SUVDT ,--신용조사일 RESNO ,--사업자번호 ESETDT ,--결산일 EENTNM ,--영문업체명 OPEDT ,--설립일 DECODE(SCALE, '대기업', '예', '아니오') AS TYSCALE ,--대기업해당여부 DECODE(R_RELCMP1, '모회사', R_ENTNM1||'('||R_RELCMP1||')', '계열사', R_ENTNM1||'('||R_RELCMP1||')') AS RELCMP ,--소속그룹계열 BONSA_ADDRESS ADR ,--본사주소 LISTYN ,--상장등록법인여부(기업형태) GOODSNM1 GOODSNM ,--주요취급품목1 BONSA_TELNO TELNO ,--본사전화번호 BONSA_FAXNO FAXNO,--본사팩스번호 LASTGRD2 LASTGRD--신용등급 FROM DCC_PACKET01 DP1 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET01 GROUP BY RESNO ) ) T00, (SELECT DP2.SUVDT , DP2.RESNO , DP2.HAPGYE--종원업수계 FROM DCC_PACKET02 DP2 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET02 GROUP BY RESNO ) )T02, (SELECT DP3.SUVDT , DP3.RESNO , LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY TRANSEQ) AS repr_nm--대표자명 FROM DCC_PACKET03 DP3 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET03 GROUP BY RESNO ) GROUP BY DP3.SUVDT, DP3.RESNO, DP3.RESNO ) T03, ( SELECT RESNO, SUVDT, STOCKNM1 AS GIBUN_RL_1, OWNRAT1 AS LSH_STK_RATE1, STOCKNM2 AS GIBUN_RL_2, OWNRAT2 AS LSH_STK_RATE2, STOCKNM3 AS GIBUN_RL_3, OWNRAT3 AS LSH_STK_RATE3 FROM DCC_PACKET04 A WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET04 GROUP BY RESNO ) ) T04, ( SELECT RESNO, SUVDT, PANMAE_ENTNM1 AS MO_1, PANMAE_DEALRAT1 AS MO_4, PANMAE_ENTNM2 AS MO_2, PANMAE_DEALRAT2 AS MO_5, PANMAE_ENTNM3 AS MO_3, PANMAE_DEALRAT3 AS MO_6, GUMAE_ENTNM1 AS MI_1, GUMAE_DEALRAT1 AS MI_4, GUMAE_ENTNM2 AS MI_2, GUMAE_DEALRAT2 AS MI_5, GUMAE_ENTNM3 AS MI_3, GUMAE_DEALRAT3 AS MI_6 FROM DCC_PACKET05 A WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET05 GROUP BY RESNO ) ) T05, (SELECT DP6.SUVDT , DP6.RESNO , MAX(DECODE(DP6.TRANSEQ, '0000', DP6.BS_ESETDT, 0)) AS BS_ESETDT_0 ,--일자_당기 MAX(DECODE(DP6.TRANSEQ, '0001', DP6.BS_ESETDT, 0)) AS BS_ESETDT_1 ,--일자_전기 MAX(DECODE(DP6.TRANSEQ, '0002', DP6.BS_ESETDT, 0)) AS BS_ESETDT_2 ,--일자_전전기 MAX(DECODE(DP6.TRANSEQ, '0000', DP6.BS0059, null)) AS BS0059_0 ,--자산총계_당기 MAX(DECODE(DP6.TRANSEQ, '0001', DP6.BS0059, null)) AS BS0059_1 ,--자산총계_전기 MAX(DECODE(DP6.TRANSEQ, '0002', DP6.BS0059, null)) AS BS0059_2 ,--자산총계_전전기 MAX(DECODE(DP6.TRANSEQ, '0000', DP6.BS0091, null)) AS BS0091_0 ,--부채총계_당기 MAX(DECODE(DP6.TRANSEQ, '0001', DP6.BS0091, null)) AS BS0091_1 ,--부채총계_전기 MAX(DECODE(DP6.TRANSEQ, '0002', DP6.BS0091, null)) AS BS0091_2 ,--부채총계_전전기 MAX(DECODE(DP6.TRANSEQ, '0000', DP6.BS0113, null)) AS BS0113_0 ,--자본총계_당기 MAX(DECODE(DP6.TRANSEQ, '0001', DP6.BS0113, null)) AS BS0113_1 ,--자본총계_전기 MAX(DECODE(DP6.TRANSEQ, '0002', DP6.BS0113, null)) AS BS0113_2--자본총계_전전기 FROM DCC_PACKET06 DP6 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET06 GROUP BY RESNO ) GROUP BY DP6.SUVDT, DP6.RESNO ) T06, (SELECT DP7.SUVDT , DP7.RESNO , MAX(DECODE(DP7.TRANSEQ, '0000', DP7.PL0001, null)) AS PL0001_0 ,--매출액_당기 MAX(DECODE(DP7.TRANSEQ, '0001', DP7.PL0001, null)) AS PL0001_1 ,--매출액_전기 MAX(DECODE(DP7.TRANSEQ, '0002', DP7.PL0001, null)) AS PL0001_2 ,--매출액_전전기 MAX(DECODE(DP7.TRANSEQ, '0000', DP7.PL0027, null)) AS PL0027_0 ,--영업이익_당기 MAX(DECODE(DP7.TRANSEQ, '0001', DP7.PL0027, null)) AS PL0027_1 ,--영업이익_전기 MAX(DECODE(DP7.TRANSEQ, '0002', DP7.PL0027, null)) AS PL0027_2 ,--영업이익_전전기 MAX(DECODE(DP7.TRANSEQ, '0000', DP7.PL0071, null)) AS PL0071_0 ,--당기순이익_당기 MAX(DECODE(DP7.TRANSEQ, '0001', DP7.PL0071, null)) AS PL0071_1 ,--당기순이익_전기 MAX(DECODE(DP7.TRANSEQ, '0002', DP7.PL0071, null)) AS PL0071_2--당기순이익_전전기 FROM DCC_PACKET07 DP7 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET07 GROUP BY RESNO ) GROUP BY DP7.SUVDT, DP7.RESNO )T07, (SELECT DP10.SUVDT , DP10.RESNO , DP10.TR0053 ,--부채비율당기 DP10.TR0052 ,--부채비율전기 DP10.TR0051 ,--부채비율전전기 DP10.TR0513 ,--차입금의존도당기 DP10.TR0512 ,--차입금의존도전기 DP10.TR0511 ,--차입금의존도전전기 DP10.PL0053 ,--매출액 영업이익율당기 DP10.PL0052 ,--매출액 영업이익율전기 DP10.PL0051 ,--매출액 영업이익율전전기 DP10.TR0103 ,--매출순이익율당기 DP10.TR0102 ,--매출순이익율전기 DP10.TR0101 ,--매출순이익율전전기 DP10.TR0083 ,--총자본순이익율당기 DP10.TR0082 ,--총자본순이익율전기 DP10.TR0081 ,--총자본순이익율전전기 DP10.TR0223 ,--매출액증가율당기 DP10.TR0222 ,--매출액증가율전기 DP10.TR0221 ,--매출액증가율전전기 DP10.TR0013 ,--유동비율당기 DP10.TR0012 ,--유동비율전기 DP10.TR0011--유동비율전전기 FROM DCC_PACKET10 DP10 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET10 GROUP BY RESNO ) ) T10, ( SELECT DP11.SUVDT , DP11.RESNO , DP11.DECISION--현금흐름등급(종합판정) FROM DCC_PACKET11 DP11 WHERE (SUVDT, RESNO) IN ( SELECT MAX(SUVDT),RESNO FROM DCC_PACKET11 GROUP BY RESNO ) ) T11, (SELECT WAT.RESNO , WAT.FDGBYIL , WAT.FWATCHD--워치등급 FROM WATCH_GRADE WAT WHERE FDGBYIL=(SELECT MAX(FDGBYIL) FROM WATCH_GRADE WHERE RESNO=WAT.RESNO) )WAT, (SELECT ETI.RESNO--협력업체사업자번호 , ETI.FSTRDRT--매출당사의존율(거래비중) FROM ETC_SUPERTAXINFO ETI ) ETI, (SELECT HPI.RESNO , DECODE(HPI.HPGBNCR,'1','폐업','2','휴업','정상') AS HPGBNCR_TY--휴폐업여부 , FCLOSDT--폐업일자 FROM ETC_HPINF HPI ) HPI, (SELECT MNE.RESNO , MNE.NOTICE--공고사항 , MNE.NOTICE_DT--공고게시일 FROM DCC_MONITOR_05 MNE WHERE MNE.NOTICE_DT=(SELECT MAX(NOTICE_DT) FROM DCC_MONITOR_05 WHERE RESNO=MNE.RESNO) ) MNE, (SELECT RMM.RESNO , RMM.FRISKRV--RMIS레벨 , RMM.EXPIRE_GB--공고사항 , RMM.EXPIRE_DATE--공고게시일 FROM DCC_RMIS_01 RMM WHERE RMM.FDROKYM=(SELECT MAX(FDROKYM) FROM DCC_RMIS_01 WHERE RESNO=RMM.RESNO) ) RMM, (SELECT RESNO, SUVDT, MAX(CASE WHEN PANMAE_ENTNM1 LIKE '%삼성중공업%' THEN PANMAE_DEALRAT1 ELSE 0 END + CASE WHEN PANMAE_ENTNM2 LIKE '%삼성중공업%' THEN PANMAE_DEALRAT2 ELSE 0 END + CASE WHEN PANMAE_ENTNM3 LIKE '%삼성중공업%' THEN PANMAE_DEALRAT3 ELSE 0 END ) PANMAE_DEALRAT FROM DCC_PACKET05 A WHERE (PANMAE_ENTNM1 LIKE '%삼성중공업%' or PANMAE_ENTNM2 LIKE '%삼성중공업%' OR PANMAE_ENTNM3 LIKE '%삼성중공업%') GROUP BY RESNO,SUVDT ) T12 WHERE T00.SUVDT=T02.SUVDT(+) AND T00.RESNO=T02.RESNO(+) AND T00.SUVDT=T03.SUVDT(+) AND T00.RESNO=T03.RESNO(+) AND T00.SUVDT=T04.SUVDT(+) AND T00.RESNO=T04.RESNO(+) AND T00.SUVDT=T05.SUVDT(+) AND T00.RESNO=T05.RESNO(+) AND T00.SUVDT=T06.SUVDT(+) AND T00.RESNO=T06.RESNO(+) AND T00.SUVDT=T07.SUVDT(+) AND T00.RESNO=T07.RESNO(+) AND T00.SUVDT=T10.SUVDT(+) AND T00.RESNO=T10.RESNO(+) AND T00.SUVDT=T11.SUVDT(+) AND T00.RESNO=T11.RESNO(+) AND T00.RESNO=WAT.RESNO(+) AND T00.RESNO=ETI.RESNO(+) AND T00.RESNO=HPI.RESNO(+) AND T00.RESNO=MNE.RESNO(+) AND T00.RESNO=RMM.RESNO(+) AND T00.SUVDT=T12.SUVDT(+) AND T00.RESNO=T12.RESNO(+) AND T00.RESNO = :reno AND 'I' = :credit_gb UNION ALL ------ 한국기업데이터 SELECT TO_CHAR(RESNO) RESNO ,--사업자번호 TO_CHAR(EENTNM) EENTNM ,--영문업체명 TO_CHAR(case when OPEDT is null then null else substr(OPEDT,1,4)||'.'||substr(OPEDT,5,2)||'.'||substr(OPEDT,7) end) OPEDT ,--설립일 TO_CHAR(REPR_NM) REPR_NM ,--대표명 TO_CHAR(TYSCALE) TYSCALE ,--대기업해당여부 TO_CHAR(RELCMP) RELCMP ,--소속그룹계열 TO_CHAR(ADR) ADR ,--본사주소 TO_CHAR(LISTYN) LISTYN ,--상장등록법인여부(기업형태) TO_CHAR(GOODSNM) GOODSNM ,--주요취급품목1 TO_CHAR(TELNO) TELNO ,--본사전화번호 TO_CHAR(FAXNO) FAXNO,--본사팩스번호 TO_CHAR(T07.TX_RT) FSTRDRT ,--매출당사의존율(거래비중) TO_CHAR(T06.MO_1) MO_1 , --매출순위1 TO_CHAR(T06.MO_4) MO_4 , --매출지분1 TO_CHAR(T06.MO_2) MO_2 , --매출순위2 TO_CHAR(T06.MO_5) MO_5 , --매출지분2 TO_CHAR(T06.MO_3) MO_3 , --매출순위3 TO_CHAR(T06.MO_6) MO_6 , --매출지분3 TO_CHAR(T06.MI_1) MI_1 , --매입순위1 TO_CHAR(T06.MI_4) MI_4 , --매입지분1 TO_CHAR(T06.MI_2) MI_2 , --매입순위2 TO_CHAR(T06.MI_5) MI_5 , --매입지분2 TO_CHAR(T06.MI_3) MI_3 , --매입순위3 TO_CHAR(T06.MI_6) MI_6 , --매입지분3 TO_CHAR(GIBUN_RL_1) GIBUN_RL_1, --지분관계성명1 TO_CHAR(LSH_STK_RATE1) LSH_STK_RATE1, --지분관계지분1 TO_CHAR(GIBUN_RL_2) GIBUN_RL_2, --지분관계성명2 TO_CHAR(LSH_STK_RATE2) LSH_STK_RATE2, --지분관계지분2 TO_CHAR(GIBUN_RL_3) GIBUN_RL_3, --지분관계성명3 TO_CHAR(LSH_STK_RATE3) LSH_STK_RATE3, --지분관계지분3 TO_CHAR(T08.ORDN_EM) HAPGYE ,--종업원수 TO_CHAR('') NOTICE ,--공고사항 (회생/파산정보) TO_CHAR('') NOTICE_DT ,--공고게시일 (회생/파산일자) TO_CHAR(HPGBNCR_TY) HPGBNCR_TY ,--휴폐업여부 TO_CHAR(FCLOSDT) FCLOSDT ,--폐업일자 TO_CHAR(LASTGRD) LASTGRD , --신용등급 TO_CHAR(DECISION) DECISION , -- 현금흐름등급 TO_CHAR('') FRISKRV ,--RMIS레벨 TO_CHAR('') EXPIRE_GB, --공고사항 TO_CHAR(FWATCHD) FWATCHD , --WATCH 등급 TO_CHAR(case when SUVDT is null then null else substr(SUVDT,1,4)||'.'||substr(SUVDT,5,2)||'.'||substr(SUVDT,7) end) SUVDT ,--신용조사일(평가기준일) TO_CHAR(case when T05.ACCT_EDDT is null then null else substr(T05.ACCT_EDDT,1,4)||'.'||substr(T05.ACCT_EDDT,5,2)||'.'||substr(T05.ACCT_EDDT,7) end) ESETDTT ,--결산일 TO_CHAR(case when T02.BS_ESETDT_0 is null then null else substr(T02.BS_ESETDT_0,1,4)||'.'||substr(T02.BS_ESETDT_0,5,2)||'.'||substr(T02.BS_ESETDT_0,7) end) bs_dt0, --재무년월 당년 TO_CHAR(case when T02.BS_ESETDT_1 is null then null else substr(T02.BS_ESETDT_1,1,4)||'.'||substr(T02.BS_ESETDT_1,5,2)||'.'||substr(T02.BS_ESETDT_1,7) end) bs_dt1, --재무년월 전년 TO_CHAR(case when T02.BS_ESETDT_2 is null then null else substr(T02.BS_ESETDT_2,1,4)||'.'||substr(T02.BS_ESETDT_2,5,2)||'.'||substr(T02.BS_ESETDT_2,7) end) bs_dt2, --재무년월 전전년 TO_CHAR(case when T03.BS_ESETDT_0 is null then null else substr(T03.BS_ESETDT_0,1,4)||'.'||substr(T03.BS_ESETDT_0,5,2)||'.'||substr(T03.BS_ESETDT_0,7) end) bs_dt3, --재무년월 당년 TO_CHAR(case when T03.BS_ESETDT_1 is null then null else substr(T03.BS_ESETDT_1,1,4)||'.'||substr(T03.BS_ESETDT_1,5,2)||'.'||substr(T03.BS_ESETDT_1,7) end) bs_dt4, --재무년월 전년 TO_CHAR(case when T03.BS_ESETDT_2 is null then null else substr(T03.BS_ESETDT_2,1,4)||'.'||substr(T03.BS_ESETDT_2,5,2)||'.'||substr(T03.BS_ESETDT_2,7) end) bs_dt5, --재무년월 전전년 trim(case when (BS0059_0/1000) < 1 AND (BS0059_0/1000) <> 0 then trim(to_char((BS0059_0/1000),'999,999,999,999,999.0')) when (BS0059_0/1000) = 0 then '0' else to_char((BS0059_0/1000),'999,999,999,999,999') end) bs59_0, --총자산 당년 (단위 :천원) trim(case when (BS0059_1/1000) < 1 AND (BS0059_1/1000) <> 0 then trim(to_char((BS0059_1/1000),'999,999,999,999,999.0')) when (BS0059_1/1000) = 0 then '0' else to_char((BS0059_1/1000),'999,999,999,999,999') end) bs59_1, --총자산 전년 trim(case when (BS0059_2/1000) < 1 AND (BS0059_2/1000) <> 0 then trim(to_char((BS0059_2/1000),'999,999,999,999,999.0')) when (BS0059_2/1000) = 0 then '0' else to_char((BS0059_2/1000),'999,999,999,999,999') end) bs59_2, --총자산 전전년 trim(case when (bs91_0/1000) < 1 AND (bs91_0/1000) <> 0 then trim(to_char((bs91_0/1000),'999,999,999,999,999.0')) when (bs91_0/1000) = 0 then '0' else to_char((bs91_0/1000),'999,999,999,999,999') end) bs91_0, --부채총계 당년 trim(case when (bs91_1/1000) < 1 AND (bs91_1/1000) <> 0 then trim(to_char((bs91_1/1000),'999,999,999,999,999.0')) when (bs91_1/1000) = 0 then '0' else to_char((bs91_1/1000),'999,999,999,999,999') end) bs91_1, --부채총계 전년 trim(case when (bs91_2/1000) < 1 AND (bs91_2/1000) <> 0 then trim(to_char((bs91_2/1000),'999,999,999,999,999.0')) when (bs91_2/1000) = 0 then '0' else to_char((bs91_2/1000),'999,999,999,999,999') end) bs91_2, --부채총계 전전년 trim(case when (bs113_0/1000) < 1 AND (bs113_0/1000) <> 0 then trim(to_char((bs113_0/1000),'999,999,999,999,999.0')) when (bs113_0/1000) = 0 then '0' else to_char((bs113_0/1000),'999,999,999,999,999') end) bs113_0, --자본총계 당년 trim(case when (bs113_1/1000) < 1 AND (bs113_1/1000) <> 0 then trim(to_char((bs113_1/1000),'999,999,999,999,999.0')) when (bs113_1/1000) = 0 then '0' else to_char((bs113_1/1000),'999,999,999,999,999') end) bs113_1, --자본총계 전년 trim(case when (bs113_2/1000) < 1 AND (bs113_2/1000) <> 0 then trim(to_char((bs113_2/1000),'999,999,999,999,999.0')) when (bs113_2/1000) = 0 then '0' else to_char((bs113_2/1000),'999,999,999,999,999') end) bs113_2, --자본총계 전전년 trim(case when (pl01_0/1000) < 1 AND (pl01_0/1000) <> 0 then trim(to_char((pl01_0/1000),'999,999,999,999,999.0')) when (pl01_0/1000) = 0 then '0' else to_char((pl01_0/1000),'999,999,999,999,999') end) pl01_0, --매출액_당기 trim(case when (pl01_1/1000) < 1 AND (pl01_1/1000) <> 0 then trim(to_char((pl01_1/1000),'999,999,999,999,999.0')) when (pl01_1/1000) = 0 then '0' else to_char((pl01_1/1000),'999,999,999,999,999') end) pl01_1, --매출액_전기 trim(case when (pl01_2/1000) < 1 AND (pl01_2/1000) <> 0 then trim(to_char((pl01_2/1000),'999,999,999,999,999.0')) when (pl01_2/1000) = 0 then '0' else to_char((pl01_2/1000),'999,999,999,999,999') end) pl01_2, --매출액_전전기 trim(case when (PL27_0/1000) < 1 AND (PL27_0/1000) <> 0 then trim(to_char((PL27_0/1000),'999,999,999,999,999.0')) when (PL27_0/1000) = 0 then '0' else to_char((PL27_0/1000),'999,999,999,999,999') end) pl27_0, --영업이익_당기 trim(case when (PL27_1/1000) < 1 AND (PL27_1/1000) <> 0 then trim(to_char((PL27_1/1000),'999,999,999,999,999.0')) when (PL27_1/1000) = 0 then '0' else to_char((PL27_1/1000),'999,999,999,999,999') end) pl27_1, --영업이익_전기 trim(case when (PL27_2/1000) < 1 AND (PL27_2/1000) <> 0 then trim(to_char((PL27_2/1000),'999,999,999,999,999.0')) when (PL27_2/1000) = 0 then '0' else to_char((PL27_2/1000),'999,999,999,999,999') end) pl27_2, --영업이익_전전기 trim(case when (PL0071_0/1000) < 1 AND (PL0071_0/1000) <> 0 then trim(to_char((PL0071_0/1000),'999,999,999,999,999.0')) when (PL0071_0/1000) = 0 then '0' else to_char((PL0071_0/1000),'999,999,999,999,999') end) pl71_0, --당기순이익_당기 trim(case when (PL0071_1/1000) < 1 AND (PL0071_1/1000) <> 0 then trim(to_char((PL0071_1/1000),'999,999,999,999,999.0')) when (PL0071_1/1000) = 0 then '0' else to_char((PL0071_1/1000),'999,999,999,999,999') end) pl71_1, --당기순이익_전기 trim(case when (PL0071_2/1000) < 1 AND (PL0071_2/1000) <> 0 then trim(to_char((PL0071_2/1000),'999,999,999,999,999.0')) when (PL0071_2/1000) = 0 then '0' else to_char((PL0071_2/1000),'999,999,999,999,999') end) pl71_2, --당기순이익_전전기 (TR0053) TR0053 ,--부채비율당기 (TR0052) TR0052 ,--부채비율전기 (TR0051) TR0051 ,--부채비율전전기 (TR0513) TR0513 ,--차입금의존도당기 (TR0512) TR0512 ,--차입금의존도전기 (TR0511) TR0511 ,--차입금의존도전전기 (TR0523) TR0523 ,--매출액 영업이익율당기 (TR0522) TR0522 ,--매출액 영업이익율전기 (TR0521) TR0521 ,--매출액 영업이익율전전기 (TR0103) TR0103 ,--매출순이익율당기 (TR0102) TR0102 ,--매출순이익율전기 (TR0101) TR0101 ,--매출순이익율전전기 (TR0223) TR0223 ,--매출액증가율당기 (TR0222) TR0222 ,--매출액증가율전기 (TR0221) TR0221 ,--매출액증가율전전기 (TR0013) TR0013 ,--유동비율당기 (TR0012) TR0012 ,--유동비율전기 (TR0011) TR0011 --유동비율전전기 FROM ( SELECT BZNO RESNO, --사업자번호 ENP_NM_ENG EENTNM, --업체영문명 ESTB_DT OPEDT, --설립일 REPER_NAME REPR_NM, --대표명 DECODE(ENP_SZE,'01','예','아니오') TYSCALE ,--대기업해당여부 GRP_NM RELCMP, --소속그룹계열 LOC_ADDRA ADR ,--본사주소 AA.CODE_NM_1 LISTYN ,--상장등록법인여부(기업형태) BB.CODE_NM_1 GOODSNM ,--주요취급품목1 TEL_NO TELNO ,--본사전화번호 FAX_NO FAXNO,--본사팩스번호 CC.CODE_NM_1 HPGBNCR_TY ,--휴폐업여부 ENP_SCD_CHG_DT FCLOSDT ,--폐업일자 CR_GRD LASTGRD , --신용등급 CASH_GRD DECISION , -- 현금흐름등급 STD_DT SUVDT ,--신용조사일(평가기준일) RPT_NO, RPT_CRE_DD, KEDCD FROM INTR S , (SELECT CODE_CD_2, CODE_NM_1 FROM K_LINK_CODE WHERE CODE_CD_1 = '0021') AA , (SELECT CODE_CD_2, CODE_NM_1 FROM K_LINK_CODE WHERE CODE_CD_1 = '0042') BB, (SELECT CODE_CD_2, CODE_NM_1 FROM K_LINK_CODE WHERE CODE_CD_1 = '0018') CC WHERE RPT_CRE_DD = (SELECT MAX(RPT_CRE_DD) FROM INTR WHERE KEDCD = S.KEDCD) AND S.ENP_FCD = AA.CODE_CD_2(+) AND S.BZC_CD = BB.CODE_CD_2(+) AND S.ENP_SCD = CC.CODE_CD_2(+) )T00, ( SELECT R.RPT_NO, R.KEDCD, R.RPT_CRE_DD, MAX(CASE WHEN DRANK = 1 THEN ACCT_DT ELSE '' END) BS_ESETDT_0,--재무년월 당년 MAX(CASE WHEN DRANK = 2 THEN ACCT_DT ELSE '' END) BS_ESETDT_1, MAX(CASE WHEN DRANK = 3 THEN ACCT_DT ELSE '' END) BS_ESETDT_2, MAX(CASE WHEN DRANK = 1 THEN SUMASSET ELSE null END) BS0059_0,--총자산 당년 MAX(CASE WHEN DRANK = 2 THEN SUMASSET ELSE null END) BS0059_1, MAX(CASE WHEN DRANK = 3 THEN SUMASSET ELSE null END) BS0059_2, MAX(CASE WHEN DRANK = 1 THEN LIABILITIES ELSE null END) BS91_0,--부채총계 당년 MAX(CASE WHEN DRANK = 2 THEN LIABILITIES ELSE null END) BS91_1, MAX(CASE WHEN DRANK = 3 THEN LIABILITIES ELSE null END) BS91_2, MAX(CASE WHEN DRANK = 1 THEN FUNDTOTAL ELSE null END) BS113_0,--자본총계 당년 MAX(CASE WHEN DRANK = 2 THEN FUNDTOTAL ELSE null END) BS113_1, MAX(CASE WHEN DRANK = 3 THEN FUNDTOTAL ELSE null END) BS113_2 FROM IMPORTANTFSDATA_N R GROUP BY R.RPT_NO, R.KEDCD, R.RPT_CRE_DD )T02, ( SELECT RPT_NO,RPT_CRE_DD,KEDCD, MAX(CASE WHEN RNUM = 1 THEN ACCT_DT ELSE '' END) BS_ESETDT_0,--재무년월 당년 MAX(CASE WHEN RNUM = 2 THEN ACCT_DT ELSE '' END) BS_ESETDT_1, MAX(CASE WHEN RNUM = 3 THEN ACCT_DT ELSE '' END) BS_ESETDT_2, MAX(CASE WHEN RNUM = 1 THEN FRT310700 ELSE null END) TR0053, --부채비율 MAX(CASE WHEN RNUM = 2 THEN FRT310700 ELSE null END) TR0052, MAX(CASE WHEN RNUM = 3 THEN FRT310700 ELSE null END) TR0051, MAX(CASE WHEN RNUM = 1 THEN FRT310300 ELSE null END) TR0513, -- 차입금의존도 MAX(CASE WHEN RNUM = 2 THEN FRT310300 ELSE null END) TR0512, MAX(CASE WHEN RNUM = 3 THEN FRT310300 ELSE null END) TR0511, MAX(CASE WHEN RNUM = 1 THEN FRT210200 ELSE null END) TR0523, --영업이익율 MAX(CASE WHEN RNUM = 2 THEN FRT210200 ELSE null END) TR0522, MAX(CASE WHEN RNUM = 3 THEN FRT210200 ELSE null END) TR0521, MAX(CASE WHEN RNUM = 1 THEN FRT110000 ELSE null END) TR0223, --매출액증가율 MAX(CASE WHEN RNUM = 2 THEN FRT110000 ELSE null END) TR0222, MAX(CASE WHEN RNUM = 3 THEN FRT110000 ELSE null END) TR0221, MAX(CASE WHEN RNUM = 1 THEN FRT311000 ELSE null END) TR0013, --유동비율 MAX(CASE WHEN RNUM = 2 THEN FRT311000 ELSE null END) TR0012, MAX(CASE WHEN RNUM = 3 THEN FRT311000 ELSE null END) TR0011, MAX(CASE WHEN RNUM = 1 THEN FRT210400 ELSE null END) TR0103, --매출순이익율당기 MAX(CASE WHEN RNUM = 2 THEN FRT210400 ELSE null END) TR0102, MAX(CASE WHEN RNUM = 3 THEN FRT210400 ELSE null END) TR0101 FROM ( SELECT H.RPT_NO RPT_NO, H.RPT_CRE_DD RPT_CRE_DD, H.KEDCD KEDCD, H.FRT310700 FRT310700, H.FRT310300 FRT310300, H.FRT210200 FRT210200, H.FRT110000 FRT110000, H.FRT311000 FRT311000, H.FRT210400 FRT210400, H.ACCT_DT ACCT_DT, ROW_NUMBER() OVER( PARTITION BY H.RPT_NO,H.RPT_CRE_DD,H.KEDCD ORDER BY H.ACCT_DT DESC) RNUM FROM FRTDATA_N_H H ) GROUP BY RPT_NO,RPT_CRE_DD,KEDCD )T03, ( SELECT RPT_NO,RPT_CRE_DD,KEDCD, MAX(CASE WHEN RNUM = 1 THEN STH_NM ELSE '' END) GIBUN_RL_1, MAX(CASE WHEN RNUM = 1 THEN TO_CHAR(EQRT) ELSE '' END) LSH_STK_RATE1, MAX(CASE WHEN RNUM = 2 THEN STH_NM ELSE '' END) GIBUN_RL_2, MAX(CASE WHEN RNUM = 2 THEN TO_CHAR(EQRT) ELSE '' END) LSH_STK_RATE2, MAX(CASE WHEN RNUM = 3 THEN STH_NM ELSE '' END) GIBUN_RL_3, MAX(CASE WHEN RNUM = 3 THEN TO_CHAR(EQRT) ELSE '' END) LSH_STK_RATE3 FROM (SELECT RPT_NO,RPT_CRE_DD,KEDCD, STH_NM, EQRT, ROW_NUMBER() OVER( PARTITION BY RPT_NO,RPT_CRE_DD,KEDCD ORDER BY EQRT desc ) RNUM FROM STH WHERE STH_STT_SEQ < 4 ) GROUP BY RPT_NO,RPT_CRE_DD,KEDCD ) T04, ( SELECT RPT_NO,RPT_CRE_DD,KEDCD, MAX(ACCT_EDDT) ACCT_EDDT FROM FSMST_N S GROUP BY RPT_NO,RPT_CRE_DD,KEDCD ) T05, ( SELECT RPT_NO,RPT_CRE_DD,KEDCD, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '1' THEN TXPL_NM ELSE '' END) MO_1, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '1' THEN TX_RT ELSE 0 END) MO_4, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '2' THEN TXPL_NM ELSE '' END) MO_2, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '2' THEN TX_RT ELSE 0 END) MO_5, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '3' THEN TXPL_NM ELSE '' END) MO_3, MAX(CASE WHEN TXPL_CCD = '2' AND RNUM = '3' THEN TX_RT ELSE 0 END) MO_6, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '1' THEN TXPL_NM ELSE '' END) MI_1, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '1' THEN TX_RT ELSE 0 END) MI_4, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '2' THEN TXPL_NM ELSE '' END) MI_2, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '2' THEN TX_RT ELSE 0 END) MI_5, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '3' THEN TXPL_NM ELSE '' END) MI_3, MAX(CASE WHEN TXPL_CCD = '1' AND RNUM = '3' THEN TX_RT ELSE 0 END) MI_6 FROM ( SELECT RPT_NO,RPT_CRE_DD,KEDCD, TXPL_CCD , TXPL_NM , TX_RT, RNUM FROM ( SELECT RPT_NO,RPT_CRE_DD,KEDCD ,TXPL_CCD,TX_AM,TXPL_NM,TX_RT , ROW_NUMBER() OVER( PARTITION BY RPT_NO,RPT_CRE_DD,KEDCD, TXPL_CCD ORDER BY TX_AM DESC ) RNUM FROM MTXPL WHERE RPT_NO IN (SELECT RPT_NO FROM INTR S WHERE (BZNO , RPT_CRE_DD) IN (SELECT BZNO ,MAX(RPT_CRE_DD) FROM INTR WHERE S.BZNO = BZNO GROUP BY BZNO)) ) WHERE RNUM < 4 ) GROUP BY RPT_NO,RPT_CRE_DD,KEDCD ) T06, ( SELECT RPT_NO,RPT_CRE_DD,KEDCD,MAX(TX_RT) TX_RT FROM MTXPL WHERE TXPL_CCD = '1' AND TXPL_NM LIKE '%삼성중공업%' GROUP BY RPT_NO,RPT_CRE_DD,KEDCD ) T07, (SELECT RPT_NO,RPT_CRE_DD,KEDCD, ORDN_EM FROM EM WHERE (RPT_NO,RPT_CRE_DD,KEDCD,STD_DT) IN (SELECT RPT_NO,RPT_CRE_DD,KEDCD,MAX(STD_DT) FROM EM GROUP BY RPT_NO,RPT_CRE_DD,KEDCD) AND TTL_CD = '99' ) T08, (SELECT BZNO, DECODE(EWRT, '00', '정상', '10', '관심', '20', '관찰1', '30', '관찰2', '40', '관찰3', '50', '휴업', '60', '부도', '70', '폐업') FWATCHD --WATCH 등급 FROM EW0010 A, ( SELECT K.* FROM ( SELECT A.*, RANK() OVER ( PARTITION BY A.EWCD ORDER BY A.BASE_DT DESC, A.UPD_DT DESC) RNK FROM EW0030 A ) K WHERE RNK = 1 ) B WHERE A.EWCD = B.EWCD AND A.DEL_YN <> 'Y' ) T09, ( SELECT RPT_NO, KEDCD, RPT_CRE_DD, MAX(CASE WHEN RNUM = 1 THEN FS290000 ELSE null END) PL0071_0, --당기순이익 MAX(CASE WHEN RNUM = 2 THEN FS290000 ELSE null END) PL0071_1, MAX(CASE WHEN RNUM = 3 THEN FS290000 ELSE null END) PL0071_2, MAX(CASE WHEN RNUM = 1 THEN FS230000 ELSE null END) PL27_0,--영업이익_당기 MAX(CASE WHEN RNUM = 2 THEN FS230000 ELSE null END) PL27_1, MAX(CASE WHEN RNUM = 3 THEN FS230000 ELSE null END) PL27_2, MAX(CASE WHEN RNUM = 1 THEN FS210000 ELSE null END) PL01_0,--매출액_당기 MAX(CASE WHEN RNUM = 2 THEN FS210000 ELSE null END) PL01_1, MAX(CASE WHEN RNUM = 3 THEN FS210000 ELSE null END) PL01_2 FROM ( SELECT RPT_NO, KEDCD, RPT_CRE_DD, ACCT_DT, FS230000, FS651400 ,FS210000,FS290000, ROW_NUMBER() OVER( PARTITION BY RPT_NO,RPT_CRE_DD,KEDCD ORDER BY ACCT_DT DESC) RNUM FROM FSDATA_N_H ) GROUP BY RPT_NO, KEDCD, RPT_CRE_DD ) T10 WHERE 1=1 AND T00.RPT_NO = T02.RPT_NO(+) AND T00.RPT_CRE_DD = T02.RPT_CRE_DD(+) AND T00.KEDCD = T02. KEDCD(+) AND T00.RPT_NO = T03.RPT_NO(+) AND T00.RPT_CRE_DD = T03.RPT_CRE_DD(+) AND T00.KEDCD = T03. KEDCD(+) AND T00.RPT_NO = T04.RPT_NO(+) AND T00.RPT_CRE_DD = T04.RPT_CRE_DD(+) AND T00.KEDCD = T04. KEDCD(+) AND T00.RPT_NO = T05.RPT_NO(+) AND T00.RPT_CRE_DD = T05.RPT_CRE_DD(+) AND T00.KEDCD = T05. KEDCD(+) AND T00.RPT_NO = T06.RPT_NO(+) AND T00.RPT_CRE_DD = T06.RPT_CRE_DD(+) AND T00.KEDCD = T06. KEDCD(+) AND T00.RPT_NO = T07.RPT_NO(+) AND T00.RPT_CRE_DD = T07.RPT_CRE_DD(+) AND T00.KEDCD = T07. KEDCD(+) AND T00.RPT_NO = T08.RPT_NO(+) AND T00.RPT_CRE_DD = T08.RPT_CRE_DD(+) AND T00.KEDCD = T08.KEDCD(+) AND T00.RESNO = T09.BZNO(+) AND T00.RPT_NO = T10.RPT_NO(+) AND T00.RPT_CRE_DD = T10.RPT_CRE_DD(+) AND T00.KEDCD = T10.KEDCD(+) AND T00.RESNO = :reno AND 'K' = :credit_gb UNION ALL ---나이스디앤비 SELECT TO_CHAR(A.BIZ_NO) RESNO ,--사업자번호 TO_CHAR(A.CMP_NM_ENG) EENTNM ,--영문업체명 TO_CHAR(case when A.ESTAB_YMD is null then null else substr(A.ESTAB_YMD,1,4)||'.'||substr(A.ESTAB_YMD,5,2)||'.'||substr(A.ESTAB_YMD,7) end) OPEDT ,--설립일 TO_CHAR(A.MGR_NM) REPR_NM ,--대표명 TO_CHAR(DECODE(A.BZ_SCR,'대기업','예','아니오')) TYSCALE ,--대기업해당여부 TO_CHAR(A.GRP_NM) RELCMP ,--소속그룹계열 TO_CHAR(A.UADDR) ADR ,--본사주소 TO_CHAR(A.TYP) LISTYN ,--상장등록법인여부(기업형태) TO_CHAR(A.PRODUCT) GOODSNM ,--주요취급품목1 TO_CHAR(A.UTEL) TELNO ,--본사전화번호 TO_CHAR(A.UFAX) FAXNO,--본사팩스번호 TO_CHAR(K.RATE_Y) FSTRDRT ,--매출당사의존율(거래비중) TO_CHAR(MO_1) MO_1 , --매출순위1 TO_CHAR(MO_4) MO_4 , --매출지분1 TO_CHAR(MO_2) MO_2 , --매출순위2 TO_CHAR(MO_5) MO_5 , --매출지분2 TO_CHAR(MO_3) MO_3 , --매출순위3 TO_CHAR(MO_6) MO_6 , --매출지분3 TO_CHAR(MI_1) MI_1 , --매입순위1 TO_CHAR(MI_4) MI_4 , --매입지분1 TO_CHAR(MI_2) MI_2 , --매입순위2 TO_CHAR(MI_5) MI_5 , --매입지분2 TO_CHAR(MI_3) MI_3 , --매입순위3 TO_CHAR(MI_6) MI_6 , --매입지분3 TO_CHAR(MGR_NM1) GIBUN_RL_1, --지분관계성명1 TO_CHAR(LSH_STK_RATE1) LSH_STK_RATE1, --지분관계지분1 TO_CHAR(MGR_NM2) GIBUN_RL_2, --지분관계성명2 TO_CHAR(LSH_STK_RATE2) LSH_STK_RATE2, --지분관계지분2 TO_CHAR(MGR_NM3) GIBUN_RL_3, --지분관계성명3 TO_CHAR(LSH_STK_RATE3) LSH_STK_RATE3, --지분관계지분3 TO_CHAR(A.EMP_NO) HAPGYE ,--종업원수 TO_CHAR(C.ANN) NOTICE ,--공고사항 (회생/파산정보) TO_CHAR(C.ANN_DATE) NOTICE_DT ,--공고게시일 (회생/파산일자) TO_CHAR(J.STT_NM) HPGBNCR_TY ,--휴폐업여부 TO_CHAR(J.MODIDATE) FCLOSDT ,--폐업일자 TO_CHAR(A.LASTGRD2) LASTGRD , --신용등급 TO_CHAR(A.CASH_GRADE) DECISION , -- 현금흐름등급 TO_CHAR(G.RM_GRD) FRISKRV ,--RMIS레벨 TO_CHAR(G.MODIDATE) EXPIRE_GB, --공고사항 TO_CHAR(B.WATCH_GRD) FWATCHD , --WATCH 등급 TO_CHAR(case when A.E_DATE is null then null else substr(A.E_DATE,1,4)||'.'||substr(A.E_DATE,5,2)||'.'||substr(A.E_DATE,7) end) SUVDT ,--신용조사일(평가기준일) TO_CHAR(case when A.acc_ymd is null then null else substr(A.acc_ymd,1,4)||'.'||substr(A.acc_ymd,5,2)||'.'||substr(A.acc_ymd,7) end) ESETDTT ,--결산일 TO_CHAR(case when D.BASE_YMD1 is null then null else substr(D.BASE_YMD1,1,4)||'.'||substr(D.BASE_YMD1,5,2)||'.'||substr(D.BASE_YMD1,7) end) bs_dt0, --재무년월 당년 TO_CHAR(case when D.BASE_YMD2 is null then null else substr(D.BASE_YMD2,1,4)||'.'||substr(D.BASE_YMD2,5,2)||'.'||substr(D.BASE_YMD2,7) end) bs_dt1, --재무년월 전년 TO_CHAR(case when D.BASE_YMD3 is null then null else substr(D.BASE_YMD3,1,4)||'.'||substr(D.BASE_YMD3,5,2)||'.'||substr(D.BASE_YMD3,7) end) bs_dt2, --재무년월 전전년 TO_CHAR(case when F.BASE_YMD1 is null then null else substr(F.BASE_YMD1,1,4)||'.'||substr(F.BASE_YMD1,5,2)||'.'||substr(F.BASE_YMD1,7) end) bs_dt3, --재무년월 당년 TO_CHAR(case when F.BASE_YMD2 is null then null else substr(F.BASE_YMD2,1,4)||'.'||substr(F.BASE_YMD2,5,2)||'.'||substr(F.BASE_YMD2,7) end) bs_dt4, --재무년월 전년 TO_CHAR(case when F.BASE_YMD3 is null then null else substr(F.BASE_YMD3,1,4)||'.'||substr(F.BASE_YMD3,5,2)||'.'||substr(F.BASE_YMD3,7) end) bs_dt5, --재무년월 전전년 trim(case when (D.DAE0620_1/1000000) < 1 AND (D.DAE0620_1/1000000) <> 0 then trim(to_char((D.DAE0620_1/1000000),'999,999,999,999,999.0')) when (D.DAE0620_1/1000000) = 0 then '0' else to_char((D.DAE0620_1/1000000),'999,999,999,999,999') end) bs59_0, --총자산 당년 trim(case when (D.DAE0620_2/1000000) < 1 AND (D.DAE0620_2/1000000) <> 0 then trim(to_char((D.DAE0620_2/1000000),'999,999,999,999,999.0')) when (D.DAE0620_2/1000000) = 0 then '0' else to_char((D.DAE0620_2/1000000),'999,999,999,999,999') end) bs59_1, --총자산 전년 trim(case when (D.DAE0620_3/1000000) < 1 AND (D.DAE0620_3/1000000) <> 0 then trim(to_char((D.DAE0620_3/1000000),'999,999,999,999,999.0')) when (D.DAE0620_3/1000000) = 0 then '0' else to_char((D.DAE0620_3/1000000),'999,999,999,999,999') end) bs59_2, --총자산 전전년 trim(case when (D.DAE0611_1/1000000) < 1 AND (D.DAE0611_1/1000000) <> 0 then trim(to_char((D.DAE0611_1/1000000),'999,999,999,999,999.0')) when (D.DAE0611_1/1000000) = 0 then '0' else to_char((D.DAE0611_1/1000000),'999,999,999,999,999') end) bs91_0, --부채총계 당년 trim(case when (D.DAE0611_2/1000000) < 1 AND (D.DAE0611_2/1000000) <> 0 then trim(to_char((D.DAE0611_2/1000000),'999,999,999,999,999.0')) when (D.DAE0611_2/1000000) = 0 then '0' else to_char((D.DAE0611_2/1000000),'999,999,999,999,999') end) bs91_1, --부채총계 전년 trim(case when (D.DAE0611_3/1000000) < 1 AND (D.DAE0611_3/1000000) <> 0 then trim(to_char((D.DAE0611_3/1000000),'999,999,999,999,999.0')) when (D.DAE0611_3/1000000) = 0 then '0' else to_char((D.DAE0611_3/1000000),'999,999,999,999,999') end) bs91_2, --부채총계 전전년 trim(case when (D.DAE0613_1/1000000) < 1 AND (D.DAE0613_1/1000000) <> 0 then trim(to_char((D.DAE0613_1/1000000),'999,999,999,999,999.0')) when (D.DAE0613_1/1000000) = 0 then '0' else to_char((D.DAE0613_1/1000000),'999,999,999,999,999') end) bs113_0, --자본총계 당년 trim(case when (D.DAE0613_2/1000000) < 1 AND (D.DAE0613_2/1000000) <> 0 then trim(to_char((D.DAE0613_2/1000000),'999,999,999,999,999.0')) when (D.DAE0613_2/1000000) = 0 then '0' else to_char((DAE0613_2/1000000),'999,999,999,999,999') end) bs113_1, --자본총계 전년 trim(case when (D.DAE0613_3/1000000) < 1 AND (D.DAE0613_3/1000000) <> 0 then trim(to_char((D.DAE0613_3/1000000),'999,999,999,999,999.0')) when (D.DAE0613_3/1000000) = 0 then '0' else to_char((D.DAE0613_3/1000000),'999,999,999,999,999') end) bs113_2, --자본총계 전전년 trim(case when (E.SON0701_1/1000000) < 1 AND (E.SON0701_1/1000000) <> 0 then trim(to_char((E.SON0701_1/1000000),'999,999,999,999,999.0')) when (E.SON0701_1/1000000) = 0 then '0' else to_char((E.SON0701_1/1000000),'999,999,999,999,999') end) pl01_0, --매출액_당기 trim(case when (E.SON0701_2/1000000) < 1 AND (E.SON0701_2/1000000) <> 0 then trim(to_char((E.SON0701_2/1000000),'999,999,999,999,999.0')) when (E.SON0701_2/1000000) = 0 then '0' else to_char((E.SON0701_2/1000000),'999,999,999,999,999') end) pl01_1, --매출액_전기 trim(case when (E.SON0701_3/1000000) < 1 AND (E.SON0701_3/1000000) <> 0 then trim(to_char((E.SON0701_3/1000000),'999,999,999,999,999.0')) when (E.SON0701_3/1000000) = 0 then '0' else to_char((E.SON0701_3/1000000),'999,999,999,999,999') end) pl01_2, --매출액_전전기 trim(case when (E.SON0709_1/1000000) < 1 AND (E.SON0709_1/1000000) <> 0 then trim(to_char((E.SON0709_1/1000000),'999,999,999,999,999.0')) when (E.SON0709_1/1000000) = 0 then '0' else to_char((E.SON0709_1/1000000),'999,999,999,999,999') end) pl27_0, --영업이익_당기 trim(case when (E.SON0709_2/1000000) < 1 AND (E.SON0709_2/1000000) <> 0 then trim(to_char((E.SON0709_2/1000000),'999,999,999,999,999.0')) when (E.SON0709_2/1000000) = 0 then '0' else to_char((E.SON0709_2/1000000),'999,999,999,999,999') end) pl27_1, --영업이익_전기 trim(case when (E.SON0709_3/1000000) < 1 AND (E.SON0709_3/1000000) <> 0 then trim(to_char((E.SON0709_3/1000000),'999,999,999,999,999.0')) when (E.SON0709_3/1000000) = 0 then '0' else to_char((E.SON0709_3/1000000),'999,999,999,999,999') end) pl27_2, --영업이익_전전기 trim(case when (E.SON0702_1/1000000) < 1 AND (E.SON0702_1/1000000) <> 0 then trim(to_char((E.SON0702_1/1000000),'999,999,999,999,999.0')) when (E.SON0702_1/1000000) = 0 then '0' else to_char((E.SON0702_1/1000000),'999,999,999,999,999') end) pl71_0, --당기순이익_당기 trim(case when (E.SON0702_2/1000000) < 1 AND (E.SON0702_2/1000000) <> 0 then trim(to_char((E.SON0702_2/1000000),'999,999,999,999,999.0')) when (E.SON0702_2/1000000) = 0 then '0' else to_char((E.SON0702_2/1000000),'999,999,999,999,999') end) pl71_1, --당기순이익_전기 trim(case when (E.SON0702_3/1000000) < 1 AND (E.SON0702_3/1000000) <> 0 then trim(to_char((E.SON0702_3/1000000),'999,999,999,999,999.0')) when (E.SON0702_3/1000000) = 0 then '0' else to_char((E.SON0702_3/1000000),'999,999,999,999,999') end) pl71_2, --당기순이익_전전기 (F.JAEMU1012_1) TR0053 ,--부채비율당기 (F.JAEMU1012_2) TR0052 ,--부채비율전기 (F.JAEMU1012_3) TR0051 ,--부채비율전전기 (F.JAEMU1014_1) TR0513 ,--차입금의존도당기 (F.JAEMU1014_2) TR0512 ,--차입금의존도전기 (F.JAEMU1014_3) TR0511 ,--차입금의존도전전기 (F.JAEMU1009_1) TR0523 ,--매출액 영업이익율당기 (F.JAEMU1009_2) TR0522 ,--매출액 영업이익율전기 (F.JAEMU1009_3) TR0521 ,--매출액 영업이익율전전기 (F.JAEMU1004_1) TR0103 ,--매출순이익율당기 (F.JAEMU1004_2) TR0102 ,--매출순이익율전기 (F.JAEMU1004_3) TR0101 ,--매출순이익율전전기 (F.JAEMU1024_1) TR0223 ,--매출액증가율당기 (F.JAEMU1024_2) TR0222 ,--매출액증가율전기 (F.JAEMU1024_3) TR0221 ,--매출액증가율전전기 (F.JAEMU1010_1) TR0013 ,--유동비율당기 (F.JAEMU1010_2) TR0012 ,--유동비율전기 (F.JAEMU1010_3) TR0011 --유동비율전전기 FROM ( SELECT BIZ_NO, E_DATE, --평가 기준일 CMP_NM_ENG , ESTAB_YMD, MGR_NM , BZ_SCR, GRP_NM , UADDR , TYP, PRODUCT ,UTEL ,UFAX, LASTGRD2, --신용등급 CASH_GRADE, --현금흐름 EMP_NO, --종업원 수 acc_ymd -- 결산일자 FROM DBK_RPT01 A WHERE (BIZ_NO,E_DATE) IN ( SELECT BIZ_NO , MAX(E_DATE) FROM DBK_RPT01 WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) ) A , ( SELECT BIZ_NO, WATCH_GRD --WATCH 등급 FROM DBK_GRD_WATCH A WHERE (BIZ_NO,BASE_YMD) IN ( SELECT BIZ_NO , MAX(BASE_YMD) FROM DBK_GRD_WATCH WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) ) B , ( SELECT BIZ_NO, ANN, --기업회생/파산정보 ANN_DATE FROM DBK_MON03 A WHERE (BIZ_NO,SEQ) IN ( SELECT BIZ_NO , MAX(SEQ) FROM DBK_MON03 WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) ) C , ( SELECT BIZ_NO,E_DATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN DAE0620*1000 ELSE 0 END) DAE0620_1, --총자산 MAX(CASE WHEN RNUM = 2 THEN DAE0620*1000 ELSE 0 END) DAE0620_2, MAX(CASE WHEN RNUM = 3 THEN DAE0620*1000 ELSE 0 END) DAE0620_3, MAX(CASE WHEN RNUM = 1 THEN DAE0611*1000 ELSE 0 END) DAE0611_1, --부채총계 MAX(CASE WHEN RNUM = 2 THEN DAE0611*1000 ELSE 0 END) DAE0611_2, MAX(CASE WHEN RNUM = 3 THEN DAE0611*1000 ELSE 0 END) DAE0611_3, MAX(CASE WHEN RNUM = 1 THEN DAE0613*1000 ELSE 0 END) DAE0613_1, --자본총계 MAX(CASE WHEN RNUM = 2 THEN DAE0613*1000 ELSE 0 END) DAE0613_2, MAX(CASE WHEN RNUM = 3 THEN DAE0613*1000 ELSE 0 END) DAE0613_3 FROM ( SELECT BIZ_NO,E_DATE,BASE_YMD, DAE0620, DAE0611, DAE0613, ROW_NUMBER() OVER( PARTITION BY BIZ_NO,E_DATE ORDER BY BASE_YMD DESC) RNUM FROM DBK_FIN01 ) GROUP BY BIZ_NO,E_DATE ) D , ( SELECT BIZ_NO,E_DATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN SON0701*1000 ELSE null END) SON0701_1, --매출액 MAX(CASE WHEN RNUM = 2 THEN SON0701*1000 ELSE null END) SON0701_2, MAX(CASE WHEN RNUM = 3 THEN SON0701*1000 ELSE null END) SON0701_3, MAX(CASE WHEN RNUM = 1 THEN SON0709*1000 ELSE null END) SON0709_1, --영업이익 MAX(CASE WHEN RNUM = 2 THEN SON0709*1000 ELSE null END) SON0709_2, MAX(CASE WHEN RNUM = 3 THEN SON0709*1000 ELSE null END) SON0709_3, MAX(CASE WHEN RNUM = 1 THEN SON0702*1000 ELSE null END) SON0702_1, --당기순이익 MAX(CASE WHEN RNUM = 2 THEN SON0702*1000 ELSE null END) SON0702_2, MAX(CASE WHEN RNUM = 3 THEN SON0702*1000 ELSE null END) SON0702_3 FROM ( SELECT BIZ_NO,E_DATE,BASE_YMD, SON0701, SON0709, SON0702, ROW_NUMBER() OVER( PARTITION BY BIZ_NO,E_DATE ORDER BY BASE_YMD DESC) RNUM FROM DBK_FIN02 ) GROUP BY BIZ_NO,E_DATE ) E , ( SELECT BIZ_NO,E_DATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1012 ELSE null END) JAEMU1012_1, --부채비율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1012 ELSE null END) JAEMU1012_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1012 ELSE null END) JAEMU1012_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1014 ELSE null END) JAEMU1014_1, --차입금의존도 MAX(CASE WHEN RNUM = 2 THEN JAEMU1014 ELSE null END) JAEMU1014_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1014 ELSE null END) JAEMU1014_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1009 ELSE null END) JAEMU1009_1, --영업이익률 MAX(CASE WHEN RNUM = 2 THEN JAEMU1009 ELSE null END) JAEMU1009_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1009 ELSE null END) JAEMU1009_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1004 ELSE null END) JAEMU1004_1, --순이익률 MAX(CASE WHEN RNUM = 2 THEN JAEMU1004 ELSE null END) JAEMU1004_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1004 ELSE null END) JAEMU1004_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1024 ELSE null END) JAEMU1024_1, --매출액 증가율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1024 ELSE null END) JAEMU1024_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1024 ELSE null END) JAEMU1024_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1010 ELSE null END) JAEMU1010_1, -- 유동비율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1010 ELSE null END) JAEMU1010_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1010 ELSE null END) JAEMU1010_3 FROM ( SELECT BIZ_NO,E_DATE,BASE_YMD, JAEMU1012, JAEMU1014, JAEMU1009, JAEMU1004, JAEMU1024, JAEMU1010, ROW_NUMBER() OVER( PARTITION BY BIZ_NO,E_DATE ORDER BY BASE_YMD DESC) RNUM FROM DBK_FIN05 ) GROUP BY BIZ_NO,E_DATE ) F, ( SELECT BIZ_NO, RM_GRD, --기업회생/파산정보 MODIDATE FROM DBK_GRD_RM A WHERE (BIZ_NO,RM_YMD) IN ( SELECT BIZ_NO , MAX(RM_YMD) FROM DBK_GRD_RM WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) ) G, (SELECT BIZ_NO, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 1 THEN CUST_NM END) MO_1, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 1 THEN RATE_Y END) MO_4, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 2 THEN CUST_NM END) MO_2, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 2 THEN RATE_Y END) MO_5, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 3 THEN CUST_NM END) MO_3, MAX(CASE WHEN CUST_TYP = '매출처' AND RNUM = 3 THEN RATE_Y END) MO_6, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 1 THEN CUST_NM END) MI_1, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 1 THEN RATE_Y END) MI_4, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 2 THEN CUST_NM END) MI_2, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 2 THEN RATE_Y END) MI_5, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 3 THEN CUST_NM END) MI_3, MAX(CASE WHEN CUST_TYP = '매입처' AND RNUM = 3 THEN RATE_Y END) MI_6 FROM ( SELECT BIZ_NO,CUST_TYP,CUST_NM,RATE_Y, ROW_NUMBER() OVER( PARTITION BY BIZ_NO,E_DATE,CUST_TYP ORDER BY BIZ_NO,E_DATE,SEQ ) RNUM FROM DBK_RPT06 A WHERE (BIZ_NO ,E_DATE) IN (SELECT BIZ_NO ,MAX(E_DATE) FROM DBK_RPT06 WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) ) WHERE RNUM < 4 GROUP BY BIZ_NO ) H, (SELECT BIZ_NO, MGR_NM1,LSH_STK_RATE1 , MGR_NM2,LSH_STK_RATE2 , MGR_NM3,LSH_STK_RATE3 FROM DBK_RPT04 WHERE (BIZ_NO, E_DATE) IN (SELECT BIZ_NO, MAX(E_DATE) FROM DBK_RPT04 GROUP BY BIZ_NO ) ) I, (SELECT BIZ_NO, STT_NM ,MODIDATE FROM DBK_MON04 WHERE (BIZ_NO,STT_YMD) IN (SELECT BIZ_NO , MAX(STT_YMD) FROM DBK_MON04 GROUP BY BIZ_NO) ) J, ( SELECT BIZ_NO,MAX(RATE_Y) RATE_Y FROM DBK_RPT06 A WHERE (BIZ_NO ,E_DATE) IN (SELECT BIZ_NO ,MAX(E_DATE) FROM DBK_RPT06 WHERE A.BIZ_NO = BIZ_NO GROUP BY BIZ_NO ) AND CUST_TYP = '매출처' AND CUST_NM like '%삼성중공업%' GROUP BY BIZ_NO ) K WHERE A.BIZ_NO = B.BIZ_NO(+) AND A.BIZ_NO = C.BIZ_NO(+) AND A.BIZ_NO = D.BIZ_NO(+) AND A.BIZ_NO = E.BIZ_NO(+) AND A.BIZ_NO = F.BIZ_NO(+) AND A.E_DATE = D.E_DATE(+) AND A.E_DATE = E.E_DATE(+) AND A.E_DATE = F.E_DATE(+) AND A.BIZ_NO = G.BIZ_NO(+) AND A.BIZ_NO = H.BIZ_NO(+) AND A.BIZ_NO = I.BIZ_NO(+) AND A.BIZ_NO = J.BIZ_NO(+) AND A.BIZ_NO = K.BIZ_NO(+) AND A.BIZ_NO = :reno AND 'N' = :credit_gb UNION ALL ---NICE신용평가사 SELECT TO_CHAR(L.BIZNO) RESNO ,--사업자번호 TO_CHAR(L.CMP_NM_ENG) EENTNM ,--영문업체명 TO_CHAR(case when L.ESTAB_YMD is null then null else substr(L.ESTAB_YMD,1,4)||'.'||substr(L.ESTAB_YMD,5,2)||'.'||substr(L.ESTAB_YMD,7) end) OPEDT ,--설립일 TO_CHAR(L.MGR_NM) REPR_NM ,--대표명 TO_CHAR(DECODE(L.BZ_SCR,'대기업','예','아니오')) TYSCALE ,--대기업해당여부 TO_CHAR(L.GRP_NM) RELCMP ,--소속그룹계열 TO_CHAR(L.UADDR) ADR ,--본사주소 TO_CHAR(L.TYP) LISTYN ,--상장등록법인여부(기업형태) TO_CHAR(L.PRODUCT) GOODSNM ,--주요취급품목1 TO_CHAR(L.UTEL) TELNO ,--본사전화번호 TO_CHAR(L.UFAX) FAXNO,--본사팩스번호 TO_CHAR(K.RATE_Y) FSTRDRT ,--매출당사의존율(거래비중) TO_CHAR(MO_1) MO_1 , --매출순위1 TO_CHAR(MO_4) MO_4 , --매출지분1 TO_CHAR(MO_2) MO_2 , --매출순위2 TO_CHAR(MO_5) MO_5 , --매출지분2 TO_CHAR(MO_3) MO_3 , --매출순위3 TO_CHAR(MO_6) MO_6 , --매출지분3 TO_CHAR(MI_1) MI_1 , --매입순위1 TO_CHAR(MI_4) MI_4 , --매입지분1 TO_CHAR(MI_2) MI_2 , --매입순위2 TO_CHAR(MI_5) MI_5 , --매입지분2 TO_CHAR(MI_3) MI_3 , --매입순위3 TO_CHAR(MI_6) MI_6 , --매입지분3 TO_CHAR(MGR_NM1) GIBUN_RL_1, --지분관계성명1 TO_CHAR(LSH_STK_RATE1) LSH_STK_RATE1, --지분관계지분1 TO_CHAR(MGR_NM2) GIBUN_RL_2, --지분관계성명2 TO_CHAR(LSH_STK_RATE2) LSH_STK_RATE2, --지분관계지분2 TO_CHAR(MGR_NM3) GIBUN_RL_3, --지분관계성명3 TO_CHAR(LSH_STK_RATE3) LSH_STK_RATE3, --지분관계지분3 TO_CHAR(L.EMP_NO) HAPGYE ,--종업원수 TO_CHAR(C.ANN) NOTICE ,--공고사항 (회생/파산정보) TO_CHAR(C.ANN_DATE) NOTICE_DT ,--공고게시일 (회생/파산일자) TO_CHAR(J.STT_NM) HPGBNCR_TY ,--휴폐업여부 TO_CHAR(J.MODIDATE) FCLOSDT ,--폐업일자 TO_CHAR(A.LASTGRD2) LASTGRD , --신용등급 TO_CHAR(A.CASH_GRADE) DECISION , -- 현금흐름등급 TO_CHAR(G.RM_GRD) FRISKRV ,--RMIS레벨 TO_CHAR(G.MODIDATE) EXPIRE_GB, --공고사항 TO_CHAR(B.WATCH_GRD) FWATCHD , --WATCH 등급 TO_CHAR(case when A.E_DATE is null then null else substr(A.E_DATE,1,4)||'.'||substr(A.E_DATE,5,2)||'.'||substr(A.E_DATE,7) end) SUVDT ,--신용조사일(평가기준일) TO_CHAR(case when A.acc_ymd is null then null else substr(A.acc_ymd,1,4)||'.'||substr(A.acc_ymd,5,2)||'.'||substr(A.acc_ymd,7) end) ESETDTT ,--결산일 TO_CHAR(case when D.BASE_YMD1 is null then null else substr(D.BASE_YMD1,1,4)||'.'||substr(D.BASE_YMD1,5,2)||'.'||substr(D.BASE_YMD1,7) end) bs_dt0, --재무년월 당년 TO_CHAR(case when D.BASE_YMD2 is null then null else substr(D.BASE_YMD2,1,4)||'.'||substr(D.BASE_YMD2,5,2)||'.'||substr(D.BASE_YMD2,7) end) bs_dt1, --재무년월 전년 TO_CHAR(case when D.BASE_YMD3 is null then null else substr(D.BASE_YMD3,1,4)||'.'||substr(D.BASE_YMD3,5,2)||'.'||substr(D.BASE_YMD3,7) end) bs_dt2, --재무년월 전전년 TO_CHAR(case when F.BASE_YMD1 is null then null else substr(F.BASE_YMD1,1,4)||'.'||substr(F.BASE_YMD1,5,2)||'.'||substr(F.BASE_YMD1,7) end) bs_dt3, --재무년월 당년 TO_CHAR(case when F.BASE_YMD2 is null then null else substr(F.BASE_YMD2,1,4)||'.'||substr(F.BASE_YMD2,5,2)||'.'||substr(F.BASE_YMD2,7) end) bs_dt4, --재무년월 전년 TO_CHAR(case when F.BASE_YMD3 is null then null else substr(F.BASE_YMD3,1,4)||'.'||substr(F.BASE_YMD3,5,2)||'.'||substr(F.BASE_YMD3,7) end) bs_dt5, --재무년월 전전년 trim(case when (D.DAE0620_1/1000000) < 1 AND (D.DAE0620_1/1000000) <> 0 then trim(to_char((D.DAE0620_1/1000000),'999,999,999,999,999.0')) when (D.DAE0620_1/1000000) = 0 then '0' else to_char((D.DAE0620_1/1000000),'999,999,999,999,999') end) bs59_0, --총자산 당년 trim(case when (D.DAE0620_2/1000000) < 1 AND (D.DAE0620_2/1000000) <> 0 then trim(to_char((D.DAE0620_2/1000000),'999,999,999,999,999.0')) when (D.DAE0620_2/1000000) = 0 then '0' else to_char((D.DAE0620_2/1000000),'999,999,999,999,999') end) bs59_1, --총자산 전년 trim(case when (D.DAE0620_3/1000000) < 1 AND (D.DAE0620_3/1000000) <> 0 then trim(to_char((D.DAE0620_3/1000000),'999,999,999,999,999.0')) when (D.DAE0620_3/1000000) = 0 then '0' else to_char((D.DAE0620_3/1000000),'999,999,999,999,999') end) bs59_2, --총자산 전전년 trim(case when (D.DAE0611_1/1000000) < 1 AND (D.DAE0611_1/1000000) <> 0 then trim(to_char((D.DAE0611_1/1000000),'999,999,999,999,999.0')) when (D.DAE0611_1/1000000) = 0 then '0' else to_char((D.DAE0611_1/1000000),'999,999,999,999,999') end) bs91_0, --부채총계 당년 trim(case when (D.DAE0611_2/1000000) < 1 AND (D.DAE0611_2/1000000) <> 0 then trim(to_char((D.DAE0611_2/1000000),'999,999,999,999,999.0')) when (D.DAE0611_2/1000000) = 0 then '0' else to_char((D.DAE0611_2/1000000),'999,999,999,999,999') end) bs91_1, --부채총계 전년 trim(case when (D.DAE0611_3/1000000) < 1 AND (D.DAE0611_3/1000000) <> 0 then trim(to_char((D.DAE0611_3/1000000),'999,999,999,999,999.0')) when (D.DAE0611_3/1000000) = 0 then '0' else to_char((D.DAE0611_3/1000000),'999,999,999,999,999') end) bs91_2, --부채총계 전전년 trim(case when (D.DAE0613_1/1000000) < 1 AND (D.DAE0613_1/1000000) <> 0 then trim(to_char((D.DAE0613_1/1000000),'999,999,999,999,999.0')) when (D.DAE0613_1/1000000) = 0 then '0' else to_char((D.DAE0613_1/1000000),'999,999,999,999,999') end) bs113_0, --자본총계 당년 trim(case when (D.DAE0613_2/1000000) < 1 AND (D.DAE0613_2/1000000) <> 0 then trim(to_char((D.DAE0613_2/1000000),'999,999,999,999,999.0')) when (D.DAE0613_2/1000000) = 0 then '0' else to_char((DAE0613_2/1000000),'999,999,999,999,999') end) bs113_1, --자본총계 전년 trim(case when (D.DAE0613_3/1000000) < 1 AND (D.DAE0613_3/1000000) <> 0 then trim(to_char((D.DAE0613_3/1000000),'999,999,999,999,999.0')) when (D.DAE0613_3/1000000) = 0 then '0' else to_char((D.DAE0613_3/1000000),'999,999,999,999,999') end) bs113_2, --자본총계 전전년 trim(case when (E.SON0701_1/1000000) < 1 AND (E.SON0701_1/1000000) <> 0 then trim(to_char((E.SON0701_1/1000000),'999,999,999,999,999.0')) when (E.SON0701_1/1000000) = 0 then '0' else to_char((E.SON0701_1/1000000),'999,999,999,999,999') end) pl01_0, --매출액_당기 trim(case when (E.SON0701_2/1000000) < 1 AND (E.SON0701_2/1000000) <> 0 then trim(to_char((E.SON0701_2/1000000),'999,999,999,999,999.0')) when (E.SON0701_2/1000000) = 0 then '0' else to_char((E.SON0701_2/1000000),'999,999,999,999,999') end) pl01_1, --매출액_전기 trim(case when (E.SON0701_3/1000000) < 1 AND (E.SON0701_3/1000000) <> 0 then trim(to_char((E.SON0701_3/1000000),'999,999,999,999,999.0')) when (E.SON0701_3/1000000) = 0 then '0' else to_char((E.SON0701_3/1000000),'999,999,999,999,999') end) pl01_2, --매출액_전전기 trim(case when (E.SON0709_1/1000000) < 1 AND (E.SON0709_1/1000000) <> 0 then trim(to_char((E.SON0709_1/1000000),'999,999,999,999,999.0')) when (E.SON0709_1/1000000) = 0 then '0' else to_char((E.SON0709_1/1000000),'999,999,999,999,999') end) pl27_0, --영업이익_당기 trim(case when (E.SON0709_2/1000000) < 1 AND (E.SON0709_2/1000000) <> 0 then trim(to_char((E.SON0709_2/1000000),'999,999,999,999,999.0')) when (E.SON0709_2/1000000) = 0 then '0' else to_char((E.SON0709_2/1000000),'999,999,999,999,999') end) pl27_1, --영업이익_전기 trim(case when (E.SON0709_3/1000000) < 1 AND (E.SON0709_3/1000000) <> 0 then trim(to_char((E.SON0709_3/1000000),'999,999,999,999,999.0')) when (E.SON0709_3/1000000) = 0 then '0' else to_char((E.SON0709_3/1000000),'999,999,999,999,999') end) pl27_2, --영업이익_전전기 trim(case when (E.SON0702_1/1000000) < 1 AND (E.SON0702_1/1000000) <> 0 then trim(to_char((E.SON0702_1/1000000),'999,999,999,999,999.0')) when (E.SON0702_1/1000000) = 0 then '0' else to_char((E.SON0702_1/1000000),'999,999,999,999,999') end) pl71_0, --당기순이익_당기 trim(case when (E.SON0702_2/1000000) < 1 AND (E.SON0702_2/1000000) <> 0 then trim(to_char((E.SON0702_2/1000000),'999,999,999,999,999.0')) when (E.SON0702_2/1000000) = 0 then '0' else to_char((E.SON0702_2/1000000),'999,999,999,999,999') end) pl71_1, --당기순이익_전기 trim(case when (E.SON0702_3/1000000) < 1 AND (E.SON0702_3/1000000) <> 0 then trim(to_char((E.SON0702_3/1000000),'999,999,999,999,999.0')) when (E.SON0702_3/1000000) = 0 then '0' else to_char((E.SON0702_3/1000000),'999,999,999,999,999') end) pl71_2, --당기순이익_전전기 (F.JAEMU1012_1) TR0053 ,--부채비율당기 (F.JAEMU1012_2) TR0052 ,--부채비율전기 (F.JAEMU1012_3) TR0051 ,--부채비율전전기 (F.JAEMU1014_1) TR0513 ,--차입금의존도당기 (F.JAEMU1014_2) TR0512 ,--차입금의존도전기 (F.JAEMU1014_3) TR0511 ,--차입금의존도전전기 (F.JAEMU1009_1) TR0523 ,--매출액 영업이익율당기 (F.JAEMU1009_2) TR0522 ,--매출액 영업이익율전기 (F.JAEMU1009_3) TR0521 ,--매출액 영업이익율전전기 (F.JAEMU1004_1) TR0103 ,--매출순이익율당기 (F.JAEMU1004_2) TR0102 ,--매출순이익율전기 (F.JAEMU1004_3) TR0101 ,--매출순이익율전전기 (F.JAEMU1024_1) TR0223 ,--매출액증가율당기 (F.JAEMU1024_2) TR0222 ,--매출액증가율전기 (F.JAEMU1024_3) TR0221 ,--매출액증가율전전기 (F.JAEMU1010_1) TR0013 ,--유동비율당기 (F.JAEMU1010_2) TR0012 ,--유동비율전기 (F.JAEMU1010_3) TR0011 --유동비율전전기 FROM ( SELECT BIZNO AS BIZNO, --사업자번호 UPCHE_ENG AS CMP_NM_ENG , --업체 영문명 SULIB_DATE AS ESTAB_YMD, --설립일 OWNER_KOR AS MGR_NM , --대표명 SCALE AS BZ_SCR, --대기업해당여부 '' AS GRP_NM , --소속그룹계열 ADDR1 AS UADDR , --본사주소 LISTED AS TYP, --상장등록법인여부(기업형태) GOODS AS PRODUCT , --주요취급품목1 TEL AS UTEL , --본사전화번호 FAX AS UFAX, --본사팩스번호 INWON AS EMP_NO --종업원 수 FROM NICE_COMPGAEYO A WHERE (BIZNO,UDATE) IN ( SELECT AA.BIZNO , MAX(AA.UDATE) UDATE FROM NICE_COMPGAEYO AA WHERE A.BIZNO = AA.BIZNO GROUP BY AA.BIZNO ) ) L , ( SELECT BIZNO AS BIZNO, --사업자번호 BASISDATE AS E_DATE, --평가 기준일 GRADE AS LASTGRD2, --신용등급 CASHGRADE AS CASH_GRADE, --현금흐름 FSDATE AS ACC_YMD -- 결산일자 FROM NICE_CREDITHIST A WHERE (BIZNO,UDATE, BASISDATE) IN ( SELECT AA.BIZNO,MAX(AA.UDATE),MAX(AA.BASISDATE) FROM NICE_CREDITHIST AA WHERE A.BIZNO = AA.BIZNO GROUP BY AA.BIZNO ) ) A , ( SELECT BIZNO, WATCHGRADE WATCH_GRD --WATCH 등급 FROM NICE_WATCHHIST A WHERE (BIZNO,UDATE, WATCHDATE) IN ( SELECT AA.BIZNO , MAX(UDATE) , MAX(WATCHDATE) FROM NICE_WATCHHIST AA WHERE A.BIZNO = AA.BIZNO GROUP BY AA.BIZNO ) ) B , ( SELECT '' AS ANN, --기업회생/파산정보 '' AS ANN_DATE FROM DUAL ) C , ( SELECT BIZNO,UDATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN DAE0620*1000000 ELSE 0 END) DAE0620_1, --총자산 5000 MAX(CASE WHEN RNUM = 2 THEN DAE0620*1000000 ELSE 0 END) DAE0620_2, MAX(CASE WHEN RNUM = 3 THEN DAE0620*1000000 ELSE 0 END) DAE0620_3, MAX(CASE WHEN RNUM = 1 THEN DAE0611*1000000 ELSE 0 END) DAE0611_1, --부채총계 8000 MAX(CASE WHEN RNUM = 2 THEN DAE0611*1000000 ELSE 0 END) DAE0611_2, MAX(CASE WHEN RNUM = 3 THEN DAE0611*1000000 ELSE 0 END) DAE0611_3, MAX(CASE WHEN RNUM = 1 THEN DAE0613*1000000 ELSE 0 END) DAE0613_1, --자본총계 8900 MAX(CASE WHEN RNUM = 2 THEN DAE0613*1000000 ELSE 0 END) DAE0613_2, MAX(CASE WHEN RNUM = 3 THEN DAE0613*1000000 ELSE 0 END) DAE0613_3 FROM ( SELECT BIZNO,UDATE, FSDATE AS BASE_YMD, MAX(CASE WHEN ITEMCODE = '5000' THEN AMT ELSE 0 END) AS DAE0620, MAX(CASE WHEN ITEMCODE = '8000' THEN AMT ELSE 0 END) AS DAE0611, MAX(CASE WHEN ITEMCODE = '8900' THEN AMT ELSE 0 END) AS DAE0613, ROW_NUMBER() OVER( PARTITION BY BIZNO,UDATE ORDER BY FSDATE DESC) RNUM FROM NICE_BS GROUP BY BIZNO,UDATE,FSDATE ) GROUP BY BIZNO,UDATE ) D , ( SELECT BIZNO,MAX(UDATE) UDATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN SON0701*1000000 ELSE null END) SON0701_1, --매출액 MAX(CASE WHEN RNUM = 2 THEN SON0701*1000000 ELSE null END) SON0701_2, MAX(CASE WHEN RNUM = 3 THEN SON0701*1000000 ELSE null END) SON0701_3, MAX(CASE WHEN RNUM = 1 THEN SON0709*1000000 ELSE null END) SON0709_1, --영업이익 MAX(CASE WHEN RNUM = 2 THEN SON0709*1000000 ELSE null END) SON0709_2, MAX(CASE WHEN RNUM = 3 THEN SON0709*1000000 ELSE null END) SON0709_3, MAX(CASE WHEN RNUM = 1 THEN SON0702*1000000 ELSE null END) SON0702_1, --당기순이익 MAX(CASE WHEN RNUM = 2 THEN SON0702*1000000 ELSE null END) SON0702_2, MAX(CASE WHEN RNUM = 3 THEN SON0702*1000000 ELSE null END) SON0702_3 FROM ( SELECT BIZNO,UDATE, FSDATE AS BASE_YMD, MAX(CASE WHEN ITEMCODE = '1000' THEN AMT ELSE 0 END) AS SON0701, MAX(CASE WHEN ITEMCODE = '5000' THEN AMT ELSE 0 END) AS SON0709, MAX(CASE WHEN ITEMCODE = '9000' THEN AMT ELSE 0 END) AS SON0702, ROW_NUMBER() OVER( PARTITION BY BIZNO,UDATE ORDER BY FSDATE DESC) RNUM FROM NICE_IS GROUP BY BIZNO,UDATE,FSDATE ) GROUP BY BIZNO ) E , ( SELECT BIZNO,MAX(UDATE) UDATE, MAX(CASE WHEN RNUM = 1 THEN BASE_YMD ELSE '' END) BASE_YMD1, --재무일자 MAX(CASE WHEN RNUM = 2 THEN BASE_YMD ELSE '' END) BASE_YMD2, MAX(CASE WHEN RNUM = 3 THEN BASE_YMD ELSE '' END) BASE_YMD3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1012 ELSE null END) JAEMU1012_1, --부채비율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1012 ELSE null END) JAEMU1012_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1012 ELSE null END) JAEMU1012_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1014 ELSE null END) JAEMU1014_1, --차입금의존도 MAX(CASE WHEN RNUM = 2 THEN JAEMU1014 ELSE null END) JAEMU1014_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1014 ELSE null END) JAEMU1014_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1009 ELSE null END) JAEMU1009_1, --영업이익률 MAX(CASE WHEN RNUM = 2 THEN JAEMU1009 ELSE null END) JAEMU1009_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1009 ELSE null END) JAEMU1009_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1004 ELSE null END) JAEMU1004_1, --순이익률 MAX(CASE WHEN RNUM = 2 THEN JAEMU1004 ELSE null END) JAEMU1004_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1004 ELSE null END) JAEMU1004_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1024 ELSE null END) JAEMU1024_1, --매출액 증가율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1024 ELSE null END) JAEMU1024_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1024 ELSE null END) JAEMU1024_3, MAX(CASE WHEN RNUM = 1 THEN JAEMU1010 ELSE null END) JAEMU1010_1, -- 유동비율 MAX(CASE WHEN RNUM = 2 THEN JAEMU1010 ELSE null END) JAEMU1010_2, MAX(CASE WHEN RNUM = 3 THEN JAEMU1010 ELSE null END) JAEMU1010_3 FROM ( SELECT BIZNO,UDATE, FSDATE AS BASE_YMD, MAX(CASE WHEN ITEMCODE = '3060' THEN BIYUL ELSE 0 END) AS JAEMU1012, MAX(CASE WHEN ITEMCODE = '3090' THEN BIYUL ELSE 0 END) AS JAEMU1014, MAX(CASE WHEN ITEMCODE = '1065' THEN BIYUL ELSE 0 END) AS JAEMU1009, MAX(CASE WHEN ITEMCODE = '1080' THEN BIYUL ELSE 0 END) AS JAEMU1004, MAX(CASE WHEN ITEMCODE = '1060' THEN BIYUL ELSE 0 END) AS JAEMU1024, MAX(CASE WHEN ITEMCODE = '3020' THEN BIYUL ELSE 0 END) AS JAEMU1010, ROW_NUMBER() OVER( PARTITION BY BIZNO,UDATE ORDER BY FSDATE DESC) RNUM FROM NICE_JR WHERE GUBUN = '1' GROUP BY BIZNO,UDATE, FSDATE ) GROUP BY BIZNO ) F, ( SELECT '' RM_GRD, --기업회생/파산정보 '' MODIDATE FROM dual ) G, (SELECT BIZNO, MAX(CASE WHEN GUBUN = '1' AND SEQ = 1 THEN UPCHE_KOR END) MO_1, MAX(CASE WHEN GUBUN = '1' AND SEQ = 1 THEN AMT END) MO_4, MAX(CASE WHEN GUBUN = '1' AND SEQ = 2 THEN UPCHE_KOR END) MO_2, MAX(CASE WHEN GUBUN = '1' AND SEQ = 2 THEN AMT END) MO_5, MAX(CASE WHEN GUBUN = '1' AND SEQ = 3 THEN UPCHE_KOR END) MO_3, MAX(CASE WHEN GUBUN = '1' AND SEQ = 3 THEN AMT END) MO_6, MAX(CASE WHEN GUBUN = '2' AND SEQ = 1 THEN UPCHE_KOR END) MI_1, MAX(CASE WHEN GUBUN = '2' AND SEQ = 1 THEN AMT END) MI_4, MAX(CASE WHEN GUBUN = '2' AND SEQ = 2 THEN UPCHE_KOR END) MI_2, MAX(CASE WHEN GUBUN = '2' AND SEQ = 2 THEN AMT END) MI_5, MAX(CASE WHEN GUBUN = '2' AND SEQ = 3 THEN UPCHE_KOR END) MI_3, MAX(CASE WHEN GUBUN = '2' AND SEQ = 3 THEN AMT END) MI_6 FROM ( SELECT BIZNO,GUBUN,SEQ,UPCHE_KOR, TRUNC(AMT / (SELECT SUM(AMT) FROM NICE_MAINCLIENT Y WHERE A.BIZNO = Y.BIZNO AND A.GUBUN = Y.GUBUN) * 100,0) AS AMT FROM NICE_MAINCLIENT A WHERE (BIZNO ,UDATE) IN (SELECT BIZNO ,MAX(UDATE) UDATE FROM NICE_MAINCLIENT WHERE A.BIZNO = BIZNO GROUP BY BIZNO ) ) GROUP BY BIZNO ) H, (SELECT BIZNO, MAX(CASE WHEN SEQ = 1 THEN NAME ELSE NULL END) MGR_NM1, MAX(CASE WHEN SEQ = 1 THEN JIBUN_RATE ELSE 0 END) LSH_STK_RATE1 , MAX(CASE WHEN SEQ = 2 THEN NAME ELSE NULL END) MGR_NM2, MAX(CASE WHEN SEQ = 2 THEN JIBUN_RATE ELSE 0 END) LSH_STK_RATE2 , MAX(CASE WHEN SEQ = 3 THEN NAME ELSE NULL END) MGR_NM3, MAX(CASE WHEN SEQ = 3 THEN JIBUN_RATE ELSE 0 END) LSH_STK_RATE3 FROM NICE_JUJU WHERE (BIZNO, UDATE) IN (SELECT BIZNO, MAX(UDATE) UDATE FROM NICE_JUJU GROUP BY BIZNO ) GROUP BY BIZNO ) I, (SELECT '' STT_NM , '' MODIDATE FROM DUAL ) J, ( SELECT BIZNO, AMT RATE_Y FROM ( SELECT BIZNO,SEQ,AMT,ROW_NUMBER() OVER (PARTITION BY BIZNO ORDER BY SEQ) RNUM FROM NICE_MAINCLIENT A WHERE (BIZNO ,UDATE) IN (SELECT BIZNO ,MAX(UDATE) UDATE FROM NICE_MAINCLIENT WHERE A.BIZNO = BIZNO GROUP BY BIZNO ) AND GUBUN = '1' AND UPCHE_KOR like '%삼성중공업%' ) WHERE RNUM = 1 ) K WHERE 1=1 AND L.BIZNO = A.BIZNO(+) AND L.BIZNO = B.BIZNO(+) AND L.BIZNO = D.BIZNO(+) AND L.BIZNO = E.BIZNO(+) AND L.BIZNO = F.BIZNO(+) AND L.BIZNO = H.BIZNO(+) AND L.BIZNO = I.BIZNO(+) AND L.BIZNO = K.BIZNO(+) AND L.BIZNO = :reno AND 'E' = :credit_gb