보기도 머리가 아픈데 튜닝이 가능할까요?
kwindow2 1학년

답변 채택시 : 0코아

답변 0 추천 0 조회 1248 2016.11.10


SELECT A.ORGCD ,
       A.INSTCD ,
       A.GOODCD ,
       MAX(A.ACNTCD) AS ACNTCD ,
       MAX((SELECT LARGE_GROUP_NM
                FROM   MIS.RSCMGBNL
                WHERE  ORGCD = A.ORGCD
                AND    ACNTCD = A.ACNTCD)) AS ACNTNM ,
       MAX((SELECT DEPTHNGNM
                FROM   COM.ZSDDDEPT
                WHERE  INSTCD = A.INSTCD
                AND    DEPTCD = 'PMR')) AS DEPTHNGNM ,
       'PMR' AS DEPTCD ,
       MAX(A.GOODHNGNM) AS GOODHNGNM ,
       MAX(A.GOODSPEC) AS GOODSPEC ,
       MAX(A.PURCUNIT) AS PURCUNIT ,
       MAX(A.UNT) AS UNT ,
       MAX(NVL(A.MINEXCH, 0)) AS MINEXCH ,
       MAX(NVL(A.CASEPRC, 0)) AS CASEPRC ,
       MAX(ROUND(NVL(A.CASEPRC, 0)/NVL(A.MINEXCH, 1), 2)) AS UNTDANGA ,
       'I' AS TYPE ,
       NVL(SUM(TOTDRUGQTY), 0) AS CHUNGGUQTY ,
       NVL(SUM(TOTDRUGQTY), 0) AS JIBGYEQTY ,
       '12' AS CHUNGGUGUBUN ,
       '4' AS CHUNGGUPROCESS ,
       MAX((SELECT CUSTNM
                FROM   MIS.RSCMCUST
                WHERE  ORGCD = A.ORGCD
                AND    CUSTCODE = A.SUPPCUSTCD)) AS SUPPCUSTCD ,
       MAX((SELECT CUSTNM
                FROM   MIS.RSCMCUST
                WHERE  ORGCD = A.ORGCD
                AND    CUSTCODE = A.CUSTOMERCODE2)) AS CUSTOMERCODE2 ,
       MAX((SELECT CUSTNM
                FROM   MIS.RSCMCUST
                WHERE  ORGCD = A.ORGCD
                AND    CUSTCODE = A.PRODCMPYCD)) AS PRODCMPYCD ,
       MAX((SELECT CDNM
                FROM   COM.ZBCMCODE
                WHERE  CDGRUPID = 'R2060'
                AND    CDID = NVL(A.SUTAKYN, 'N'))) AS SUTAKYNNM ,
       MAX(A.EDICD) AS EDICD ,
       MAX(A.UNT||'/'||A.ORDEREXCH) AS UNTEXCH ,
       MAX(A.PURCUNIT||'/'||A.MINEXCH) AS PUREXCH ,
       'req' AS REFCOND ,
       COUNT(A.GOODCD) OVER() CNT ,
       D.IOFLAG
FROM   MIS.RSCHDGOD A ,
       (SELECT K.INSTCD,
               K.DRUGCD,
               K.DRUGKIND2,
               K.HOSINFLAG,
               K.DRUGENDDD
        FROM   AST.ADBMDRUG K
        WHERE  K.INSTCD IN ('052',
                       '053',
                       '054',
                       '055')
        AND    K.DRUGTODD = (SELECT MAX(J.DRUGTODD)
                FROM   AST.ADBMDRUG J
                WHERE  J.INSTCD = K.INSTCD
                AND    J.DRUGCD = K.DRUGCD) ) C ,
       (SELECT HDRUG.PRCPCD ,
               HDRUG.PRCPDD ,
               HDRUG.PID ,
               HDRUG.PRCPNO ,
                       CASE
                         WHEN TPNYN = 'Y'
        OR     IOFLAG = 'O' THEN SUM(HDRUG.TOTDRUGQTY)
                         ELSE MAX(HDRUG.TOTDRUGQTY)
                       END TOTDRUGQTY ,
               HDRUG.INSTCD ,
               HDRUG.DRUGNO ,
               HDRUG.IOFLAG
        FROM   (SELECT HDRUG.PRCPCD ,
                       HDRUG.PID ,
                                       CASE
                                         WHEN CEIL(HDRUG.TOTDRUGQTY) -
HDRUG.TOTDRUGQTY < 0.5 THEN CEIL(HDRUG.TOTDRUGQTY)
                                         WHEN CEIL(HDRUG.TOTDRUGQTY) -
HDRUG.TOTDRUGQTY = 0.5 THEN HDRUG.TOTDRUGQTY
                                         ELSE TRUNC(HDRUG.TOTDRUGQTY) + 0.5
                                       END TOTDRUGQTY ,
                       HDRUG.INSTCD ,
                       HDRUG.DRUGNO ,
                       NVL(DECODE(HDRUG.IOFLAG, 'I', HDRUG.DRUGDD, HDRUG.PRCPDD)
, HDRUG.PRCPDD) PRCPDD ,
                                       CASE
                                         WHEN (SELECT COUNT(DISTINCT PRCPDD)
                        FROM   AST.ADTHDRUG HD
                        WHERE  HD.INSTCD = HDRUG.INSTCD
                        AND    HD.DRUGDD = HDRUG.DRUGDD
                        AND    HD.DRUGNO = HDRUG.DRUGNO
                        AND    HD.IOFLAG = HDRUG.IOFLAG
                        AND    HD.PRCPCD = HDRUG.PRCPCD
                        AND    HD.LBLRP = HDRUG.LBLRP ) > 1 THEN TO_NUMBER(
DDRUG.SUMDT||HDRUG.DRUGNO)
                                         ELSE HDRUG.PRCPNO
                                       END PRCPNO ,
                       'N' TPNYN ,
                       HDRUG.IOFLAG
                FROM   AST.ADTHDRUG HDRUG ,
                       AST.ADTDDRUG DDRUG
                WHERE  DDRUG.SUMDT BETWEEN :B5 || :B4 AND :B3 || :B2
                AND    HDRUG.INSTCD = :B1
                AND    HDRUG.PRCPHISTCD ='O'
                AND    HDRUG.INSTCD = DDRUG.INSTCD
                AND    HDRUG.DRUGDD = DDRUG.DRUGDD
                AND    HDRUG.IOFLAG = DDRUG.IOFLAG
                AND    HDRUG.DRUGNO = DDRUG.DRUGNO
                AND    HDRUG.HOSINHOSOUTFLAG = 'I'
                AND    HDRUG.DRUGSTAT NOT IN ( 'NP',
                               'X')
                AND    HDRUG.NODELIVEYN != 'Y'
                AND    HDRUG.RTNFLAG != 'D'
                AND    (DDRUG.ENDFLAG = '80'
                        OR     NOT EXISTS (SELECT 1
                                FROM   AST.ADTHDRUGSUM INJ
                                WHERE  INJ.INSTCD = HDRUG.INSTCD
                                AND    INJ.PID = HDRUG.PID
                                AND    INJ.PRCPCD = HDRUG.PRCPCD
                                AND    INJ.PRCPDD = HDRUG.PRCPDD
                                AND    INJ.PRCPNO = HDRUG.PRCPNO) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODMRSM Y
                        WHERE  Y.INSTCD = HDRUG.INSTCD
                        AND    Y.PID = HDRUG.PID
                        AND    Y.ORDDD = HDRUG.ORDDD
                        AND    Y.PRCPDD = HDRUG.PRCPDD
                        AND    Y.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                                FROM   EMR.MMODMRSM X
                                WHERE  X.INSTCD = Y.INSTCD
                                AND    X.PID = Y.PID
                                AND    X.ORDDD = Y.ORDDD
                                AND    X.PRCPDD = Y.PRCPDD
                                AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                                AND    X.PRCPFLAG ='D' ) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODEXIP IP ,
                               EMR.MMOHIPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                        UNION ALL
SELECT 1
                        FROM   EMR.MMODEXOP IP,
                               EMR.MMOHOPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
                AND    HDRUG.STOCDT = '-'
                AND    EXISTS (SELECT 1
                        FROM   AST.ADBMDRUG
                        WHERE  INSTCD = HDRUG.INSTCD
                        AND    DRUGCD = HDRUG.PRCPCD
                        AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                                       '26',
                                       '27')
                        AND    HDRUG.DRUGDD BETWEEN DRUGFROMDD AND DRUGTODD)
                UNION ALL
SELECT ZZ.ITEMCD PRCPCD ,
                       MAX(HDRUG.PID) PID ,
                       CEIL(SUM(ZZ.QTY)) TOTDRUGQTY ,
                       MAX(HDRUG.INSTCD) INSTCD ,
                       MAX(HDRUG.DRUGNO) DRUGNO ,
                       MAX(HDRUG.DRUGDD) PRCPDD ,
                       MAX(HDRUG.PRCPNO) PRCPNO ,
                       'Y' TPNYN ,
                       'I' IOFLAG
                FROM   AST.ADTHDRUG HDRUG ,
                       AST.ADTDDRUG DDRUG ,
                       (SELECT DISTINCT YY.DRUGCD ,
                               YY.ITEMCD ,
                               YY.ITEMVOL ,
                               YY.INSTCD ,
                               MD2.UNITSEQNO ,
                               YY.PRCPDD ,
                               YY.PID ,
                               ROUND(TO_NUMBER(YY.ITEMVOL) / TO_NUMBER( (SELECT
X.VOL
                                                FROM   AST.ADBDUNIT X
                                                WHERE  X.UNITFLAG = 'S'
                                                AND    X.INSTCD = MD2.INSTCD
                                                AND    X.DRUGCD = MD2.DRUGCD
                                                AND    X.MSTYN = 'Y'
                                                AND    X.UNITSEQNO =
MD2.UNITSEQNO
                                                AND    ROWNUM = 1 )), 3) QTY
                        FROM   (SELECT DISTINCT A.INSTCD ,
                                       A.VOLCD DRUGCD ,
                                       DECODE(LV, 1, DEXDRUGCD , 2, AMINODRUGCD
, 3, NADRUGCD , 4, KDRUGCD , 5, CADRUGCD , 6, PDRUGCD , 7, MGDRUGCD , 8,
HEPARINDRUGCD , 9, MVDRUGCD , 10, RACICROLCD , 11, ZNCD , 12, LIPIDCD , 13,
AQUACD , 14, FURTMANCD ) ITEMCD ,
                                       DECODE(LV, 1, REALCALCGIRVOL , 2,
REALCALCAMINOVOL , 3, REALCALCNAVOL , 4, REALCALCKVOL , 5, REALCALCCAVOL , 6,
REALCALCPVOL , 7, REALCALCMGVOL , 8, REALCALCHEPARINVOL , 9, REALCALCMVVOL , 10,
 REALCALCRACICROLVOL , 11, REALCALCZNVOL , 12, CALCLIPIDVOL , 13, REALCALCAQUA ,
 14, REALCALCFURTMANVOL ) ITEMVOL ,
                                       A.TPNNO ,
                                       A.PID ,
                                       A.PRCPDD
                                FROM   AST.ADBHTPNPRC A ,
                                       (SELECT LEVEL LV
                                        FROM   DUAL CONNECT BY LEVEL <= 14)
                                WHERE  A.INSTCD = :B1 ) YY ,
                               AST.ADBMDRUG MD2 ,
                               EMR.MMOHIPRC ORD
                        WHERE  YY.ITEMCD IS NOT NULL
                        AND    YY.INSTCD = MD2.INSTCD
                        AND    YY.ITEMCD = MD2.DRUGCD
                        AND    MD2.STNDVOL IS NOT NULL
                        AND    ORD.INSTCD = YY.INSTCD
                        AND    ORD.PID = YY.PID
                        AND    ORD.PRCPDD = YY.PRCPDD
                        AND    ORD.TESTREQLNKNO = YY.TPNNO
                        AND    ORD.PRCPHISTCD = 'O'
                        AND    ITEMVOL > 0 ) ZZ
                WHERE  DDRUG.PRNTDT BETWEEN :B5 || :B4 AND :B3 || :B2
                AND    HDRUG.INSTCD = :B1
                AND    HDRUG.PRCPHISTCD = 'O'
                AND    HDRUG.INSTCD = DDRUG.INSTCD
                AND    HDRUG.DRUGDD = DDRUG.DRUGDD
                AND    HDRUG.IOFLAG = DDRUG.IOFLAG
                AND    HDRUG.DRUGNO = DDRUG.DRUGNO
                AND    HDRUG.HOSINHOSOUTFLAG = 'I'
                AND    HDRUG.DRUGSTAT NOT IN ( 'NP',
                               'X')
                AND    HDRUG.RTNFLAG != 'D'
                AND    HDRUG.NODELIVEYN != 'Y'
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODMRSM Y
                        WHERE  Y.INSTCD = HDRUG.INSTCD
                        AND    Y.PID = HDRUG.PID
                        AND    Y.ORDDD = HDRUG.ORDDD
                        AND    Y.PRCPDD = HDRUG.PRCPDD
                        AND    Y.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                                FROM   EMR.MMODMRSM X
                                WHERE  X.INSTCD = Y.INSTCD
                                AND    X.PID = Y.PID
                                AND    X.ORDDD = Y.ORDDD
                                AND    X.PRCPDD = Y.PRCPDD
                                AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                                AND    X.PRCPFLAG ='D' ) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODEXIP IP ,
                               EMR.MMOHIPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                        UNION ALL
SELECT 1
                        FROM   EMR.MMODEXOP IP ,
                               EMR.MMOHOPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
                AND    HDRUG.STOCDT = '-'
                AND    EXISTS (SELECT 1
                        FROM   AST.ADBMDRUG
                        WHERE  INSTCD = HDRUG.INSTCD
                        AND    DRUGCD = HDRUG.PRCPCD
                        AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                                       '26',
                                       '27')
                        AND    HDRUG.DRUGDD BETWEEN DRUGFROMDD AND DRUGTODD)
                AND    HDRUG.PRCPCD = ZZ.DRUGCD
                AND    HDRUG.PRCPDD = ZZ.PRCPDD
                AND    HDRUG.PID = ZZ.PID
                GROUP BY ITEMCD
                UNION ALL
SELECT ZZ.CMCDNM PRCPCD ,
                       MAX(HDRUG.PID) PID ,
                       CEIL(SUM(ZZ.ITEMVOL) / MAX(ZZ.STNDVOL)) TOTDRUGQTY ,
                       MAX(HDRUG.INSTCD) INSTCD ,
                       MAX(HDRUG.DRUGNO) DRUGNO ,
                       MAX(HDRUG.DRUGDD) PRCPDD ,
                       MAX(HDRUG.PRCPNO) PRCPNO ,
                       'Y' TPNYN ,
                       MIN(HDRUG.IOFLAG)
                FROM   AST.ADTHDRUG HDRUG ,
                       AST.ADTDDRUG DDRUG ,
                       (SELECT DISTINCT CMCDNM ,
                               YY.DRUGCD ,
                               YY.ITEMVOL ,
                               MD2.STNDVOL
                        FROM   (SELECT A.INSTCD ,
                                       A.DRUGCD ,
                                       LV ,
                                       DECODE(LV, 1, 'B020' , 2, 'B021' , 3,
'B022' , 4, 'B023' , 5, 'B024' , 6, 'B025' , 7, 'B026' , 8, 'B101' , 9, 'B102' ,
 10, 'B106' , 11, 'B105' , 12, 'B103' , 13, 'B108' , 14, 'B107') ITEMFLAG ,
                                       DECODE(LV, 1, TPNCD , 2, DEXCD , 3,
AMINOCD , 4, LIPIDCD , 5, MULTIVITACD , 6, HEPARINCD , 7, DISTILLCD , 8,
TRACEELECD1 , 9, TRACEELECD2 , 10, TRACEELECD3 , 11, TRACEELECD4 , 12,
TRACEELECD5 , 13, TRACEELECD6 , 14, TRACEELECD7) ITEMCD ,
                                       DECODE(LV, 1, TPNVOL , 2, DEXVOL1 , 3,
AMINOVOL1 , 4, LIPIDVOL1 , 5, MULTIVITAVOL , 6, HEPARINVOL1 , 7, DISTILLVOL , 8,
 TRACEELEVOL1 , 9, TRACEELEVOL2 , 10, TRACEELEVOL3 , 11, TRACEELEVOL4 , 12,
TRACEELEVOL5 , 13, TRACEELEVOL6 , 14, TRACEELEVOL7) ITEMVOL
                                FROM   AST.ADBMTPNCD A ,
                                       (SELECT LEVEL LV
                                        FROM   DUAL CONNECT BY LEVEL <= 14)
                                WHERE  A.INSTCD = :B1
                                AND    A.TPNKIND = 'A' ) YY ,
                               AST.ADBDCMCD CD2 ,
                               AST.ADBMDRUG MD2
                        WHERE  YY.ITEMCD IS NOT NULL
                        AND    YY.INSTCD = CD2.INSTCD
                        AND    YY.ITEMFLAG = CD2.CDTYPE
                        AND    YY.ITEMCD = CD2.CMCD
                        AND    CD2.INSTCD = MD2.INSTCD
                        AND    CD2.CMCDNM = MD2.DRUGCD
                        AND    MD2.STNDVOL IS NOT NULL ) ZZ
                WHERE  DDRUG.PRNTDT BETWEEN :B5 || :B4 AND :B3 || :B2
                AND    HDRUG.INSTCD = :B1
                AND    HDRUG.PRCPHISTCD ='O'
                AND    HDRUG.INSTCD = DDRUG.INSTCD
                AND    HDRUG.DRUGDD = DDRUG.DRUGDD
                AND    HDRUG.IOFLAG = DDRUG.IOFLAG
                AND    HDRUG.DRUGNO = DDRUG.DRUGNO
                AND    HDRUG.HOSINHOSOUTFLAG = 'I'
                AND    HDRUG.DRUGSTAT NOT IN ( 'NP',
                               'X')
                AND    HDRUG.RTNFLAG != 'D'
                AND    HDRUG.NODELIVEYN != 'Y'
                AND    HDRUG.PRCPCD IN (SELECT DISTINCT DRUGCD
                        FROM   AST.ADBMDRUG M
                        WHERE  M.INSTCD = HDRUG.INSTCD
                        AND    M.USEHOSP LIKE '%' || :B1 || '%'
                        AND    SEQNO != '99'
                        AND    DRUGFROMDD != '00000000'
                        AND    DRUGKIND2 = '05')
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODMRSM Y
                        WHERE  Y.INSTCD = HDRUG.INSTCD
                        AND    Y.PID = HDRUG.PID
                        AND    Y.ORDDD = HDRUG.ORDDD
                        AND    Y.PRCPDD = HDRUG.PRCPDD
                        AND    Y.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                                FROM   EMR.MMODMRSM X
                                WHERE  X.INSTCD = Y.INSTCD
                                AND    X.PID = Y.PID
                                AND    X.ORDDD = Y.ORDDD
                                AND    X.PRCPDD = Y.PRCPDD
                                AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                                AND    X.PRCPFLAG ='D' ) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODEXIP IP ,
                               EMR.MMOHIPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                        UNION ALL
SELECT 1
                        FROM   EMR.MMODEXOP IP ,
                               EMR.MMOHOPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
                AND    HDRUG.STOCDT = '-'
                AND    EXISTS (SELECT 1
                        FROM   AST.ADBMDRUG
                        WHERE  INSTCD = HDRUG.INSTCD
                        AND    DRUGCD = HDRUG.PRCPCD
                        AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                                       '26',
                                       '27')
                        AND    HDRUG.DRUGDD BETWEEN DRUGFROMDD AND DRUGTODD)
                AND    HDRUG.PRCPCD = ZZ.DRUGCD
                GROUP BY CMCDNM ) HDRUG
        GROUP BY HDRUG.INSTCD, HDRUG.PRCPCD, HDRUG.PRCPDD, HDRUG.PID,
HDRUG.PRCPNO, HDRUG.DRUGNO, HDRUG.TPNYN, HDRUG.IOFLAG
        UNION ALL
SELECT HDRUG.PRCPCD ,
               HDRUG.PRCPDD ,
               HDRUG.PID ,
               HDRUG.PRCPNO ,
               -1 * MAX(HDRUG.TOTDRUGQTY) TOTDRUGQTY ,
               HDRUG.INSTCD ,
               HDRUG.DRUGNO ,
               HDRUG.IOFLAG
        FROM   (SELECT HDRUG.PRCPCD ,
                       HDRUG.PID ,
                                       CASE
                                         WHEN HDRUG.IOFLAG = 'I' THEN
                                         CASE
                                           WHEN HDRUG.DRUGKIND = 'D' THEN
                                           CASE
                                             WHEN CEIL(HDRUG.TOTDRUGQTY) -
HDRUG.TOTDRUGQTY < 0.5 THEN CEIL(HDRUG.TOTDRUGQTY)
                                             WHEN CEIL(HDRUG.TOTDRUGQTY) -
HDRUG.TOTDRUGQTY = 0.5 THEN HDRUG.TOTDRUGQTY
                                             ELSE TRUNC(HDRUG.TOTDRUGQTY) + 0.5
                                           END
                                           ELSE TRUNC(HDRUG.TOTDRUGQTY)
                                         END
                                         ELSE (SELECT
                                                       CASE
                                                         WHEN CEIL(
AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD, TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1,
1, C.PRCPQTYUNITFLAG, C.DRPRCPETC3, C.PRCPDD) ) - AST.FN_DRUGTOTQTY(C.INSTCD,
C.PRCPCD, TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1, 1, C.PRCPQTYUNITFLAG,
C.DRPRCPETC3, C.PRCPDD) < 0.5 THEN CEIL( AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD,
TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1, 1, C.PRCPQTYUNITFLAG, C.DRPRCPETC3,
C.PRCPDD) )
                                                         WHEN CEIL(
AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD, TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1,
1, C.PRCPQTYUNITFLAG, C.DRPRCPETC3, C.PRCPDD) ) - AST.FN_DRUGTOTQTY(C.INSTCD,
C.PRCPCD, TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1, 1, C.PRCPQTYUNITFLAG,
C.DRPRCPETC3, C.PRCPDD) = 0.5 THEN AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD,
TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1, 1, C.PRCPQTYUNITFLAG, C.DRPRCPETC3,
C.PRCPDD)
                                                         ELSE TRUNC(
AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD, TO_NUMBER(C.REHBPRCPCUREPARTCD), '*', 1,
1, C.PRCPQTYUNITFLAG, C.DRPRCPETC3, C.PRCPDD)) + 0.5
                                                       END
                        FROM   EMR.MMOHOPRC C
                        WHERE  HDRUG.INSTCD = C.INSTCD
                        AND    HDRUG.PRCPDD = C.PRCPDD
                        AND    HDRUG.PRCPNO = C.PRCPNO
                        AND    HDRUG.PID = C.PID
                        AND    C.PRCPHISTCD = 'D')
                                       END TOTDRUGQTY ,
                       HDRUG.INSTCD ,
                       HDRUG.DRUGNO ,
                       SUBSTR(HDRUG.RTNDT, 0, 8) PRCPDD ,
                                       CASE
                                         WHEN (SELECT COUNT(DISTINCT PRCPDD)
                        FROM   AST.ADTHDRUG HD
                        WHERE  HD.INSTCD = HDRUG.INSTCD
                        AND    HD.DRUGDD = HDRUG.DRUGDD
                        AND    HD.DRUGNO = HDRUG.DRUGNO
                        AND    HD.IOFLAG = HDRUG.IOFLAG
                        AND    HD.PRCPCD = HDRUG.PRCPCD
                        AND    HD.LBLRP = HDRUG.LBLRP) > 1 THEN TO_NUMBER(
DDRUG.SUMDT||HDRUG.DRUGNO)
                                         ELSE HDRUG.PRCPNO
                                       END PRCPNO ,
                       HDRUG.IOFLAG
                FROM   AST.ADTHDRUG HDRUG ,
                       AST.ADTDDRUG DDRUG
                WHERE  HDRUG.RTNDT BETWEEN :B5 || :B4 AND :B3 || :B2
                AND    HDRUG.INSTCD = :B1
                AND    HDRUG.PRCPHISTCD ='O'
                AND    HDRUG.INSTCD = DDRUG.INSTCD
                AND    HDRUG.DRUGDD = DDRUG.DRUGDD
                AND    HDRUG.IOFLAG = DDRUG.IOFLAG
                AND    HDRUG.DRUGNO = DDRUG.DRUGNO
                AND    HDRUG.HOSINHOSOUTFLAG = 'I'
                AND    HDRUG.DRUGSTAT NOT IN ( 'NP',
                               'X')
                AND    HDRUG.NODELIVEYN != 'Y'
                AND    HDRUG.RTNFLAG = 'D'
                AND    HDRUG.RTNID NOT IN (SELECT CMCDNM
                        FROM   AST.ADBDCMCD
                        WHERE  INSTCD = :B1
                        AND    CDTYPE = 'D008')
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODMRSM Y
                        WHERE  Y.INSTCD = HDRUG.INSTCD
                        AND    Y.PID = HDRUG.PID
                        AND    Y.ORDDD = HDRUG.ORDDD
                        AND    Y.PRCPDD = HDRUG.PRCPDD
                        AND    Y.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                                FROM   EMR.MMODMRSM X
                                WHERE  X.INSTCD = Y.INSTCD
                                AND    X.PID = Y.PID
                                AND    X.ORDDD = Y.ORDDD
                                AND    X.PRCPDD = Y.PRCPDD
                                AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                                AND    X.PRCPFLAG ='D' ) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODEXIP IP ,
                               EMR.MMOHIPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                        UNION ALL
SELECT 1
                        FROM   EMR.MMODEXOP IP ,
                               EMR.MMOHOPRC RC
                        WHERE  IP.INSTCD = HDRUG.INSTCD
                        AND    IP.PRCPDD = HDRUG.PRCPDD
                        AND    IP.PRCPNO = HDRUG.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = HDRUG.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
                AND    HDRUG.STOCDT = '-'
                AND    EXISTS (SELECT 1
                        FROM   AST.ADBMDRUG
                        WHERE  INSTCD = HDRUG.INSTCD
                        AND    DRUGCD = HDRUG.PRCPCD
                        AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                                       '26',
                                       '27')
                        AND    HDRUG.DRUGDD BETWEEN DRUGFROMDD AND DRUGTODD)
                UNION ALL
SELECT A.CALCSCORCD PRCPCD ,
                       A.PID ,
                       AST.FN_DRUGTOTQTY(C.INSTCD, C.PRCPCD, TO_NUMBER(
C.DRPRCPETC4), C.DRPRCPETC7, TO_NUMBER(C.DRPRCPETC8), TO_NUMBER(C.PRCPDAYNO),
C.PRCPQTYUNITFLAG, C.DRPRCPETC3, C.PRCPDD) TOTDRUGQTY ,
                       A.INSTCD,
                       A.DRUGNO,
                       A.RTNDD PRCPDD,
                       A.PRCPNO,
                       'O' IOFLAG
                FROM   EMR.MMODDRRT A ,
                       EMR.MMODEXOP B ,
                       EMR.MMOHOPRC C
                WHERE  A.INSTCD = :B1
                AND    A.RTNDD || A.RTNTM BETWEEN :B5 || :B4 AND :B3 || :B2
                AND    B.EXPRCPETC6 != '-'
                AND    B.EXPRCPETC6 !='00000000'
                AND    A.INSTCD = B.INSTCD
                AND    A.PRCPDD = B.PRCPDD
                AND    A.EXECPRCPUNIQNO = B.EXECPRCPUNIQNO
                AND    B.INSTCD = C.INSTCD
                AND    B.PRCPDD = C.PRCPDD
                AND    B.PRCPNO = C.PRCPNO
                AND    B.PRCPHISTNO = C.PRCPHISTNO
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODMRSM Y
                        WHERE  Y.INSTCD = B.INSTCD
                        AND    Y.PID = B.PID
                        AND    Y.ORDDD = B.ORDDD
                        AND    Y.PRCPDD = B.PRCPDD
                        AND    Y.EXECPRCPUNIQNO = B.EXECPRCPUNIQNO
                        AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                                FROM   EMR.MMODMRSM X
                                WHERE  X.INSTCD = Y.INSTCD
                                AND    X.PID = Y.PID
                                AND    X.ORDDD = Y.ORDDD
                                AND    X.PRCPDD = Y.PRCPDD
                                AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                                AND    X.PRCPFLAG ='D' ) )
                AND    NOT EXISTS (SELECT 1
                        FROM   EMR.MMODEXIP IP ,
                               EMR.MMOHIPRC RC
                        WHERE  IP.INSTCD = B.INSTCD
                        AND    IP.PRCPDD = B.PRCPDD
                        AND    IP.PRCPNO = B.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = B.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                        UNION ALL
SELECT 1
                        FROM   EMR.MMODEXOP IP ,
                               EMR.MMOHOPRC RC
                        WHERE  IP.INSTCD = B.INSTCD
                        AND    IP.PRCPDD = B.PRCPDD
                        AND    IP.PRCPNO = B.PRCPNO
                        AND    IP.EXECPRCPUNIQNO = B.EXECPRCPUNIQNO
                        AND    RC.STOCYN = 'Y'
                        AND    IP.INSTCD = RC.INSTCD
                        AND    IP.PRCPDD = RC.PRCPDD
                        AND    IP.PRCPNO = RC.PRCPNO
                        AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
                AND    EXISTS (SELECT 1
                        FROM   AST.ADBMDRUG
                        WHERE  INSTCD = C.INSTCD
                        AND    DRUGCD = C.PRCPCD
                        AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                                       '26',
                                       '27')
                        AND    C.PRCPDD BETWEEN DRUGFROMDD AND DRUGTODD) )
HDRUG
        GROUP BY HDRUG.INSTCD, HDRUG.PRCPCD, HDRUG.PRCPDD, HDRUG.PID,
HDRUG.PRCPNO, HDRUG.DRUGNO, HDRUG.IOFLAG
        UNION ALL
SELECT DISTINCT B.PRCPCD ,
               SUBSTR(B.RTNCNFMDT, 1, 8) PRCPDD ,
               B.PID ,
               A.PRCPNO ,
               -1 *
                       CASE
                         WHEN F.DELIVECRITE = '01'
        AND    CEIL(B.RTNQTY) - B.RTNQTY < 0.5 THEN CEIL(B.RTNQTY)
                         WHEN F.DELIVECRITE = '01'
        AND    CEIL(B.RTNQTY) - B.RTNQTY = 0.5 THEN B.RTNQTY
                         WHEN F.DELIVECRITE = '01' THEN TRUNC(B.RTNQTY) + 0.5
                         ELSE B.RTNQTY
                       END TOTDRUGQTY ,
               B.INSTCD ,
               B.DRUGNO ,
               B.IOFLAG
        FROM   AST.ADTHDRUG A ,
               AST.ADTHNARC B ,
               AST.ADBMDRUG F ,
               (SELECT A.INSTCD ,
                       A.DRUGCD ,
                       MAX(DECODE(A.UNITFLAG, 'C', A.VOL)) CVOL ,
                       MAX(DECODE(A.UNITFLAG, 'C', CMCDNM)) CUNIT ,
                       MAX(DECODE(A.UNITFLAG, 'S', A.VOL)) SVOL ,
                       MAX(DECODE(A.UNITFLAG, 'S', CMCDNM)) SUNIT ,
                       MAX(DECODE(A.UNITFLAG, 'P', A.VOL)) PVOL ,
                       MAX(DECODE(A.UNITFLAG, 'P', CMCDNM)) PUNIT
                FROM   AST.ADBDUNIT A ,
                       (SELECT INSTCD,
                               'C' UNITFLAG,
                               CMCD,
                               CMCDNM
                        FROM   AST.ADBDCMCD A
                        WHERE  CDTYPE ='B001'
                        UNION ALL
SELECT INSTCD,
                               'P' UNITFLAG,
                               CMCD,
                               CMCDNM
                        FROM   AST.ADBDCMCD A
                        WHERE  CDTYPE ='B002'
                        UNION ALL
SELECT INSTCD,
                               'S' UNITFLAG,
                               CMCD,
                               CMCDNM
                        FROM   AST.ADBDCMCD A
                        WHERE  CDTYPE ='B003' )B
                WHERE  A.INSTCD = B.INSTCD
                AND    A.UNITFLAG = B.UNITFLAG
                AND    A.UNIT = B.CMCD
                AND    A.INSTCD = :B1
                AND    A.UNITSEQNO = (SELECT MAX(X.UNITSEQNO)
                        FROM   AST.ADBDUNIT X
                        WHERE  X.INSTCD = A.INSTCD
                        AND    X.DRUGCD = A.DRUGCD)
                GROUP BY A.INSTCD , A.DRUGCD )G
        WHERE  A.INSTCD = :B1
        AND    B.RTNCNFMDT BETWEEN :B5 || :B4 AND :B3 || :B2
        AND    CNCLSTAT = '2'
        AND    B.INSTCD = A.INSTCD
        AND    B.DRUGDD = A.DRUGDD
        AND    B.DRUGNO = A.DRUGNO
        AND    B.IOFLAG = A.IOFLAG
        AND    B.DRUGSEQNO = A.DRUGSEQNO
        AND    F.INSTCD = A.INSTCD
        AND    F.DRUGCD = A.PRCPCD
        AND    A.PRCPDD BETWEEN F.DRUGFROMDD AND F.DRUGTODD
        AND    A.STOCDT = '-'
        AND    F.SEQNO != '99'
        AND    A.NODELIVEYN = '-'
        AND    NVL(F.DRUGKIND2, '-') IN ('26',
                       '27')
        AND    HOSINFLAG != 'O'
        AND    NOT EXISTS (SELECT 1
                FROM   EMR.MMODMRSM Y
                WHERE  Y.INSTCD = A.INSTCD
                AND    Y.PID = A.PID
                AND    Y.ORDDD = A.ORDDD
                AND    Y.PRCPDD = A.PRCPDD
                AND    Y.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                        FROM   EMR.MMODMRSM X
                        WHERE  X.INSTCD = Y.INSTCD
                        AND    X.PID = Y.PID
                        AND    X.ORDDD = Y.ORDDD
                        AND    X.PRCPDD = Y.PRCPDD
                        AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                        AND    X.PRCPDD >= '20150501'
                        AND    X.PRCPFLAG ='D' ) )
        AND    NOT EXISTS (SELECT 1
                FROM   EMR.MMODEXIP IP ,
                       EMR.MMOHIPRC RC
                WHERE  IP.INSTCD = A.INSTCD
                AND    IP.PRCPDD = A.PRCPDD
                AND    IP.PRCPNO = A.PRCPNO
                AND    IP.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    (RC.STOCYN = 'Y'
                        OR     RC.PRCPHISTCD = 'L')
                AND    IP.INSTCD = RC.INSTCD
                AND    IP.PRCPDD = RC.PRCPDD
                AND    IP.PRCPNO = RC.PRCPNO
                AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                UNION ALL
SELECT 1
                FROM   EMR.MMODEXOP IP ,
                       EMR.MMOHOPRC RC
                WHERE  IP.INSTCD = A.INSTCD
                AND    IP.PRCPDD = A.PRCPDD
                AND    IP.PRCPNO = A.PRCPNO
                AND    IP.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    (RC.STOCYN = 'Y'
                        OR     RC.PRCPHISTCD = 'L')
                AND    IP.INSTCD = RC.INSTCD
                AND    IP.PRCPDD = RC.PRCPDD
                AND    IP.PRCPNO = RC.PRCPNO
                AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
        AND    G.INSTCD(+) = A.INSTCD
        AND    G.DRUGCD(+) = A.PRCPCD
        UNION ALL
SELECT DISTINCT B.PRCPCD ,
               SUBSTR(B.DRUGDT, 1, 8) PRCPDD ,
               B.PID ,
               A.PRCPNO ,
                       CASE
                         WHEN F.DELIVECRITE = '01'
        AND    CEIL(B.DELIVEQTY) - B.DELIVEQTY < 0.5 THEN CEIL(B.DELIVEQTY)
                         WHEN F.DELIVECRITE = '01'
        AND    CEIL(B.DELIVEQTY) - B.DELIVEQTY = 0.5 THEN B.DELIVEQTY
                         WHEN F.DELIVECRITE = '01' THEN TRUNC(B.DELIVEQTY) +
0.5
                         ELSE B.DELIVEQTY
                       END TOTDRUGQTY ,
               B.INSTCD ,
               B.DRUGNO ,
               A.IOFLAG
        FROM   AST.ADTHDRUG A ,
               AST.ADTHNARC B ,
               AST.ADBMDRUG F
        WHERE  B.INSTCD = :B1
        AND    B.DRUGDT BETWEEN :B5 || :B4 AND :B3 || :B2
        AND    NARCSTAT IN ('5',
                       '6')
        AND    B.INSTCD = A.INSTCD
        AND    B.DRUGDD = A.DRUGDD
        AND    B.DRUGNO = A.DRUGNO
        AND    B.IOFLAG = A.IOFLAG
        AND    B.DRUGSEQNO = A.DRUGSEQNO
        AND    F.INSTCD = A.INSTCD
        AND    F.DRUGCD = A.PRCPCD
        AND    A.PRCPDD BETWEEN F.DRUGFROMDD AND F.DRUGTODD
        AND    A.STOCDT = '-'
        AND    F.SEQNO != '99'
        AND    A.NODELIVEYN = '-'
        AND    NVL(F.DRUGKIND2, '-') IN ('26',
                       '27')
        AND    HOSINFLAG != 'O'
        AND    NOT EXISTS (SELECT 1
                FROM   EMR.MMODMRSM Y
                WHERE  Y.INSTCD = A.INSTCD
                AND    Y.PID = A.PID
                AND    Y.ORDDD = A.ORDDD
                AND    Y.PRCPDD = A.PRCPDD
                AND    Y.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    Y.EXECPRCPUNIQNO NOT IN (SELECT EXECPRCPUNIQNO
                        FROM   EMR.MMODMRSM X
                        WHERE  X.INSTCD = Y.INSTCD
                        AND    X.PID = Y.PID
                        AND    X.ORDDD = Y.ORDDD
                        AND    X.PRCPDD = Y.PRCPDD
                        AND    X.EXECPRCPUNIQNO = Y.EXECPRCPUNIQNO
                        AND    X.PRCPDD >= '20150501'
                        AND    X.PRCPFLAG ='D' ) )
        AND    NOT EXISTS (SELECT 1
                FROM   EMR.MMODEXIP IP ,
                       EMR.MMOHIPRC RC
                WHERE  IP.INSTCD = A.INSTCD
                AND    IP.PRCPDD = A.PRCPDD
                AND    IP.PRCPNO = A.PRCPNO
                AND    IP.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    (RC.STOCYN = 'Y'
                        OR     RC.PRCPHISTCD = 'L')
                AND    IP.INSTCD = RC.INSTCD
                AND    IP.PRCPDD = RC.PRCPDD
                AND    IP.PRCPNO = RC.PRCPNO
                AND    IP.PRCPHISTNO = RC.PRCPHISTNO
                UNION ALL
SELECT 1
                FROM   EMR.MMODEXOP IP ,
                       EMR.MMOHOPRC RC
                WHERE  IP.INSTCD = A.INSTCD
                AND    IP.PRCPDD = A.PRCPDD
                AND    IP.PRCPNO = A.PRCPNO
                AND    IP.EXECPRCPUNIQNO = A.EXECPRCPUNIQNO
                AND    (RC.STOCYN = 'Y'
                        OR     RC.PRCPHISTCD = 'L')
                AND    IP.INSTCD = RC.INSTCD
                AND    IP.PRCPDD = RC.PRCPDD
                AND    IP.PRCPNO = RC.PRCPNO
                AND    IP.PRCPHISTNO = RC.PRCPHISTNO )
        UNION ALL
SELECT B.DRUGCD PRCPCD ,
               B.DRUGDD ,
               '' PID ,
               0 PRCPNO ,
               B.DELIVEQTY TOTDRUGQTY ,
               B.INSTCD ,
               B.DRUGNO ,
               B.IOFLAG
        FROM   AST.ADTHNARCSTOC B ,
               AST.ADBMDRUG F
        WHERE  B.INSTCD = :B1
        AND    B.DRUGDD BETWEEN :B5 AND :B3
        AND    F.INSTCD = B.INSTCD
        AND    F.DRUGCD = B.DRUGCD
        AND    B.NARCFLAG IN ('M',
                       'H')
        AND    B.DELYN = 'N'
        AND    B.STATFLAG !='D'
        AND    B.DRUGDD BETWEEN F.DRUGFROMDD AND F.DRUGTODD
        AND    F.SEQNO != '99'
        AND    NVL(F.DRUGKIND2, '-') IN ('26',
                       '27')
        UNION ALL
SELECT GSUM.PRCPCD ,
               GSUM.SUMDD AS PRCPDD ,
               NULL AS PID ,
               0 PRCPNO ,
                       CASE
                         WHEN CEIL(GSUM.DELIVEQTY) - GSUM.DELIVEQTY < 0.5 THEN
CEIL(GSUM.DELIVEQTY)
                         WHEN CEIL(GSUM.DELIVEQTY) - GSUM.DELIVEQTY = 0.5 THEN
GSUM.DELIVEQTY
                         ELSE TRUNC(GSUM.DELIVEQTY) + 0.5
                       END -
                       CASE
                         WHEN CEIL(SUM(NVL(HSUM.SUMQTY, 0))) - SUM(NVL(
HSUM.SUMQTY, 0)) < 0.5 THEN CEIL(SUM(NVL(HSUM.SUMQTY, 0)))
                         WHEN CEIL(SUM(NVL(HSUM.SUMQTY, 0))) - SUM(NVL(
HSUM.SUMQTY, 0)) = 0.5 THEN SUM(NVL(HSUM.SUMQTY, 0))
                         ELSE TRUNC(SUM(NVL(HSUM.SUMQTY, 0))) + 0.5
                       END AS TOTDRUGQTY ,
               GSUM.INSTCD ,
               0 DRUGNO ,
                       CASE
                         WHEN GSUM.SUMKIND = 'N' THEN NVL((SELECT TEMP2
                        FROM   AST.ADBDCMCD
                        WHERE  INSTCD = :B1
                        AND    CDTYPE = 'E001'
                        AND    CMCDNM = GSUM.SUMDEPTCD), 'O')
                         ELSE 'I'
                       END IOFLAG
        FROM   AST.ADTDDRUGSUM GSUM ,
               (SELECT DISTINCT HSUM.INSTCD,
                       HSUM.SUMDD,
                       HSUM.SUMTM,
                       HSUM.SUMNO,
                       HSUM.SUMKIND,
                       HSUM.SUMDEPTCD,
                       HSUM.PRCPCD,
                       HSUM.PRCPDD,
                       HSUM.PRCPNO,
                       HSUM.SUMQTY
                FROM   AST.ADTHDRUGSUM HSUM ,
                       AST.ADTHDRUG HDRUG
                WHERE  HSUM.INSTCD = HDRUG.INSTCD
                AND    HSUM.PRCPDD = HDRUG.PRCPDD
                AND    HSUM.PRCPNO = HDRUG.PRCPNO
                AND    HSUM.PID = HDRUG.PID
                AND    HSUM.PRCPCD = HDRUG.PRCPCD
                AND    HSUM.INSTCD = :B1
                AND    HSUM.SUMDD || HSUM.SUMTM BETWEEN :B5 || :B4 AND :B3 ||
:B2
                AND    HSUM.SUMSTAT = 'S'
                AND    (HSUM.SUMQTY > 0
                        OR     HSUM.SUMKIND = 'N')
                AND    NODELIVEYN = 'Y'
                AND    NVL(RTNFLAG, '-') != 'D') HSUM
        WHERE  GSUM.INSTCD = :B1
        AND    GSUM.SUMDD || GSUM.SUMTM BETWEEN :B5 || :B4 AND :B3 || :B2
        AND    GSUM.SUMSTAT = 'S'
        AND    (GSUM.DELIVEQTY > 0
                OR     GSUM.SUMKIND = 'N')
        AND    (GSUM.STOCDT = '-'
                OR     GSUM.STOCDT IS NULL)
        AND    HSUM.INSTCD(+) = GSUM.INSTCD
        AND    HSUM.SUMDD(+) = GSUM.SUMDD
        AND    HSUM.SUMTM(+) = GSUM.SUMTM
        AND    HSUM.SUMNO(+) = GSUM.SUMNO
        AND    HSUM.SUMKIND(+) = GSUM.SUMKIND
        AND    HSUM.SUMDEPTCD(+) = GSUM.SUMDEPTCD
        AND    HSUM.PRCPCD(+) = GSUM.PRCPCD
        AND    EXISTS (SELECT 1
                FROM   AST.ADBMDRUG
                WHERE  INSTCD = GSUM.INSTCD
                AND    DRUGCD = GSUM.PRCPCD
                AND    NVL(DRUGKIND2, '-') NOT IN ('17',
                               '26',
                               '27')
                AND    GSUM.SUMDD BETWEEN DRUGFROMDD AND DRUGTODD)
        GROUP BY GSUM.PRCPCD, GSUM.SUMDD, GSUM.DELIVEQTY, GSUM.INSTCD,
GSUM.SUMTM, GSUM.SUMNO, GSUM.SUMKIND, GSUM.SUMDEPTCD ) D
WHERE  1 = 1
AND    A.INSTCD = C.INSTCD
AND    A.GOODCD = C.DRUGCD
AND    A.INSTCD = D.INSTCD
AND    A.GOODCD = D.PRCPCD
AND    A.ORGCD = :B6
AND    A.INSTCD = :B1
AND    A.APPLYDATE = (SELECT MAX(APPLYDATE)
        FROM   MIS.RSCHDGOD
        WHERE  ORGCD = A.ORGCD
        AND    INSTCD = A.INSTCD
        AND    GOODCD = A.GOODCD
        AND    TO_DATE(APPLYDATE) <= TO_DATE(:B5 , 'yyyymmdd') )
AND    NVL(C.DRUGKIND2, '-') != '17'
GROUP BY A.ORGCD, A.INSTCD, A.GOODCD, D.IOFLAG
ORDER BY IOFLAG, GOODCD

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch      126  229.465      440.782     116825   31214544          0       1245
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      128  229.466      440.782     116825   31214544          0       1245

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: SYSTEM (ID=5)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      6   TABLE ACCESS BY INDEX ROWID RSCMGBNL (cr=4 pr=0 pw=0 time=71 us cost=2 size=24 card=1)
      6    INDEX RANGE SCAN IX_RSCMGBNL_01 (cr=3 pr=0 pw=0 time=49 us cost=1 size=0 card=1)(Object ID 310346)
      1   INDEX RANGE SCAN IX_ZSDDDEPT_01 (cr=2 pr=0 pw=0 time=14 us cost=2 size=27 card=1)(Object ID 289924)
      4   TABLE ACCESS BY INDEX ROWID RSCMCUST (cr=10 pr=0 pw=0 time=44 us cost=2 size=29 card=1)
      4    INDEX UNIQUE SCAN PK_RSCMCUST (cr=6 pr=0 pw=0 time=24 us cost=1 size=0 card=1)(Object ID 118367)
    270   TABLE ACCESS BY INDEX ROWID RSCMCUST (cr=542 pr=0 pw=0 time=1755 us cost=2 size=29 card=1)
    270    INDEX UNIQUE SCAN PK_RSCMCUST (cr=272 pr=0 pw=0 time=1019 us cost=1 size=0 card=1)(Object ID 118367)
    252   TABLE ACCESS BY INDEX ROWID RSCMCUST (cr=506 pr=0 pw=0 time=2223 us cost=2 size=29 card=1)
    252    INDEX UNIQUE SCAN PK_RSCMCUST (cr=254 pr=0 pw=0 time=1318 us cost=1 size=0 card=1)(Object ID 118367)
      1   TABLE ACCESS BY INDEX ROWID ZBCMCODE (cr=3 pr=0 pw=0 time=12 us cost=2 size=32 card=1)
      1    INDEX UNIQUE SCAN UK_ZBCMCODE_02 (cr=2 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(Object ID 118781)
   1245   WINDOW BUFFER (cr=31214544 pr=116825 pw=0 time=440778466 us cost=3263655 size=413 card=1)
   1245    SORT GROUP BY (cr=31214544 pr=116825 pw=0 time=440773898 us cost=3263655 size=413 card=1)
   6546     HASH JOIN  (cr=31213477 pr=116825 pw=0 time=420878676 us cost=3263651 size=413 card=1)
   3908      NESTED LOOPS  (cr=13650631 pr=12 pw=0 time=12199336 us cost=75 size=168 card=1)
  37407       NESTED LOOPS  (cr=13633133 pr=12 pw=0 time=200328454 us cost=74 size=144 card=1)
  37407        VIEW  VW_SQ_2 (cr=1609 pr=0 pw=0 time=710074 us cost=42 size=1008 card=36)
  37407         SORT GROUP BY (cr=1609 pr=0 pw=0 time=671262 us cost=42 size=972 card=36)
 203506          FILTER  (cr=1609 pr=0 pw=0 time=487079 us)
 203506           INDEX RANGE SCAN IX_RSCHDGOD_01 (cr=1609 pr=0 pw=0 time=419238 us cost=42 size=4644 card=172)(Object ID 118362)
  37407        TABLE ACCESS BY INDEX ROWID RSCHDGOD (cr=13631524 pr=12 pw=0 time=184709900 us cost=1 size=116 card=1)
174407525         INDEX RANGE SCAN IX_RSCHDGOD_04 (cr=758343 pr=2 pw=0 time=95766217 us cost=1 size=0 card=1)(Object ID 312753)
   3908       INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=17498 pr=0 pw=0 time=278008 us cost=1 size=24 card=1)(Object ID 118343)
   3912        SORT AGGREGATE (cr=7228 pr=0 pw=0 time=46111 us)
   3912         FIRST ROW  (cr=7228 pr=0 pw=0 time=35913 us cost=3 size=21 card=1)
   3912          INDEX RANGE SCAN (MIN/MAX) PK_ADBMDRUG (cr=7228 pr=0 pw=0 time=31590 us cost=3 size=21 card=1)(Object ID 118338)
   6555      VIEW  (cr=17562846 pr=116813 pw=0 time=235080912 us cost=3263575 size=41563515 card=169647)
   6555       UNION-ALL  (cr=17562846 pr=116813 pw=0 time=235075991 us)
   3891        SORT GROUP BY (cr=16925527 pr=201 pw=0 time=235066514 us cost=65594 size=159057 card=1161)
   5954         VIEW  (cr=16925527 pr=201 pw=0 time=30000827 us cost=65593 size=159057 card=1161)
   5954          UNION-ALL  (cr=16925527 pr=201 pw=0 time=29997913 us)
   3873           SORT GROUP BY (cr=6283 pr=0 pw=0 time=111457 us)
   5641            PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=6283 pr=0 pw=0 time=70220 us cost=5 size=41 card=1)
   5641             TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: KEY KEY (cr=6283 pr=0 pw=0 time=60690 us cost=5 size=41 card=1)
  35322              INDEX RANGE SCAN PK_ADTHDRUG PARTITION: KEY KEY (cr=2267 pr=0 pw=0 time=35694 us cost=4 size=0 card=1)(Object ID 202442)
   5937           FILTER  (cr=282467 pr=179 pw=0 time=29850614 us)
  10969            FILTER  (cr=114155 pr=22 pw=0 time=29168492 us)
  10969             HASH JOIN RIGHT SEMI (cr=114155 pr=22 pw=0 time=29164658 us cost=52360 size=2422495 card=15629)
  43574              INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=30814 us cost=120 size=353925 card=10725)(Object ID 118343)
  13938              NESTED LOOPS  (cr=113791 pr=22 pw=0 time=28973193 us cost=52240 size=1906738 card=15629)
  20079               NESTED LOOPS  (cr=109526 pr=20 pw=0 time=28940976 us cost=52240 size=1906738 card=15629)
   6098                PARTITION RANGE ALL PARTITION: 1 21 (cr=100238 pr=7 pw=0 time=28850801 us cost=24095 size=453241 card=15629)
   6098                 TABLE ACCESS BY LOCAL INDEX ROWID ADTDDRUG PARTITION: 1 21 (cr=100238 pr=7 pw=0 time=28849080 us cost=24095 size=453241 card=15629)
   6098                  INDEX RANGE SCAN IX_ADTDDRUG_05 PARTITION: 1 21 (cr=97299 pr=6 pw=0 time=28826247 us cost=22057 size=0 card=28132)(Object ID 202538)
  20079                PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=9288 pr=13 pw=0 time=108977 us cost=1 size=0 card=1)
  20079                 INDEX RANGE SCAN PK_ADTHDRUG PARTITION: KEY KEY (cr=9288 pr=13 pw=0 time=93177 us cost=1 size=0 card=1)(Object ID 202442)
  13938               TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 1 (cr=4265 pr=2 pw=0 time=65223 us cost=2 size=93 card=1)
      0            FILTER  (cr=33758 pr=10 pw=0 time=162026 us)
     68             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=33571 pr=9 pw=0 time=153709 us cost=5 size=39 card=1)
     68              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=33503 pr=9 pw=0 time=142492 us cost=4 size=0 card=1)(Object ID 99387)
     34             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=187 pr=1 pw=0 time=960 us cost=5 size=41 card=1)
     52              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=134 pr=0 pw=0 time=311 us cost=4 size=0 card=1)(Object ID 99387)
      0            UNION-ALL  (cr=112905 pr=143 pw=0 time=1489238 us)
      0             NESTED LOOPS  (cr=83146 pr=108 pw=0 time=962391 us cost=5 size=57 card=1)
  10784              NESTED LOOPS  (cr=72079 pr=72 pw=0 time=817925 us cost=5 size=57 card=1)
  10784               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=39142 pr=61 pw=0 time=648833 us cost=4 size=31 card=1)
  10784                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=39142 pr=61 pw=0 time=621609 us cost=4 size=31 card=1)
  10784                 INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=28421 pr=37 pw=0 time=516485 us cost=3 size=0 card=1)(Object ID 203576)
  10784               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=32937 pr=11 pw=0 time=149420 us cost=1 size=0 card=1)
  10784                INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=32937 pr=11 pw=0 time=115609 us cost=1 size=0 card=1)(Object ID 203502)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=11067 pr=36 pw=0 time=121722 us cost=1 size=26 card=1)
      0             NESTED LOOPS  (cr=29759 pr=35 pw=0 time=460528 us cost=5 size=55 card=1)
    700              NESTED LOOPS  (cr=29049 pr=34 pw=0 time=445985 us cost=5 size=55 card=1)
    700               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=27102 pr=34 pw=0 time=430266 us cost=4 size=30 card=1)
    700                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=27102 pr=34 pw=0 time=407223 us cost=4 size=30 card=1)
    700                 INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=26392 pr=33 pw=0 time=391646 us cost=3 size=0 card=1)(Object ID 204062)
    700               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=1947 pr=0 pw=0 time=7871 us cost=1 size=0 card=1)
    700                INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY(AP) KEY(AP) (cr=1947 pr=0 pw=0 time=5694 us cost=1 size=0 card=1)(Object ID 203561)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=710 pr=1 pw=0 time=3616 us cost=1 size=25 card=1)
   3221            TABLE ACCESS BY INDEX ROWID ADTHDRUGSUM (cr=21649 pr=4 pw=0 time=133777 us cost=4 size=38 card=1)
   3221             INDEX RANGE SCAN IX_ADTHDRUGSUM_02 (cr=18428 pr=2 pw=0 time=85231 us cost=3 size=0 card=1)(Object ID 202351)
     17           COUNT STOPKEY (cr=74 pr=3 pw=0 time=30748 us)
     17            TABLE ACCESS BY INDEX ROWID ADBDUNIT (cr=74 pr=3 pw=0 time=30621 us cost=4 size=22 card=1)
     17             INDEX RANGE SCAN PK_ADBDUNIT (cr=57 pr=3 pw=0 time=25973 us cost=3 size=0 card=1)(Object ID 118871)
     14           FILTER  (cr=15874045 pr=21 pw=0 time=147125724 us)
     14            SORT GROUP BY (cr=15874045 pr=21 pw=0 time=147125708 us cost=1454 size=3400 card=17)
     60             FILTER  (cr=15874045 pr=21 pw=0 time=134746106 us)
     60              NESTED LOOPS  (cr=15873955 pr=18 pw=0 time=134744735 us cost=1298 size=3400 card=17)
  40730               NESTED LOOPS  (cr=15838842 pr=18 pw=0 time=147556888 us cost=1298 size=3400 card=17)
  40730                NESTED LOOPS SEMI (cr=15745129 pr=18 pw=0 time=146674537 us cost=1283 size=2805 card=17)
  40730                 NESTED LOOPS  (cr=15745116 pr=18 pw=0 time=146553060 us cost=1267 size=2244 card=17)
  40415                  VIEW  (cr=13105799 pr=8 pw=0 time=132562599 us cost=410 size=765 card=17)
  40415                   SORT UNIQUE (cr=13105799 pr=8 pw=0 time=132532415 us cost=410 size=3536 card=17)
 332579                    FILTER  (cr=13105725 pr=5 pw=0 time=121928671 us)
 332579                     NESTED LOOPS  (cr=13105725 pr=5 pw=0 time=121792122 us cost=409 size=3536 card=17)
28076791                      NESTED LOOPS  (cr=1591002 pr=2 pw=0 time=39497871 us cost=409 size=3536 card=17)
 595178                       NESTED LOOPS  (cr=388378 pr=0 pw=0 time=3864429 us cost=378 size=3060 card=17)
  64438                        NESTED LOOPS  (cr=15820 pr=0 pw=0 time=312729 us cost=312 size=6031 card=37)
     14                         VIEW  (cr=0 pr=0 pw=0 time=185 us cost=2 size=13 card=1)
     14                          FILTER  (cr=0 pr=0 pw=0 time=158 us)
     14                           CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14 us)
      1                            FAST DUAL  (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)
  64438                         TABLE ACCESS FULL ADBHTPNPRC (cr=15820 pr=0 pw=0 time=435949 us cost=310 size=5550 card=37)
 595178                        TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=372558 pr=0 pw=0 time=4615816 us cost=2 size=17 card=1)
 686223                         INDEX RANGE SCAN PK_ADBMDRUG (cr=7604 pr=0 pw=0 time=757482 us cost=1 size=0 card=1)(Object ID 118338)
28076791                       PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=1202624 pr=2 pw=0 time=35035901 us cost=1 size=0 card=1)
28076791                        INDEX RANGE SCAN IX_MMOHIPRC_26 PARTITION: KEY KEY (cr=1202624 pr=2 pw=0 time=26752082 us cost=1 size=0 card=1)(Object ID 290848)
 332579                      TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=11514723 pr=3 pw=0 time=90234301 us cost=2 size=28 card=1)
  40730                  PARTITION RANGE ALL PARTITION: 1 21 (cr=2639317 pr=10 pw=0 time=13156561 us cost=50 size=87 card=1)
  40730                   TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 21 (cr=2639317 pr=10 pw=0 time=12402496 us cost=50 size=87 card=1)
 201453                    INDEX RANGE SCAN IX_ADTHDRUG_12 PARTITION: 1 21 (cr=2529208 pr=4 pw=0 time=7132114 us cost=50 size=0 card=1)(Object ID 202997)
   7796                 INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=13 pr=0 pw=0 time=56809 us cost=1 size=406197 card=12309)(Object ID 118343)
  40730                PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=93713 pr=0 pw=0 time=666445 us cost=1 size=0 card=1)
  40730                 INDEX UNIQUE SCAN PK_ADTDDRUG PARTITION: KEY KEY (cr=93713 pr=0 pw=0 time=545012 us cost=1 size=0 card=1)(Object ID 202430)
     60               TABLE ACCESS BY LOCAL INDEX ROWID ADTDDRUG PARTITION: 1 1 (cr=35113 pr=0 pw=0 time=507436 us cost=1 size=35 card=1)
      0              FILTER  (cr=20 pr=0 pw=0 time=556 us)
      0               TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=20 pr=0 pw=0 time=551 us cost=5 size=39 card=1)
      0                INDEX RANGE SCAN IX_MMODMRSM_01 (cr=20 pr=0 pw=0 time=544 us cost=4 size=0 card=1)(Object ID 99387)
      0               TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=0 pr=0 pw=0 time=0 us cost=5 size=41 card=1)
      0                INDEX RANGE SCAN IX_MMODMRSM_01 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(Object ID 99387)
      0              UNION-ALL  (cr=70 pr=3 pw=0 time=11412 us)
      0               NESTED LOOPS  (cr=52 pr=3 pw=0 time=11215 us cost=5 size=57 card=1)
      5                NESTED LOOPS  (cr=47 pr=3 pw=0 time=11157 us cost=5 size=57 card=1)
      5                 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=25 pr=2 pw=0 time=10456 us cost=4 size=31 card=1)
      5                  TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=25 pr=2 pw=0 time=10442 us cost=4 size=31 card=1)
      5                   INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=20 pr=2 pw=0 time=10401 us cost=3 size=0 card=1)(Object ID 203576)
      5                 PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=22 pr=1 pw=0 time=684 us cost=1 size=0 card=1)
      5                  INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=22 pr=1 pw=0 time=654 us cost=1 size=0 card=1)(Object ID 203502)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=5 pr=0 pw=0 time=47 us cost=1 size=26 card=1)
      0               NESTED LOOPS  (cr=18 pr=0 pw=0 time=147 us cost=5 size=55 card=1)
      0                NESTED LOOPS  (cr=18 pr=0 pw=0 time=139 us cost=5 size=55 card=1)
      0                 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=18 pr=0 pw=0 time=135 us cost=4 size=30 card=1)
      0                  TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=18 pr=0 pw=0 time=121 us cost=4 size=30 card=1)
      0                   INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=18 pr=0 pw=0 time=110 us cost=3 size=0 card=1)(Object ID 204062)
      0                 PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0                  INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(Object ID 203561)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=25 card=1)
      3           FILTER  (cr=762732 pr=1 pw=0 time=56249104 us)
      3            SORT GROUP BY (cr=762732 pr=1 pw=0 time=56249097 us cost=1373 size=245 card=1)
      9             FILTER  (cr=762732 pr=1 pw=0 time=6147300 us)
      9              NESTED LOOPS SEMI (cr=762684 pr=1 pw=0 time=6147152 us cost=1354 size=245 card=1)
      9               NESTED LOOPS  (cr=762671 pr=1 pw=0 time=6147012 us cost=1353 size=212 card=1)
 469566                HASH JOIN  (cr=187397 pr=0 pw=0 time=41466503 us cost=1350 size=531 card=3)
     84                 VIEW  (cr=933 pr=0 pw=0 time=11421 us cost=25 size=50 card=1)
     84                  SORT UNIQUE (cr=933 pr=0 pw=0 time=11337 us cost=25 size=152 card=1)
    890                   FILTER  (cr=933 pr=0 pw=0 time=8218 us)
    890                    NESTED LOOPS  (cr=933 pr=0 pw=0 time=8085 us cost=24 size=152 card=1)
   1000                     NESTED LOOPS  (cr=250 pr=0 pw=0 time=4881 us cost=24 size=152 card=1)
     84                      HASH JOIN  (cr=75 pr=0 pw=0 time=4097 us cost=22 size=138 card=1)
     40                       NESTED LOOPS  (cr=14 pr=0 pw=0 time=73 us cost=3 size=294 card=7)
     14                        VIEW  (cr=0 pr=0 pw=0 time=70 us cost=2 size=13 card=1)
     14                         FILTER  (cr=0 pr=0 pw=0 time=55 us)
     14                          CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14 us)
      1                           FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
     40                        INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=14 pr=0 pw=0 time=493 us cost=1 size=203 card=7)(Object ID 118346)
     55                       TABLE ACCESS FULL ADBMTPNCD (cr=61 pr=0 pw=0 time=3088 us cost=19 size=3552 card=37)
   1000                      INDEX RANGE SCAN PK_ADBMDRUG (cr=175 pr=0 pw=0 time=1669 us cost=1 size=0 card=1)(Object ID 118338)
    890                     TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=683 pr=0 pw=0 time=2893 us cost=2 size=14 card=1)
 181364                 NESTED LOOPS  (cr=186464 pr=0 pw=0 time=46934839 us cost=1325 size=125222 card=986)
 188656                  NESTED LOOPS  (cr=3588 pr=0 pw=0 time=1123612 us cost=1325 size=125222 card=1824)
     29                   SORT UNIQUE (cr=444 pr=0 pw=0 time=19722 us cost=128 size=40 card=1)
    155                    TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=444 pr=0 pw=0 time=15244 us cost=128 size=40 card=1)
    155                     INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=14526 us cost=120 size=0 card=15)(Object ID 118343)
 188656                   PARTITION RANGE ALL PARTITION: 1 21 (cr=3144 pr=0 pw=0 time=1021991 us cost=92 size=0 card=1824)
 188656                    INDEX RANGE SCAN IX_ADTHDRUG_11 PARTITION: 1 21 (cr=3144 pr=0 pw=0 time=921128 us cost=92 size=0 card=1824)(Object ID 202731)
 181364                  TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 1 (cr=182876 pr=0 pw=0 time=44464480 us cost=1196 size=129369 card=1487)
      9                PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=575274 pr=1 pw=0 time=9737697 us cost=1 size=35 card=1)
      9                 TABLE ACCESS BY LOCAL INDEX ROWID ADTDDRUG PARTITION: KEY KEY (cr=575274 pr=1 pw=0 time=8627419 us cost=1 size=35 card=1)
 469566                  INDEX UNIQUE SCAN PK_ADTDDRUG PARTITION: KEY KEY (cr=488079 pr=0 pw=0 time=4036679 us cost=1 size=0 card=1)(Object ID 202430)
      9               INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=13 pr=0 pw=0 time=99 us cost=1 size=406197 card=12309)(Object ID 118343)
      0              FILTER  (cr=12 pr=0 pw=0 time=269 us)
      0               TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=12 pr=0 pw=0 time=265 us cost=5 size=39 card=1)
      0                INDEX RANGE SCAN IX_MMODMRSM_01 (cr=12 pr=0 pw=0 time=263 us cost=4 size=0 card=1)(Object ID 99387)
      0               TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=0 pr=0 pw=0 time=0 us cost=5 size=41 card=1)
      0                INDEX RANGE SCAN IX_MMODMRSM_01 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(Object ID 99387)
      0              UNION-ALL  (cr=36 pr=0 pw=0 time=232 us)
      0               NESTED LOOPS  (cr=27 pr=0 pw=0 time=157 us cost=5 size=57 card=1)
      3                NESTED LOOPS  (cr=24 pr=0 pw=0 time=136 us cost=5 size=57 card=1)
      3                 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=12 pr=0 pw=0 time=78 us cost=4 size=31 card=1)
      3                  TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=12 pr=0 pw=0 time=65 us cost=4 size=31 card=1)
      3                   INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=9 pr=0 pw=0 time=46 us cost=3 size=0 card=1)(Object ID 203576)
      3                 PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=12 pr=0 pw=0 time=53 us cost=1 size=0 card=1)
      3                  INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=12 pr=0 pw=0 time=41 us cost=1 size=0 card=1)(Object ID 203502)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=3 pr=0 pw=0 time=15 us cost=1 size=26 card=1)
      0               NESTED LOOPS  (cr=9 pr=0 pw=0 time=52 us cost=5 size=55 card=1)
      0                NESTED LOOPS  (cr=9 pr=0 pw=0 time=49 us cost=5 size=55 card=1)
      0                 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=9 pr=0 pw=0 time=47 us cost=4 size=30 card=1)
      0                  TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=9 pr=0 pw=0 time=40 us cost=4 size=30 card=1)
      0                   INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=9 pr=0 pw=0 time=36 us cost=3 size=0 card=1)(Object ID 204062)
      0                 PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0                  INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(Object ID 203561)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=25 card=1)
    211        SORT GROUP BY (cr=25230 pr=73 pw=0 time=1218854 us cost=3003445 size=12356224 card=166976)
    385         VIEW  (cr=25230 pr=73 pw=0 time=229740 us cost=3000534 size=12356224 card=166976)
    385          UNION-ALL  (cr=25230 pr=73 pw=0 time=229257 us)
      0           PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=4 size=48 card=1)
      0            TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=4 size=48 card=1)
      0             INDEX RANGE SCAN PK_MMOHOPRC PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(Object ID 203561)
    204           SORT GROUP BY (cr=978 pr=5 pw=0 time=70951 us)
      0            PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=978 pr=5 pw=0 time=69605 us cost=5 size=41 card=1)
      0             TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: KEY KEY (cr=978 pr=5 pw=0 time=69092 us cost=5 size=41 card=1)
   2486              INDEX RANGE SCAN PK_ADTHDRUG PARTITION: KEY KEY (cr=419 pr=2 pw=0 time=27124 us cost=4 size=0 card=1)(Object ID 202442)
    383           FILTER  (cr=10807 pr=41 pw=0 time=211818 us)
    383            FILTER  (cr=1776 pr=4 pw=0 time=168327 us)
    383             HASH JOIN SEMI (cr=1776 pr=4 pw=0 time=168068 us cost=1456 size=172386 card=942)
    399              NESTED LOOPS  (cr=1412 pr=4 pw=0 time=26003 us cost=1336 size=141300 card=942)
    399               NESTED LOOPS  (cr=1013 pr=4 pw=0 time=22523 us cost=1336 size=141300 card=942)
    399                HASH JOIN RIGHT ANTI NA (cr=275 pr=2 pw=0 time=17119 us cost=487 size=116808 card=942)
      1                 INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=2 pr=0 pw=0 time=17 us cost=2 size=75 card=3)(Object ID 118346)
    735                 PARTITION RANGE ALL PARTITION: 1 21 (cr=273 pr=2 pw=0 time=16993 us cost=486 size=98307 card=993)
    735                  TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 21 (cr=273 pr=2 pw=0 time=17567 us cost=486 size=98307 card=993)
    740                   INDEX RANGE SCAN IX_ADTHDRUG_07 PARTITION: 1 21 (cr=69 pr=1 pw=0 time=14748 us cost=375 size=0 card=2154)(Object ID 202683)
    399                PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=738 pr=2 pw=0 time=18434 us cost=1 size=0 card=1)
    399                 INDEX UNIQUE SCAN PK_ADTDDRUG PARTITION: KEY KEY (cr=738 pr=2 pw=0 time=17443 us cost=1 size=0 card=1)(Object ID 202430)
    399               TABLE ACCESS BY LOCAL INDEX ROWID ADTDDRUG PARTITION: 1 1 (cr=399 pr=0 pw=0 time=12019 us cost=1 size=26 card=1)
  43574              INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=34139 us cost=120 size=353925 card=10725)(Object ID 118343)
      0            FILTER  (cr=1540 pr=1 pw=0 time=8004 us)
      0             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=1540 pr=1 pw=0 time=7733 us cost=5 size=39 card=1)
      0              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=1540 pr=1 pw=0 time=7333 us cost=4 size=0 card=1)(Object ID 99387)
      0             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=0 pr=0 pw=0 time=0 us cost=5 size=41 card=1)
      0              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(Object ID 99387)
      0            UNION-ALL  (cr=7491 pr=36 pw=0 time=153683 us)
      0             NESTED LOOPS  (cr=6326 pr=33 pw=0 time=128102 us cost=5 size=57 card=1)
   1167              NESTED LOOPS  (cr=5088 pr=22 pw=0 time=94690 us cost=5 size=57 card=1)
   1167               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2022 pr=17 pw=0 time=76373 us cost=4 size=31 card=1)
   1167                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=2022 pr=17 pw=0 time=75129 us cost=4 size=31 card=1)
   1167                 INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=1165 pr=6 pw=0 time=37987 us cost=3 size=0 card=1)(Object ID 203576)
   1167               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=3066 pr=5 pw=0 time=17019 us cost=1 size=0 card=1)
   1167                INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=3066 pr=5 pw=0 time=13271 us cost=1 size=0 card=1)(Object ID 203502)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=1238 pr=11 pw=0 time=39545 us cost=1 size=26 card=1)
      0             NESTED LOOPS  (cr=1165 pr=3 pw=0 time=22792 us cost=5 size=55 card=1)
      0              NESTED LOOPS  (cr=1165 pr=3 pw=0 time=22424 us cost=5 size=55 card=1)
      0               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=1165 pr=3 pw=0 time=22122 us cost=4 size=30 card=1)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=1165 pr=3 pw=0 time=21295 us cost=4 size=30 card=1)
      0                 INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=1165 pr=3 pw=0 time=20821 us cost=3 size=0 card=1)(Object ID 204062)
      0               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0                INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(Object ID 203561)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=25 card=1)
      2           FILTER  (cr=13409 pr=27 pw=0 time=781898 us)
      2            FILTER  (cr=13389 pr=27 pw=0 time=781680 us)
      2             HASH JOIN RIGHT SEMI (cr=13389 pr=27 pw=0 time=781673 us cost=3729 size=32874732 card=166034)
  43574              INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=46915 us cost=120 size=353925 card=10725)(Object ID 118343)
      2              NESTED LOOPS  (cr=13025 pr=27 pw=0 time=742717 us cost=3608 size=27395610 card=166034)
      2               NESTED LOOPS  (cr=13023 pr=27 pw=0 time=742688 us cost=3608 size=27395610 card=166034)
      2                NESTED LOOPS  (cr=13017 pr=27 pw=0 time=742635 us cost=3463 size=18837 card=161)
    447                 TABLE ACCESS FULL MMODDRRT (cr=12165 pr=25 pw=0 time=1485216 us cost=3318 size=10626 card=161)
      2                 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=852 pr=2 pw=0 time=16700 us cost=1 size=51 card=1)
      2                  TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=852 pr=2 pw=0 time=15736 us cost=1 size=51 card=1)
      4                   INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=848 pr=2 pw=0 time=14094 us cost=1 size=0 card=1)(Object ID 204062)
      2                PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=50 us cost=1 size=0 card=1)
      2                 INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=37 us cost=1 size=0 card=1)(Object ID 203561)
      2               TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=2 pr=0 pw=0 time=24 us cost=1 size=49488 card=1031)
      0            FILTER  (cr=4 pr=0 pw=0 time=47 us)
      0             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=4 pr=0 pw=0 time=45 us cost=5 size=39 card=1)
      0              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=4 pr=0 pw=0 time=40 us cost=4 size=0 card=1)(Object ID 99387)
      0             TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=0 pr=0 pw=0 time=0 us cost=5 size=41 card=1)
      0              INDEX RANGE SCAN IX_MMODMRSM_01 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(Object ID 99387)
      0            UNION-ALL  (cr=16 pr=0 pw=0 time=122 us)
      0             NESTED LOOPS  (cr=3 pr=0 pw=0 time=32 us cost=5 size=57 card=1)
      0              NESTED LOOPS  (cr=3 pr=0 pw=0 time=30 us cost=5 size=57 card=1)
      0               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=28 us cost=4 size=31 card=1)
      0                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=20 us cost=4 size=31 card=1)
      0                 INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=16 us cost=3 size=0 card=1)(Object ID 203576)
      0               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0                INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(Object ID 203502)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)
      0             NESTED LOOPS  (cr=13 pr=0 pw=0 time=82 us cost=5 size=55 card=1)
      2              NESTED LOOPS  (cr=11 pr=0 pw=0 time=61 us cost=5 size=55 card=1)
      2               PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=5 pr=0 pw=0 time=31 us cost=4 size=30 card=1)
      2                TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: KEY KEY (cr=5 pr=0 pw=0 time=28 us cost=4 size=30 card=1)
      2                 INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=15 us cost=3 size=0 card=1)(Object ID 204062)
      2               PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=6 pr=0 pw=0 time=29 us cost=1 size=0 card=1)
      2                INDEX UNIQUE SCAN PK_MMOHOPRC PARTITION: KEY(AP) KEY(AP) (cr=6 pr=0 pw=0 time=22 us cost=1 size=0 card=1)(Object ID 203561)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: 1 1 (cr=2 pr=0 pw=0 time=15 us cost=1 size=25 card=1)
     16        SORT UNIQUE (cr=1413 pr=6 pw=0 time=311718 us cost=263 size=189 card=1)
     21         FILTER  (cr=1413 pr=6 pw=0 time=305468 us)
     21          FILTER  (cr=920 pr=5 pw=0 time=295767 us)
     21           HASH JOIN OUTER (cr=920 pr=5 pw=0 time=295762 us cost=244 size=189 card=1)
     21            NESTED LOOPS  (cr=145 pr=2 pw=0 time=17875 us cost=106 size=177 card=1)
     21             NESTED LOOPS  (cr=132 pr=2 pw=0 time=17685 us cost=106 size=177 card=37)
     21              NESTED LOOPS  (cr=86 pr=2 pw=0 time=17174 us cost=40 size=5032 card=37)
     21               TABLE ACCESS BY INDEX ROWID ADTHNARC (cr=21 pr=2 pw=0 time=6611 us cost=6 size=1924 card=37)
     21                INDEX RANGE SCAN IX_ADTHNARC_02 (cr=3 pr=0 pw=0 time=1129 us cost=3 size=0 card=67)(Object ID 202355)
     21               PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=65 pr=0 pw=0 time=2585 us cost=1 size=84 card=1)
     21                TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: KEY KEY (cr=65 pr=0 pw=0 time=2493 us cost=1 size=84 card=1)
     21                 INDEX UNIQUE SCAN PK_ADTHDRUG PARTITION: KEY KEY (cr=44 pr=0 pw=0 time=1085 us cost=1 size=0 card=1)(Object ID 202442)
     21              INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=46 pr=0 pw=0 time=371 us cost=1 size=0 card=1)(Object ID 118343)
     21             TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=13 pr=0 pw=0 time=96 us cost=2 size=41 card=1)
   6580            VIEW  (cr=775 pr=3 pw=0 time=286890 us cost=138 size=1200 card=100)
   6580             SORT GROUP BY (cr=775 pr=3 pw=0 time=285623 us cost=138 size=990 card=0)
  13065              HASH JOIN  (cr=775 pr=3 pw=0 time=264517 us cost=137 size=990 card=15)
  13690               HASH JOIN  (cr=680 pr=0 pw=0 time=234203 us cost=108 size=4650 card=93)
     55                VIEW  (cr=6 pr=0 pw=0 time=130 us cost=3 size=675 card=25)
     55                 UNION-ALL  (cr=6 pr=0 pw=0 time=128 us)
     22                  FILTER  (cr=2 pr=0 pw=0 time=60 us)
     22                   INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=2 pr=0 pw=0 time=55 us cost=2 size=348 card=12)(Object ID 118346)
     19                  FILTER  (cr=2 pr=0 pw=0 time=29 us)
     19                   INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=2 pr=0 pw=0 time=28 us cost=2 size=551 card=19)(Object ID 118346)
     14                  FILTER  (cr=2 pr=0 pw=0 time=20 us)
     14                   INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=2 pr=0 pw=0 time=6 us cost=2 size=58 card=2)(Object ID 118346)
  13690                TABLE ACCESS BY INDEX ROWID ADBDUNIT (cr=674 pr=0 pw=0 time=220656 us cost=105 size=57592 card=2504)
  13690                 INDEX RANGE SCAN IX_ADBDUNIT_01 (cr=61 pr=0 pw=0 time=42547 us cost=19 size=0 card=2504)(Object ID 118868)
   6580               VIEW  VW_SQ_1 (cr=95 pr=3 pw=0 time=74001 us cost=29 size=40064 card=2504)
   6580                SORT GROUP BY (cr=95 pr=3 pw=0 time=72479 us cost=29 size=35056 card=2504)
  13690                 FILTER  (cr=95 pr=3 pw=0 time=67406 us)
  13690                  INDEX RANGE SCAN PK_ADBDUNIT (cr=95 pr=3 pw=0 time=62413 us cost=29 size=35056 card=2504)(Object ID 118871)
      0          FILTER  (cr=84 pr=0 pw=0 time=1460 us)
      0           TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=84 pr=0 pw=0 time=1443 us cost=5 size=39 card=1)
      0            INDEX RANGE SCAN IX_MMODMRSM_01 (cr=84 pr=0 pw=0 time=1414 us cost=4 size=0 card=1)(Object ID 99387)
      0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0            TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=0 pr=0 pw=0 time=0 us cost=5 size=41 card=1)
      0             INDEX RANGE SCAN IX_MMODMRSM_06 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(Object ID 295337)
      0          UNION-ALL  (cr=409 pr=1 pw=0 time=7904 us)
      0           NESTED LOOPS  (cr=344 pr=1 pw=0 time=6011 us cost=5 size=59 card=1)
     58            NESTED LOOPS  (cr=286 pr=1 pw=0 time=4315 us cost=5 size=59 card=1)
     58             PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=104 pr=1 pw=0 time=2937 us cost=4 size=31 card=1)
     58              TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=104 pr=1 pw=0 time=2863 us cost=4 size=31 card=1)
     58               INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=63 pr=0 pw=0 time=2278 us cost=3 size=0 card=1)(Object ID 203576)
     58             PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=182 pr=0 pw=0 time=1279 us cost=1 size=0 card=1)
     58              INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=182 pr=0 pw=0 time=1065 us cost=1 size=0 card=1)(Object ID 203502)
      0            TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=58 pr=0 pw=0 time=1308 us cost=1 size=28 card=1)
      0           NESTED LOOPS  (cr=65 pr=0 pw=0 time=1696 us cost=5 size=57 card=1)
      0            NESTED LOOPS  (cr=65 pr=0 pw=0 time=1671 us cost=5 size=57 card=1)
      0             PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=65 pr=0 pw=0 time=1654 us cost=4 size=27 card=1)
      0              TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=65 pr=0 pw=0 time=1605 us cost=4 size=27 card=1)
      3               INDEX RANGE SCAN PK_MMOHOPRC PARTITION: KEY KEY (cr=63 pr=0 pw=0 time=1562 us cost=3 size=0 card=1)(Object ID 203561)
      0             PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0              INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY(AP) KEY(AP) (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(Object ID 204062)
      0            TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=30 card=1)
    470        SORT UNIQUE (cr=18176 pr=141 pw=0 time=1440759 us cost=2354 size=3264 card=17)
    566         FILTER  (cr=18176 pr=141 pw=0 time=975618 us)
    878          FILTER  (cr=3979 pr=41 pw=0 time=82091 us)
    878           HASH JOIN  (cr=3979 pr=41 pw=0 time=81835 us cost=2194 size=3264 card=17)
   1028            TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=1023 pr=0 pw=0 time=5295 us cost=259 size=7626 card=186)
   1183             INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=1215 us cost=120 size=0 card=276)(Object ID 118343)
    878            NESTED LOOPS  (cr=2956 pr=41 pw=0 time=62656 us cost=1935 size=182106 card=1206)
    878             NESTED LOOPS  (cr=2021 pr=40 pw=0 time=56877 us cost=1935 size=182106 card=1206)
    878              TABLE ACCESS BY INDEX ROWID ADTHNARC (cr=412 pr=39 pw=0 time=45810 us cost=849 size=80802 card=1206)
    878               INDEX RANGE SCAN IX_ADTHNARC_01 (cr=12 pr=0 pw=0 time=1995 us cost=12 size=0 card=2180)(Object ID 202354)
    878              PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=1609 pr=1 pw=0 time=30375 us cost=1 size=0 card=1)
    878               INDEX UNIQUE SCAN PK_ADTHDRUG PARTITION: KEY KEY (cr=1609 pr=1 pw=0 time=27302 us cost=1 size=0 card=1)(Object ID 202442)
    878             TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 1 (cr=935 pr=1 pw=0 time=14845 us cost=1 size=84 card=1)
    308          FILTER  (cr=7769 pr=63 pw=0 time=652261 us)
    316           TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=3808 pr=15 pw=0 time=146647 us cost=5 size=39 card=1)
    316            INDEX RANGE SCAN IX_MMODMRSM_01 (cr=3491 pr=5 pw=0 time=39976 us cost=4 size=0 card=1)(Object ID 99387)
      4           FILTER  (cr=3961 pr=48 pw=0 time=502998 us)
      4            TABLE ACCESS BY INDEX ROWID MMODMRSM (cr=3961 pr=48 pw=0 time=502641 us cost=5 size=41 card=1)
   5996             INDEX RANGE SCAN IX_MMODMRSM_06 (cr=1333 pr=17 pw=0 time=209943 us cost=4 size=0 card=1)(Object ID 295337)
      4          UNION-ALL  (cr=6428 pr=37 pw=0 time=297079 us)
      0           NESTED LOOPS  (cr=4632 pr=33 pw=0 time=231137 us cost=5 size=59 card=1)
    472            NESTED LOOPS  (cr=4073 pr=19 pw=0 time=175685 us cost=5 size=59 card=1)
    472             PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2227 pr=18 pw=0 time=157374 us cost=4 size=31 card=1)
    472              TABLE ACCESS BY LOCAL INDEX ROWID MMODEXIP PARTITION: KEY KEY (cr=2227 pr=18 pw=0 time=155845 us cost=4 size=31 card=1)
    472               INDEX RANGE SCAN IX_MMODEXIP_01 PARTITION: KEY KEY (cr=1721 pr=13 pw=0 time=125420 us cost=3 size=0 card=1)(Object ID 203576)
    472             PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=1846 pr=1 pw=0 time=17271 us cost=1 size=0 card=1)
    472              INDEX UNIQUE SCAN PK_MMOHIPRC PARTITION: KEY(AP) KEY(AP) (cr=1846 pr=1 pw=0 time=15490 us cost=1 size=0 card=1)(Object ID 203502)
      0            TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 1 (cr=559 pr=14 pw=0 time=54287 us cost=1 size=28 card=1)
      4           NESTED LOOPS  (cr=1796 pr=4 pw=0 time=61717 us cost=5 size=57 card=1)
      7            NESTED LOOPS  (cr=1789 pr=4 pw=0 time=61022 us cost=5 size=57 card=1)
      4             PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=1777 pr=2 pw=0 time=40412 us cost=4 size=27 card=1)
      4              TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=1777 pr=2 pw=0 time=39087 us cost=4 size=27 card=1)
    128               INDEX RANGE SCAN PK_MMOHOPRC PARTITION: KEY KEY (cr=1650 pr=2 pw=0 time=37656 us cost=3 size=0 card=1)(Object ID 203561)
      7             PARTITION RANGE SINGLE PARTITION: KEY(AP) KEY(AP) (cr=12 pr=2 pw=0 time=20191 us cost=1 size=0 card=1)
      7              INDEX RANGE SCAN IX_MMODEXOP_01 PARTITION: KEY(AP) KEY(AP) (cr=12 pr=2 pw=0 time=20176 us cost=1 size=0 card=1)(Object ID 204062)
      4            TABLE ACCESS BY LOCAL INDEX ROWID MMODEXOP PARTITION: 1 1 (cr=7 pr=0 pw=0 time=59 us cost=1 size=30 card=1)
      0        FILTER  (cr=461 pr=0 pw=0 time=8613 us)
      0         NESTED LOOPS  (cr=461 pr=0 pw=0 time=8610 us cost=6 size=81 card=1)
      0          NESTED LOOPS  (cr=461 pr=0 pw=0 time=8608 us cost=6 size=81 card=1)
    119           TABLE ACCESS BY INDEX ROWID ADBMDRUG (cr=458 pr=0 pw=0 time=5238 us cost=5 size=36 card=1)
    119            INDEX RANGE SCAN IX_ADBMDRUG_02 (cr=365 pr=0 pw=0 time=4986 us cost=4 size=0 card=1)(Object ID 118341)
      0           INDEX RANGE SCAN PK_ADTHNARCSTOC (cr=3 pr=0 pw=0 time=585 us cost=1 size=0 card=1)(Object ID 95884)
      0          TABLE ACCESS BY INDEX ROWID ADTHNARCSTOC (cr=0 pr=0 pw=0 time=0 us cost=2 size=45 card=1)
      7        TABLE ACCESS BY INDEX ROWID ADBDCMCD (cr=22 pr=0 pw=0 time=1856 us cost=3 size=27 card=1)
      7         INDEX RANGE SCAN IX_ADBDCMCD_01 (cr=15 pr=0 pw=0 time=518 us cost=2 size=0 card=1)(Object ID 118346)
   1967        SORT GROUP BY (cr=592017 pr=116392 pw=0 time=16873663 us cost=191912 size=196812 card=1491)
   1968         FILTER  (cr=592017 pr=116392 pw=0 time=16848012 us)
   1968          HASH JOIN SEMI (cr=592017 pr=116392 pw=0 time=16847516 us cost=191911 size=196812 card=1491)
   2998           HASH JOIN OUTER (cr=591653 pr=116392 pw=0 time=16830582 us cost=191791 size=147609 card=1491)
   2997            TABLE ACCESS BY INDEX ROWID ADTDDRUGSUM (cr=19512 pr=34 pw=0 time=5229256 us cost=5491 size=70077 card=1491)
   3139             INDEX RANGE SCAN IX_ADTDDRUGSUM_01 (cr=18952 pr=10 pw=0 time=5226271 us cost=5256 size=0 card=1718)(Object ID 202996)
      5            VIEW  (cr=572141 pr=116358 pw=0 time=11345006 us cost=186299 size=236184 card=4542)
      5             SORT UNIQUE (cr=572141 pr=116358 pw=0 time=11345005 us cost=186299 size=513246 card=4542)
      5              FILTER  (cr=572141 pr=116358 pw=0 time=10602639 us)
      5               NESTED LOOPS  (cr=572141 pr=116358 pw=0 time=10602635 us cost=186298 size=513246 card=4542)
  16218                NESTED LOOPS  (cr=566998 pr=116358 pw=0 time=11152414 us cost=186298 size=513246 card=4542)
   7739                 TABLE ACCESS FULL ADTHDRUGSUM (cr=118032 pr=116346 pw=0 time=9956355 us cost=30901 size=317940 card=4542)
  16218                 PARTITION RANGE ALL PARTITION: 1 21 (cr=448966 pr=12 pw=0 time=1281879 us cost=33 size=0 card=1)
  16218                  INDEX RANGE SCAN IX_ADTHDRUG_09 PARTITION: 1 21 (cr=448966 pr=12 pw=0 time=1200091 us cost=33 size=0 card=1)(Object ID 202707)
      5                TABLE ACCESS BY LOCAL INDEX ROWID ADTHDRUG PARTITION: 1 1 (cr=5143 pr=0 pw=0 time=46956 us cost=34 size=43 card=1)
  43574           INDEX RANGE SCAN IX_ADBMDRUG_05 (cr=364 pr=0 pw=0 time=34650 us cost=120 size=353925 card=10725)(Object ID 118343)


댓글 (0)
목록 답변등록