diff options
| author | joonhoekim <26rote@gmail.com> | 2025-07-07 08:59:50 +0000 |
|---|---|---|
| committer | joonhoekim <26rote@gmail.com> | 2025-07-07 08:59:50 +0000 |
| commit | 2ded9dcc3968b7fff3e29a128d327df6b77923ab (patch) | |
| tree | dba9a0b9b79b8b04e4f9512d6ea60b9091aaf8dd | |
| parent | 44bdb81a60d3a44ba7e379f3c20fe6d8fb284339 (diff) | |
(김준회) 벤더 상세페이지 - 신용평가정보 조회 (nonsap oracle db)
| -rw-r--r-- | app/[lng]/evcp/(evcp)/vendors/[id]/info/credit/page.tsx | 440 | ||||
| -rw-r--r-- | app/[lng]/evcp/(evcp)/vendors/[id]/info/layout.tsx | 4 | ||||
| -rw-r--r-- | lib/oracle-db/nonsap/services/creditService.ts | 215 | ||||
| -rw-r--r-- | lib/oracle-db/nonsap/services/creditServiceMapping.json | 10 | ||||
| -rw-r--r-- | lib/oracle-db/nonsap/services/creditServiceQuery.sql | 1387 |
5 files changed, 2056 insertions, 0 deletions
diff --git a/app/[lng]/evcp/(evcp)/vendors/[id]/info/credit/page.tsx b/app/[lng]/evcp/(evcp)/vendors/[id]/info/credit/page.tsx new file mode 100644 index 00000000..e90969b5 --- /dev/null +++ b/app/[lng]/evcp/(evcp)/vendors/[id]/info/credit/page.tsx @@ -0,0 +1,440 @@ +'use client'; + +import { useState, useEffect } from 'react'; +import { useParams } from 'next/navigation'; +import { getCreditInfo } from '@/lib/oracle-db/nonsap/services/creditService'; +import { + Table, + TableBody, + TableCell, + TableHead, + TableHeader, + TableRow, +} from '@/components/ui/table'; + +// 신용평가사 옵션 +const creditServices = [ + { code: 'I', name: '이크레더블' }, + { code: 'K', name: '한국기업데이터' }, + { code: 'N', name: '나이스디앤비' }, + { code: 'E', name: 'NICE신용평가사' } +]; + +interface CreditData { + // 기본 정보 + RESNO: string; + EENTNM: string; + OPEDT: string; + REPR_NM: string; + TYSCALE: string; + RELCMP: string; + ADR: string; + LISTYN: string; + GOODSNM: string; + TELNO: string; + FAXNO: string; + FSTRDRT: string; + + // 매출순위 + MO_1: string; + MO_4: string; + MO_2: string; + MO_5: string; + MO_3: string; + MO_6: string; + + // 매입순위 + MI_1: string; + MI_4: string; + MI_2: string; + MI_5: string; + MI_3: string; + MI_6: string; + + // 지분관계 + GIBUN_RL_1: string; + LSH_STK_RATE1: string; + GIBUN_RL_2: string; + LSH_STK_RATE2: string; + GIBUN_RL_3: string; + LSH_STK_RATE3: string; + + // 기타 정보 + HAPGYE: string; + NOTICE: string; + NOTICE_DT: string; + HPGBNCR_TY: string; + FCLOSDT: string; + LASTGRD: string; + DECISION: string; + FRISKRV: string; + EXPIRE_GB: string; + FWATCHD: string; + SUVDT: string; + ESETDTT: string; + + // 재무년월 + bs_dt0: string; + bs_dt1: string; + bs_dt2: string; + + // 재무현황 + bs59_0: string; // 총자산 + bs59_1: string; + bs59_2: string; + bs91_0: string; // 부채총계 + bs91_1: string; + bs91_2: string; + bs113_0: string; // 자본총계 + bs113_1: string; + bs113_2: string; + pl01_0: string; // 매출액 + pl01_1: string; + pl01_2: string; + pl27_0: string; // 영업이익 + pl27_1: string; + pl27_2: string; + pl71_0: string; // 당기순이익 + pl71_1: string; + pl71_2: string; + + // 재무비율 + TR0053: string; // 부채비율 + TR0052: string; + TR0051: string; + TR0513: string; // 차입금의존도 + TR0512: string; + TR0511: string; + TR0523: string; // 영업이익율 + TR0522: string; + TR0521: string; + TR0103: string; // 매출순이익율 + TR0102: string; + TR0101: string; + TR0223: string; // 매출액증가율 + TR0222: string; + TR0221: string; + TR0013: string; // 유동비율 + TR0012: string; + TR0011: string; +} + +export default function CreditInfoPage() { + const params = useParams(); + const vendorId = params?.id as string; + + const [selectedCreditService, setSelectedCreditService] = useState<string>('I'); + const [creditData, setCreditData] = useState<CreditData | null>(null); + const [loading, setLoading] = useState(false); + const [error, setError] = useState<string | null>(null); + + // 데이터 로딩 함수 + const loadCreditData = async (reno: string, creditGb: string) => { + setLoading(true); + setError(null); + + try { + const result = await getCreditInfo(reno, creditGb); + if (result && result.length > 0) { + setCreditData(result[0]); + } else { + setCreditData(null); + } + } catch (err) { + setError('데이터를 불러오는 중 오류가 발생했습니다.'); + console.error(err); + } finally { + setLoading(false); + } + }; + + // 신용평가사 선택 변경 + const handleCreditServiceChange = (code: string) => { + setSelectedCreditService(code); + if (vendorId) { + loadCreditData(vendorId, code); + } + }; + + // 초기 데이터 로딩 + useEffect(() => { + if (vendorId) { + loadCreditData(vendorId, selectedCreditService); + } + }, [vendorId]); + + return ( + <div className="p-6 bg-background"> + <div className="mb-6"> + <h2 className="text-xl font-bold mb-4">협력업체 신용정보</h2> + + {/* 신용평가사 선택 */} + <div className="mb-4"> + <span className="text-sm text-muted-foreground mr-4">평가업체의 기본 정보를 확인 및 관리하는 화면입니다.</span> + <div className="flex space-x-4 mt-2"> + {creditServices.map((service) => ( + <label key={service.code} className="flex items-center space-x-2 cursor-pointer"> + <input + type="checkbox" + checked={selectedCreditService === service.code} + onChange={() => handleCreditServiceChange(service.code)} + className="h-4 w-4 rounded border-border text-primary focus:ring-primary" + /> + <span className="text-sm text-foreground">{service.name}</span> + </label> + ))} + </div> + </div> + </div> + + {loading && ( + <div className="text-center py-8"> + <div className="inline-block animate-spin rounded-full h-8 w-8 border-b-2 border-primary"></div> + <p className="mt-2 text-muted-foreground">데이터를 불러오는 중...</p> + </div> + )} + + {error && ( + <div className="bg-destructive/10 border border-destructive/20 rounded-md p-4 mb-4"> + <p className="text-destructive">{error}</p> + </div> + )} + + {creditData && ( + <div className="space-y-6"> + {/* 매출순위, 매입순위, 지분관계 */} + <div className="grid grid-cols-3 gap-4"> + {/* 매출순위 */} + <div className="border"> + <div className="bg-muted p-3 text-center font-semibold">매출순위</div> + <Table> + <TableBody> + <TableRow> + <TableCell className="text-center font-medium border-r">1</TableCell> + <TableCell className="border-r">{creditData.MO_1}</TableCell> + <TableCell className="text-right">{creditData.MO_4} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">2</TableCell> + <TableCell className="border-r">{creditData.MO_2}</TableCell> + <TableCell className="text-right">{creditData.MO_5} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">3</TableCell> + <TableCell className="border-r">{creditData.MO_3}</TableCell> + <TableCell className="text-right">{creditData.MO_6} %</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + + {/* 매입순위 */} + <div className="border"> + <div className="bg-muted p-3 text-center font-semibold">매입순위</div> + <Table> + <TableBody> + <TableRow> + <TableCell className="text-center font-medium border-r">1</TableCell> + <TableCell className="border-r">{creditData.MI_1}</TableCell> + <TableCell className="text-right">{creditData.MI_4} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">2</TableCell> + <TableCell className="border-r">{creditData.MI_2}</TableCell> + <TableCell className="text-right">{creditData.MI_5} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">3</TableCell> + <TableCell className="border-r">{creditData.MI_3}</TableCell> + <TableCell className="text-right">{creditData.MI_6} %</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + + {/* 지분관계 */} + <div className="border"> + <div className="bg-muted p-3 text-center font-semibold">지분관계</div> + <Table> + <TableBody> + <TableRow> + <TableCell className="text-center font-medium border-r">1</TableCell> + <TableCell className="border-r">{creditData.GIBUN_RL_1}</TableCell> + <TableCell className="text-right">{creditData.LSH_STK_RATE1} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">2</TableCell> + <TableCell className="border-r">{creditData.GIBUN_RL_2}</TableCell> + <TableCell className="text-right">{creditData.LSH_STK_RATE2} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">3</TableCell> + <TableCell className="border-r">{creditData.GIBUN_RL_3}</TableCell> + <TableCell className="text-right">{creditData.LSH_STK_RATE3} %</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + </div> + + {/* 기본 정보 */} + <div className="grid grid-cols-3 gap-4"> + {/* 섹션1: 종업원 수, 신용등급, WATCH 등급 */} + <div className="border"> + <Table> + <TableBody> + <TableRow> + <TableCell className="bg-muted font-medium border-r">종업원 수</TableCell> + <TableCell>{creditData.HAPGYE}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">신용등급</TableCell> + <TableCell className="bg-blue-100 dark:bg-blue-900/30 font-semibold">{creditData.LASTGRD}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">WATCH등급</TableCell> + <TableCell>{creditData.FWATCHD}</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + + {/* 섹션2: 기업회생/파산정보, 현금흐름등급, 평가기준일 */} + <div className="border"> + <Table> + <TableBody> + <TableRow> + <TableCell className="bg-muted font-medium border-r">기업회생/파산정보</TableCell> + <TableCell>{creditData.NOTICE || '없음'}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">현금흐름등급</TableCell> + <TableCell className="bg-green-100 dark:bg-green-900/30 font-semibold">{creditData.DECISION}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">평가기준일</TableCell> + <TableCell>{creditData.SUVDT}</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + + {/* 섹션3: 휴/폐업 여부, RISK등급, 결산기준일 */} + <div className="border"> + <Table> + <TableBody> + <TableRow> + <TableCell className="bg-muted font-medium border-r">휴/폐업 여부</TableCell> + <TableCell>{creditData.HPGBNCR_TY || '정상'}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">RISK등급</TableCell> + <TableCell>{creditData.FRISKRV}</TableCell> + </TableRow> + <TableRow> + <TableCell className="bg-muted font-medium border-r">결산기준일</TableCell> + <TableCell>{creditData.ESETDTT}</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + </div> + + {/* 재무현황 */} + <div className="border"> + <div className="bg-muted p-3 text-center font-semibold">재무현황(3개년)</div> + <Table> + <TableHeader> + <TableRow> + <TableHead className="text-center border-r">구분</TableHead> + <TableHead className="text-center border-r">총자산<br/>(단위:백만원)</TableHead> + <TableHead className="text-center border-r">부채총계<br/>(단위:백만원)</TableHead> + <TableHead className="text-center border-r">자본총계<br/>(단위:백만원)</TableHead> + <TableHead className="text-center border-r">매출액<br/>(단위:백만원)</TableHead> + <TableHead className="text-center border-r">영업이익<br/>(단위:백만원)</TableHead> + <TableHead className="text-center">당기순이익<br/>(단위:백만원)</TableHead> + </TableRow> + </TableHeader> + <TableBody> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt0}</TableCell> + <TableCell className="text-right border-r">{creditData.bs59_0}</TableCell> + <TableCell className="text-right border-r">{creditData.bs91_0}</TableCell> + <TableCell className="text-right border-r">{creditData.bs113_0}</TableCell> + <TableCell className="text-right border-r">{creditData.pl01_0}</TableCell> + <TableCell className="text-right border-r">{creditData.pl27_0}</TableCell> + <TableCell className="text-right">{creditData.pl71_0}</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt1}</TableCell> + <TableCell className="text-right border-r">{creditData.bs59_1}</TableCell> + <TableCell className="text-right border-r">{creditData.bs91_1}</TableCell> + <TableCell className="text-right border-r">{creditData.bs113_1}</TableCell> + <TableCell className="text-right border-r">{creditData.pl01_1}</TableCell> + <TableCell className="text-right border-r">{creditData.pl27_1}</TableCell> + <TableCell className="text-right">{creditData.pl71_1}</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt2}</TableCell> + <TableCell className="text-right border-r">{creditData.bs59_2}</TableCell> + <TableCell className="text-right border-r">{creditData.bs91_2}</TableCell> + <TableCell className="text-right border-r">{creditData.bs113_2}</TableCell> + <TableCell className="text-right border-r">{creditData.pl01_2}</TableCell> + <TableCell className="text-right border-r">{creditData.pl27_2}</TableCell> + <TableCell className="text-right">{creditData.pl71_2}</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + + {/* 재무비율 */} + <div className="border"> + <div className="bg-muted p-3 text-center font-semibold">재무비율(3개년)</div> + <Table> + <TableHeader> + <TableRow> + <TableHead className="text-center border-r">구분</TableHead> + <TableHead className="text-center border-r">부채비율</TableHead> + <TableHead className="text-center border-r">차입금의존도</TableHead> + <TableHead className="text-center border-r">영업이익율</TableHead> + <TableHead className="text-center border-r">순이익율</TableHead> + <TableHead className="text-center border-r">전년대비 매출액 증가율</TableHead> + <TableHead className="text-center">유동비율</TableHead> + </TableRow> + </TableHeader> + <TableBody> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt0}</TableCell> + <TableCell className="text-right border-r">{creditData.TR0053} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0513} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0523} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0103} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0223} %</TableCell> + <TableCell className="text-right">{creditData.TR0013} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt1}</TableCell> + <TableCell className="text-right border-r">{creditData.TR0052} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0512} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0522} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0102} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0222} %</TableCell> + <TableCell className="text-right">{creditData.TR0012} %</TableCell> + </TableRow> + <TableRow> + <TableCell className="text-center font-medium border-r">{creditData.bs_dt2}</TableCell> + <TableCell className="text-right border-r">{creditData.TR0051} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0511} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0521} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0101} %</TableCell> + <TableCell className="text-right border-r">{creditData.TR0221} %</TableCell> + <TableCell className="text-right">{creditData.TR0011} %</TableCell> + </TableRow> + </TableBody> + </Table> + </div> + </div> + )} + </div> + ); +} diff --git a/app/[lng]/evcp/(evcp)/vendors/[id]/info/layout.tsx b/app/[lng]/evcp/(evcp)/vendors/[id]/info/layout.tsx index 7826a7c0..50e2723e 100644 --- a/app/[lng]/evcp/(evcp)/vendors/[id]/info/layout.tsx +++ b/app/[lng]/evcp/(evcp)/vendors/[id]/info/layout.tsx @@ -39,6 +39,10 @@ export default async function SettingsLayout({ href: `/${lng}/evcp/vendors/${id}/info/basic`, }, { + title: "신용평가", + href: `/${lng}/evcp/vendors/${id}/info/credit`, + }, + { title: "공급품목(패키지)", href: `/${lng}/evcp/vendors/${id}/info/items`, }, diff --git a/lib/oracle-db/nonsap/services/creditService.ts b/lib/oracle-db/nonsap/services/creditService.ts new file mode 100644 index 00000000..60e4f287 --- /dev/null +++ b/lib/oracle-db/nonsap/services/creditService.ts @@ -0,0 +1,215 @@ +'use server'; + +import { oracleKnex, getOracleConnection } from "@/lib/oracle-db/db"; +import { readFileSync } from "fs"; +import { join } from "path"; +import creditServiceMapping from "./creditServiceMapping.json"; + +// SQL 파일 읽기 +const getCreditQuerySQL = () => { + const sqlPath = join(process.cwd(), "lib/oracle-db/nonsap/services/creditServiceQuery.sql"); + return readFileSync(sqlPath, "utf8"); +}; + +// 신용평가사 코드 변환 함수 +const convertCreditGbToCode = (creditGb: string): string => { + // 이미 1글자 코드인 경우 그대로 반환 + if (creditGb.length === 1 && creditServiceMapping.hasOwnProperty(creditGb)) { + return creditGb; + } + + // 한글 이름인 경우 코드로 변환 + const code = creditServiceMapping[creditGb as keyof typeof creditServiceMapping]; + if (code && typeof code === "string" && code.length === 1) { + return code; + } + + throw new Error(`유효하지 않은 신용평가사 코드: ${creditGb}`); +}; + + + +// 방법 1: knex.raw() 사용 (네임드 바인드 변수) +// reno: 사업자번호, credit_gb: 신용평가사 코드 +export const getCreditInfo = async (reno: string, credit_gb: string) => { + const query = getCreditQuerySQL(); + const creditGbCode = convertCreditGbToCode(credit_gb); + + const result = await oracleKnex.raw(query, { + reno: reno, + credit_gb: creditGbCode + }); + + // Oracle knex raw 결과에서 실제 rows 추출 + const rows = Array.isArray(result) ? result : result.rows || []; + + return rows; + + // UI 테스트용 목업 데이터 (필요시 주석 해제) + // const mockData = getMockCreditData(reno, credit_gb); + // return [mockData]; +}; + +// 방법 2: oracledb 직접 사용 (네임드 바인드 변수) +// reno: 사업자번호, credit_gb: 신용평가사 코드 +export const getCreditInfoWithOracleDb = async (reno: string, credit_gb: string) => { + + const connection = await getOracleConnection(); + + try { + const query = getCreditQuerySQL(); + const creditGbCode = convertCreditGbToCode(credit_gb); + + const result = await connection.execute(query, { + reno: reno, + credit_gb: creditGbCode + }); + + return result.rows || []; + } finally { + await connection.close(); + } + + // UI 테스트용 목업 데이터 (필요시 주석 해제) + // const mockData = getMockCreditData(reno, credit_gb); + // return [mockData]; +}; + +// 편의 함수: 신용평가사 코드 목록 조회 +export const getCreditServiceCodes = async () => { + const codes = Object.keys(creditServiceMapping).filter(key => key.length === 1); + return codes.map(code => ({ + code: code, + name: creditServiceMapping[code as keyof typeof creditServiceMapping] + })); +}; + +// 편의 함수: 신용평가사 이름 목록 조회 +export const getCreditServiceNames = async () => { + const names = Object.keys(creditServiceMapping).filter(key => key.length > 1); + return names.map(name => ({ + name: name, + code: creditServiceMapping[name as keyof typeof creditServiceMapping] + })); +}; + +// 임시 테스트 데이터 +const getMockCreditData = (reno: string, credit_gb: string) => { + return { + RESNO: reno, + EENTNM: "SAMPLE COMPANY LTD", + OPEDT: "2005.03.15", + REPR_NM: "홍길동", + TYSCALE: "중소기업", + RELCMP: "독립회사", + ADR: "서울특별시 강남구 테헤란로 123", + LISTYN: "비상장", + GOODSNM: "전자부품제조", + TELNO: "02-1234-5678", + FAXNO: "02-1234-5679", + FSTRDRT: "15.2", + + // 매출순위 + MO_1: "현대중공업", + MO_4: "27", + MO_2: "대우조선해양", + MO_5: "25", + MO_3: "삼성물산", + MO_6: "21", + + // 매입순위 + MI_1: "안전세관", + MI_4: "152", + MI_2: "부산세관", + MI_5: "84", + MI_3: "포디어션푸드(주)", + MI_6: "4", + + // 지분관계 + GIBUN_RL_1: "Kongsberg Maritime", + LSH_STK_RATE1: "100", + GIBUN_RL_2: "", + LSH_STK_RATE2: "0", + GIBUN_RL_3: "", + LSH_STK_RATE3: "0", + + // 기타 정보 + HAPGYE: "228", + NOTICE: "", + NOTICE_DT: "", + HPGBNCR_TY: "정상", + FCLOSDT: "", + LASTGRD: "BBB+", + DECISION: "A", + FRISKRV: "R-2", + EXPIRE_GB: "", + FWATCHD: "정상", + SUVDT: "2021.04.26", + ESETDTT: "2020.12.31", + + // 재무년월 + bs_dt0: "20.12.31", + bs_dt1: "19.12.31", + bs_dt2: "18.12.31", + + // 재무현황 - 총자산 + bs59_0: "63,307", + bs59_1: "63,307", + bs59_2: "62,216", + + // 재무현황 - 부채총계 + bs91_0: "27,660", + bs91_1: "36,196", + bs91_2: "23,205", + + // 재무현황 - 자본총계 + bs113_0: "125,474", + bs113_1: "142,804", + bs113_2: "89,251", + + // 재무현황 - 매출액 + pl01_0: "36,247", + pl01_1: "34,866", + pl01_2: "39,011", + + // 재무현황 - 영업이익 + pl27_0: "6,425", + pl27_1: "7,140", + pl27_2: "5,389", + + // 재무현황 - 당기순이익 + pl71_0: "5,286", + pl71_1: "5,761", + pl71_2: "4,325", + + // 재무비율 - 부채비율 + TR0053: "55.3", + TR0052: "0", + TR0051: "59.5", + + // 재무비율 - 차입금의존도 + TR0513: "0", + TR0512: "0", + TR0511: "0", + + // 재무비율 - 영업이익율 + TR0523: "5.1", + TR0522: "5", + TR0521: "6", + + // 재무비율 - 순이익율 + TR0103: "4.2", + TR0102: "4", + TR0101: "4.8", + + // 재무비율 - 매출액증가율 + TR0223: "-19.1", + TR0222: "59.3", + TR0221: "-13.9", + + // 재무비율 - 유동비율 + TR0013: "194.4", + TR0012: "172.7", + TR0011: "230.8" + }; +};
\ No newline at end of file diff --git a/lib/oracle-db/nonsap/services/creditServiceMapping.json b/lib/oracle-db/nonsap/services/creditServiceMapping.json new file mode 100644 index 00000000..0eeeca3b --- /dev/null +++ b/lib/oracle-db/nonsap/services/creditServiceMapping.json @@ -0,0 +1,10 @@ +{ + "I": "이크레더블", + "K": "한국기업데이터", + "N": "나이스디앤비", + "E": "NICE 신용평가사", + "이크레더블": "I", + "한국기업데이터": "K", + "나이스디앤비": "N", + "NICE 신용평가사": "E" +}
\ No newline at end of file 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 |