diff options
Diffstat (limited to 'lib/oracle-db/nonsap/services/creditServiceQuery.sql')
| -rw-r--r-- | lib/oracle-db/nonsap/services/creditServiceQuery.sql | 1387 |
1 files changed, 1387 insertions, 0 deletions
diff --git a/lib/oracle-db/nonsap/services/creditServiceQuery.sql b/lib/oracle-db/nonsap/services/creditServiceQuery.sql new file mode 100644 index 00000000..de34862a --- /dev/null +++ b/lib/oracle-db/nonsap/services/creditServiceQuery.sql @@ -0,0 +1,1387 @@ + ----이크레더블 + + 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
\ No newline at end of file |