위탁약정현황(EIS개발)

--DEFINE BASE_DT = '20090204'

--FetlBrnPrfm00
--위탁약정현황

SELECT /*+ MG$FetlBrnPrfm00  작성자 영업지원_황해훈 */
       V3.SEQ,
       NVL(V2.CNT,0)                    /*!type long  */ /*! 목표금액 */ AMT,
       NVL(V1.D_SALES_AMT,0)            /*!type long  */ /*! 당일금액 */ AMT1,
       NVL(V1.P_SASLES_AMT,0)           /*!type long  */ /*! 전일금액 */ AMT2,
       NVL(V1.M_SALES_AMT,0)            /*!type long  */ /*! 당월금액 */ AMT3,
       NVL(V1.MP_SALES_AMT,0)           /*!type long  */ /*! 전월금액 */ AMT4,
       NVL(V1.Y_SALES_AMT,0)            /*!type long  */ /*! 당년금액 */ AMT5,
       NVL(V1.YP_SALES_AMT,0)           /*!type long  */ /*! 전년금액 */ AMT6,
       NVL(DECODE(NVL(V2.CNT,0),0,0,NVL(V1.Y_SALES_AMT,0) / V2.CNT * 100),0)
                                        /*!type double */ /*! 진척도   */ PSES_RAT07
FROM (
 --주식 약정
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 1
             WHEN A.BRN_NO IN ('940')
             THEN 2
             WHEN A.BRN_NO IN ('999')
             THEN 3
             END SEQ,
        SUM(CASE WHEN A.BNS_DT  = /*!type char( 8) */ /*! 기준일 */ :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        D_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        P_SASLES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        M_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        MP_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        Y_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('10', '11', '12', '13')  /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 1
             WHEN A.BRN_NO IN ('940')
             THEN 2
             WHEN A.BRN_NO IN ('999')
             THEN 3
             END

 UNION ALL
 --주식 약정 합계
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 4
             END SEQ,
        SUM(CASE WHEN A.BNS_DT  = :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        D_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        P_SASLES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        M_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        MP_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        Y_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('10', '11', '12', '13')  /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 4
             END

 UNION ALL
 --선물 약정
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 5
             WHEN A.BRN_NO IN ('940')
             THEN 6
             WHEN A.BRN_NO IN ('999')
             THEN 7
             END SEQ,
        NVL(SUM(CASE WHEN A.BNS_DT  = :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        D_SALES_AMT,
        NVL(SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        P_SASLES_AMT,
        NVL(SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        M_SALES_AMT,
        NVL(SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        MP_SALES_AMT,
        NVL(SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        Y_SALES_AMT,
        NVL(SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END),0)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('20')                 /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 5
             WHEN A.BRN_NO IN ('940')
             THEN 6
             WHEN A.BRN_NO IN ('999')
             THEN 7
             END

 UNION ALL
 --선물 약정 합계
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 8
             END SEQ,
        SUM(CASE WHEN A.BNS_DT  = :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        D_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        P_SASLES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        M_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        MP_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        Y_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('20')                 /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 8
             END

 UNION ALL
 --옵션 약정
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 9
             WHEN A.BRN_NO IN ('940')
             THEN 10
             WHEN A.BRN_NO IN ('999')
             THEN 11
             END SEQ,
        SUM(CASE WHEN A.BNS_DT  = :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        D_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        P_SASLES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        M_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        MP_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        Y_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('30')  /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100')
             THEN 9
             WHEN A.BRN_NO IN ('940')
             THEN 10
             WHEN A.BRN_NO IN ('999')
             THEN 11
             END

 UNION ALL
 --옵션 약정 합계
 SELECT
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 12
             END SEQ,
        SUM(CASE WHEN A.BNS_DT  = :BASE_DT   --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        D_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD') --전일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        P_SASLES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(:BASE_DT,1,6) || '01'  --당월 시작일
                               AND :BASE_DT                      --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        M_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1),'YYYYMMDD'),1,6) || '01'            --전월시작일
                               AND TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')  --전월마지막일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        MP_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),'YYYYMMDD'),1,4)||'0101' --당년 시작일
                               AND :BASE_DT                                                                                           --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        Y_SALES_AMT,
        SUM(CASE WHEN A.BNS_DT BETWEEN SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'0101' --전년 시작일
                                   AND SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년 마지막일                                                                                        --기준일
                 THEN A.SALES_AMT
                 ELSE 0
                 END)        YP_SALES_AMT
 FROM SL_TBL_BRN_PRFM A,
      CM_TBM_BRN      B
 WHERE A.BRN_NO           = B.BRN_NO
   AND A.ACMGT_CODE       = '210'              /*!집계지점      */
   AND B.OP_TP NOT IN('1')
   AND A.REG_MKT_CODE IN('30')  /*!등록시장코드  */
   AND A.BRN_NO != '942'
 GROUP BY
        CASE WHEN A.BRN_NO IN ('001','002','003','004','100','940','999')
             THEN 12
             END
  )V1
  ,
   (--목표값 테이블에서 코드값별로 가져옴
 SELECT 4 SEQ
       ,NVL(SUM(MTH_TRGT_VAL),0) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('019','020','021')

 UNION ALL

 SELECT 8 SEQ
       ,NVL(SUM(MTH_TRGT_VAL),0) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('022','023','024')

 UNION ALL

 SELECT 12 SEQ
       ,NVL(SUM(MTH_TRGT_VAL),0) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('025','026','027')
 UNION ALL

 SELECT DECODE(UNDGL_CODE,'019',1,'020',2,'021',3,'022',5,'023',6,'024',7,'025',9,'026',10,'027',11) SEQ
       ,NVL(SUM(MTH_TRGT_VAL),0) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('019','020','021','022','023','024','025','026','027')
 GROUP BY DECODE(UNDGL_CODE,'019',1,'020',2,'021',3,'022',5,'023',6,'024',7,'025',9,'026',10,'027',11)
 )V2
 ,
 (
 SELECT 1 SEQ FROM DUAL UNION ALL
 SELECT 2 SEQ FROM DUAL UNION ALL
 SELECT 3 SEQ FROM DUAL UNION ALL
 SELECT 4 SEQ FROM DUAL UNION ALL
 SELECT 5 SEQ FROM DUAL UNION ALL
 SELECT 6 SEQ FROM DUAL UNION ALL
 SELECT 7 SEQ FROM DUAL UNION ALL
 SELECT 8 SEQ FROM DUAL UNION ALL
 SELECT 9 SEQ FROM DUAL UNION ALL
 SELECT 10 SEQ FROM DUAL UNION ALL
 SELECT 11 SEQ FROM DUAL UNION ALL
 SELECT 12 SEQ FROM DUAL
 )V3
WHERE V3.SEQ = V1.SEQ(+)
AND   V3.SEQ = V2.SEQ(+)
ORDER BY V3.SEQ