SELECT * FROM TB_SY1007M WHERE CDTP IN ('GDS_CD')
--       ,ENT_PERS_TPCD             --개인기업구분코드)

SELECT * FROM   ANGEL.TB_IL1014M

SELECT * FROM ANGEL.TB_IL1009M WHERE CUST_NO IN ('1000000269', '1000000436', '1000000221','2128604883','2158784578')

SELECT *
FROM   ANGEL.EXCEL    


CREATE TABLE ANGEL.EXCEL01 AS
          SELECT    A.*
          ,         B.CUST_NO
          ,         C.CMBT_CUST_NO 
          ,         D.CNTRNO
          FROM 
          (
                    SELECT    A
                    ,         B
                    ,         C
                    ,         K
                    ,         CASE WHEN K='보통주' THEN '01' ELSE '03' END AS GDS_CD
                    ,         J
                    FROM      ANGEL.EXCEL         IA    
          )         A
          LEFT JOIN
          (
                    SELECT    CUST_NO
                    ,         BMAN_REG_NO
                    ,         KOR_CUST_NM
                    FROM      ANGEL.TB_SY1011M
          )        B
          ON        A.C = B.BMAN_REG_NO
          LEFT JOIN
          (
                    SELECT    CMBT_CUST_NO
                    ,         REPLACE(CMBT_NM, ' ') AS CMBT_NM
                    FROM      ANGEL.TB_FD1003M
          )        C
          ON        A.J = C.CMBT_NM
          LEFT JOIN
          (
                    SELECT    *
                    FROM
                    (
                              SELECT    ROW_NUMBER() OVER(PARTITION BY CMBT_CUST_NO, CUST_NO, GDS_CD ORDER BY CMBT_CUST_NO, CUST_NO, GDS_CD, CNTRNO DESC) AS RN
                              ,         IB.*
                              FROM      ANGEL.TB_IL1009M    IB
                    )         IB
                    WHERE     IB.RN = 1
          )         D          
          ON        C.CMBT_CUST_NO = D.CMBT_CUST_NO
          AND       B.CUST_NO = D.CUST_NO
          AND       A.GDS_CD = D.GDS_CD
;


UPDATE   ANGEL.EXCEL          A
SET       A.CNTRNO = (SELECT B.CNTRNO FROM ANGEL.EXCEL01 B WHERE B.A = A.A)
;
INSERT INTO ANGEL.TB_IL1010D
(
          CNTRNO
,         EXE_DGRN
,         TRD_DT
,         TRD_SEQ
,         TRD_CD
,         CRC_TPCD
,         OGN_PRC_STD_TRD_AMT
,         PRFT_GAIN_OGN_PRC
,         TRD_MAIN_CNT
,         REG_DTM
,         REG_EMPNO
,         MODY_DTM
,         MODY_EMPNO
,         DEL_YN
)
SELECT    A.CNTRNO
,         A.EXE_DGRN
,         A.TRD_DT
,         (SELECT NVL(MAX(TRD_SEQ),0)+A.TRD_SEQ FROM ANGEL.TB_IL1010D B WHERE B.CNTRNO = A.CNTRNO AND B.TRD_DT = A.TRD_DT) AS TRD_SEQ
,         A.TRD_CD
,         A.CRC_TPCD
,         A.OGN_PRC_STD_TRD_AMT
,         A.PRFT_GAIN_OGN_PRC
,         A.TRD_MAIN_CNT
,         SYSDATE   AS REG_DTM
,         'EXCEL'   AS REG_EMPNO
,         SYSDATE   AS MODY_DTM
,         'EXCEL'   AS MODY_EMPNO
,         '0'       AS DEL_YN
FROM
(
          SELECT    CNTRNO
          ,         EXE_DGRN
          ,         REPLACE(TRD_DT,'-','')        AS TRD_DT
          ,         ROW_NUMBER() OVER(PARTITION BY CNTRNO,TRD_DT ORDER BY CNTRNO,TRD_DT)     AS TRD_SEQ                   --거래순번
          ,         TRD_CD
          ,         MAX(CRC_TPCD)                 AS CRC_TPCD
          ,         SUM(OGN_PRC_STD_TRD_AMT)      AS OGN_PRC_STD_TRD_AMT
          ,         SUM(PRFT_GAIN_OGN_PRC)        AS PRFT_GAIN_OGN_PRC
          ,         SUM(TRD_MAIN_CNT)             AS TRD_MAIN_CNT
          FROM
          (
                    SELECT    
                           CNTRNO                          AS CNTRNO                    --계약번호
                           ,1                               AS EXE_DGRN                  --실행차수
                           ,E                               AS TRD_DT                    --거래일자
                           ,CASE WHEN G = '회수' THEN 'PC'
                                 ELSE 'GS' END              AS TRD_CD                  --거래코드
                           ,'KRW'                           AS CRC_TPCD                  --통화구분코드
                           , CASE WHEN H = '원금'   THEN TO_NUMBER(F)
                                  ELSE 0 END                AS OGN_PRC_STD_TRD_AMT       --거래금액-원화
                           , CASE WHEN H = '수익'  THEN  TO_NUMBER(F)
                                  ELSE 0 END                AS PRFT_GAIN_OGN_PRC         --거래수익-원화
                           ,TO_NUMBER(I)                               AS TRD_MAIN_CNT              --거래주수   
                    FROM   ANGEL.EXCEL   
                    --WHERE CNTRNO IS NOT NULL
                     WHERE A = '181'
          ) 
          GROUP BY 
                    CNTRNO
          ,         EXE_DGRN
          ,         TRD_DT
          ,         TRD_CD
)         A
ORDER BY 1,3

         
          SELECT * FROM ANGEL.EXCEL WHERE CNTRNO IS  NULL
          
          SELECT * FROM ANGEL.EXCEL WHERE C='1208784004'
          
          SELECT * FROM ANGEL.TB_IL1009M WHERE CNTRNO = 'I20120039'
          
          SELECT * FROM ANGEL.TB_SY1011M WHERE CUST_NO = '1000000185'
          
           SELECT * FROM ANGEL.TB_IL1010D WHERE TRD_CD IN ('PC','GS') AND DEL_YN = '0' 
 
 SELECT * FROM ANGEL.TB_IL1010D WHERE TRD_CD IN ('PC','GS') AND DEL_YN = '0' AND REG_EMPNO <>'EXCEL'


--엑셀 데이터로 올린 것만 제외하고 회수, 기타수익 거래는 삭제 처리 요청 (신혜란) 
UPDATE ANGEL.TB_IL1010D 
SET       DEL_YN = '1'
,         MODY_DTM=SYSDATE
,         MODY_EMPNO = 'EXCEL_DEL'
WHERE TRD_CD IN ('PC','GS') AND DEL_YN = '0' AND REG_EMPNO <>'EXCEL'