본문 바로가기
작업일지

금융상품실적현황(EIS개발)

소마후니 2015. 9. 14. 18:48

--DEFINE BASE_DT = '20090402'

--FetEvalAmt00
--금융상품실적현황

SELECT /*+ MG$FetEvalAmt00 영업지원 작성자_황해훈 */
       V1.SEQ                                              /*!type long   */ /*!시퀀스     */ SEQ,
       NVL(V2.CNT,0) / 100000000                           /*!type long   */ /*!목표금액   */ AMT1,
       NVL(V1.EVAL_AMT1,0) / 100000000                     /*!type long   */ /*!당일금액   */ AMT2,
       NVL(V1.EVAL_AMT1 - V1.EVAL_AMT2,0) / 100000000      /*!type long   */ /*!전일금액   */ AMT3,
       NVL(V1.EVAL_AMT1 - V1.EVAL_AMT3,0) / 100000000      /*!type long   */ /*!전월금액   */ AMT4,
       NVL(V1.EVAL_AMT1 - V1.EVAL_AMT4,0) / 100000000      /*!type long   */ /*!전년금액   */ AMT5,
       NVL(V1.EVAL_AMT1 / V2.CNT * 100,0)                  /*!type double */ /*!진척도     */ ACHV_RAT
FROM (
    -- 수익증권
 SELECT 1 SEQ,
        SUM(CASE WHEN BASE_DT = /*!type char(8)*/ /*!기준일 */:BASE_DT
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT1,
        SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT2,
        SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT3,
        SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT4
 FROM SL_TBL_ACNT_IVTRST_BAL
 WHERE BASE_DT IN (
                   :BASE_DT, --당일
                   TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                   TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                   SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                   )

 UNION ALL

 SELECT 2 SEQ,
        SUM(CASE WHEN BASE_DT = :BASE_DT
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT1,
        SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT2,
        SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT3,
        SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT4
 FROM SL_TBL_ACNT_IVTRST_BAL A,
      CM_TBM_BRN B
 WHERE A.MGMT_PSN_BRN_NO = B.BRN_NO
 AND   B.HDNBR_TP  = '1'
 AND   BASE_DT IN (
                   :BASE_DT, --당일
                   TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                   TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                   SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                   )

 UNION ALL

 SELECT 3 SEQ,
        SUM(CASE WHEN BASE_DT = :BASE_DT
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT1,
        SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT2,
        SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT3,
        SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                 THEN EVAL_AMT
                 ELSE 0
                 END) EVAL_AMT4
 FROM SL_TBL_ACNT_IVTRST_BAL A,
      CM_TBM_BRN B
 WHERE A.MGMT_PSN_BRN_NO = B.BRN_NO
 AND   B.HDNBR_TP  != '1'
 AND   BASE_DT IN (
                   :BASE_DT, --당일
                   TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                   TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                   SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                   )
 UNION ALL
    -- RP
 SELECT 4 SEQ ,
        NVL(SUM(CASE WHEN BASE_DT = :BASE_DT
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT1,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT2,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT3,
        NVL(SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT4
 FROM
         BM_TBM_DT_CF_RP_FXDT_BAL A
         ,CM_TBM_BRN              B
 WHERE   A.MGMT_BRN_NO  =  B.BRN_NO
 AND     BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )

 UNION ALL

 SELECT 5 SEQ ,
        NVL(SUM(CASE WHEN BASE_DT = :BASE_DT
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT1,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT2,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT3,
        NVL(SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT4
 FROM
         BM_TBM_DT_CF_RP_FXDT_BAL A
         ,CM_TBM_BRN              B
 WHERE   A.MGMT_BRN_NO  =  B.BRN_NO
 AND     BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )
 AND     HDNBR_TP = '1'

 UNION ALL

 SELECT 6 SEQ ,
        NVL(SUM(CASE WHEN BASE_DT = :BASE_DT
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT1,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT2,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT3,
        NVL(SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT4
 FROM
         BM_TBM_DT_CF_RP_FXDT_BAL A
         ,CM_TBM_BRN              B
 WHERE   A.MGMT_BRN_NO  =  B.BRN_NO
 AND     BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )
 AND  HDNBR_TP != '1'

 UNION ALL
    -- RP-CMA
 SELECT 7 SEQ ,
        NVL(SUM(CASE WHEN BASE_DT = :BASE_DT
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT1,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT2,
        NVL(SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT3,
        NVL(SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                     THEN BAL_AMT
                     ELSE 0
                     END),0) AMT4
 FROM
         BM_TBM_DT_CF_RP_FXDT_BAL A
 WHERE   BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )
 AND RP_BNS_TP = '5'

 UNION ALL
 -- 소매채권(매출)
 SELECT 8 SEQ,
       NVL(SUM(CASE WHEN SETT_DT = :BASE_DT
                    THEN SETT_QTY
                    ELSE 0
                    END),0) AMT1,
       NVL(SUM(CASE WHEN SETT_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                    THEN SETT_QTY
                    ELSE 0
                    END),0) AMT2,
       NVL(SUM(CASE WHEN SETT_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                    THEN SETT_QTY
                    ELSE 0
                    END),0) AMT3,
       NVL(SUM(CASE WHEN SETT_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                    THEN SETT_QTY
                    ELSE 0
                    END),0) AMT4
 FROM OF_TBL_SETT
 WHERE SETT_DT IN (
                   :BASE_DT, --당일
                   TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                   TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                   SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                   )
 AND   SETT_AFIR_CODE = '01'
 AND   EXEC_NO           = '0'
 AND   CANC_YN           = '0'
---
   )V1
   ,
   (--목표값 테이블에서 코드값별로 가져옴
 SELECT 1 SEQ
       ,SUM(MTH_TRGT_VAL) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('013','014')

 UNION ALL

 SELECT 4 SEQ
       ,SUM(MTH_TRGT_VAL) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('015','016')


 UNION ALL

 SELECT DECODE(UNDGL_CODE,'013',2,'014',3,'015',5,'016',6,'017',7,'018',8) SEQ
       ,SUM(MTH_TRGT_VAL) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('013','014','015','016','017','018')
 GROUP BY DECODE(UNDGL_CODE,'013',2,'014',3,'015',5,'016',6,'017',7,'018',8)
 )V2
WHERE V1.SEQ = V2.SEQ(+)


 

저작자표시 비영리 변경금지

'작업일지' 카테고리의 다른 글

20100705~20100707 Tibero Exp,Imp 작업  (0) 2015.09.14
고객수 현황(EIS개발)  (0) 2015.09.14
위탁약정현황(EIS개발)  (0) 2015.09.14
날짜 조회(EIS개발)  (0) 2015.09.14
자금현황조회(처리)  (0) 2015.09.14

'작업일지' Related Articles

티스토리툴바