XinYu_RZMES/.svn/pristine/ec/ecfe658ea5c5eb29a832f4481e8f271eb1ae848d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="">
  
     <query id="UIB030050.SELECT" desc="" fetchSize="10">  
        <![CDATA[
      SELECT (CASE WHEN SUBSTR(T.DT,7,1) = '0' THEN SUBSTR(T.DT,8,1) ELSE SUBSTR(T.DT,7,2) END ) SEQ
    ,TO_CHAR('当日') DANGRI
    ,TO_CHAR('累计') LEIJI        
      ,TO_CHAR(SUM(SLAB_WGT)OVER(ORDER BY T.DT),'FM999990.000') SLAB_WGT_LJ
      ,TO_CHAR(SUM(HGYL)OVER(ORDER BY T.DT),'FM999990.000')     HGYL_LJ
      ,TO_CHAR(SUM(HGL)OVER(ORDER BY T.DT),'FM999990.000')      HGL_LJ
      ,TO_CHAR((CASE WHEN SUM(HGL)OVER(ORDER BY T.DT) != 0 AND SUM(HGYL)OVER(ORDER BY T.DT)!= 0 THEN
      SUM(HGL)OVER(ORDER BY T.DT)/SUM(HGYL)OVER(ORDER BY T.DT) END)*100, 'FM990.00')      CCL_LJ
      ,TO_CHAR((CASE WHEN SUM(HGL)OVER(ORDER BY T.DT) != 0 THEN
      SUM(HGL)OVER(ORDER BY T.DT)/(SUM(HGL)OVER(ORDER BY T.DT)+(CASE WHEN SUM(GYC_LEIJI)OVER(ORDER BY T.DT) != 0 THEN
      SUM(GYC_LEIJI)OVER(ORDER BY T.DT) ELSE 0 END))END)*100,'FM990.00')     HGLV_LJ
      ,TO_CHAR(SUM(GYC_TUDU)OVER(ORDER BY T.DT),'FM999990.000')          GYC_TUDU_LJ
      ,TO_CHAR(SUM(GYC_LANGXING)OVER(ORDER BY T.DT),'FM999990.000')      GYC_LANGXING_LJ
      ,TO_CHAR(SUM(GYC_GUNYIN)OVER(ORDER BY T.DT),'FM999990.000')        GYC_GUNYIN_LJ
      ,TO_CHAR(SUM(GYC_YAHENG)OVER(ORDER BY T.DT),'FM999990.000')        GYC_YAHENG_LJ
      ,TO_CHAR(SUM(GYC_HUASHANG)OVER(ORDER BY T.DT),'FM999990.000')      GYC_HUASHANG_LJ
      ,TO_CHAR(SUM(GYC_TUKUAI)OVER(ORDER BY T.DT),'FM999990.000')        GYC_TUKUAI_LJ
      ,TO_CHAR(SUM(GYC_QITA)OVER(ORDER BY T.DT),'FM999990.000')          GYC_QITA_LJ
      ,TO_CHAR(SUM(GYC_GUANGLIANG)OVER(ORDER BY T.DT),'FM999990.000')    GYC_GUANGLIANG_LJ
      ,TO_CHAR(SUM(GYC_ZHONGLIANG)OVER(ORDER BY T.DT),'FM999990.000')    GYC_ZHONGLIANG_LJ
      ,TO_CHAR(SUM(GYC_BIANSUN)OVER(ORDER BY T.DT),'FM999990.000')       GYC_BIANSUN_LJ
      ,TO_CHAR(SUM(GYC_HOUDU)OVER(ORDER BY T.DT),'FM999990.000')         GYC_HOUDU_LJ   
      ,TO_CHAR(SUM(GYC_XIEXING)OVER(ORDER BY T.DT),'FM999990.000')       GYC_XIEXING_LJ   
      ,TO_CHAR(SUM(GYC_MADIAN)OVER(ORDER BY T.DT),'FM999990.000')         GYC_MADIAN_LJ   
      ,TO_CHAR(SUM(GYC_KUANDU)OVER(ORDER BY T.DT),'FM999990.000')         GYC_KUANDU_LJ   
      ,TO_CHAR(SUM(GYC_JUANXING)OVER(ORDER BY T.DT),'FM999990.000')         GYC_JUANXING_LJ   
      ,TO_CHAR(SUM(GYC_ZHEDIE)OVER(ORDER BY T.DT),'FM999990.000')         GYC_ZHEDIE_LJ   
      ,TO_CHAR(SUM(GYC_TAXING)OVER(ORDER BY T.DT),'FM999990.000')         GYC_TAXING_LJ   
      ,TO_CHAR(SUM(GYC_BUPING)OVER(ORDER BY T.DT),'FM999990.000')         GYC_BUPING_LJ   
      ,TO_CHAR(SUM(GYC_XINGNENG)OVER(ORDER BY T.DT),'FM999990.000')         GYC_XINGNENG_LJ  
      ,TO_CHAR(SUM(GYC_FEIPIN)OVER(ORDER BY T.DT),'FM999990.000')         GYC_FEIPIN_LJ  
      ,TO_CHAR(SUM(GYC_LEIJI)OVER(ORDER BY T.DT),'FM999990.000')         GYC_LEIJI_LJ  
      ,TO_CHAR(SUM(GZC_QIPAO)OVER(ORDER BY T.DT),'FM999990.000')         GZC_QIPAO_LJ  
      ,TO_CHAR(SUM(GZC_FENCENG)OVER(ORDER BY T.DT),'FM999990.000')         GZC_FENCENG_LJ  
      ,TO_CHAR(SUM(GZC_JIAZA)OVER(ORDER BY T.DT),'FM999990.000')         GZC_JIAZA_LJ  
      ,TO_CHAR(SUM(GZC_BIANLIE)OVER(ORDER BY T.DT),'FM999990.000')         GZC_BIANLIE_LJ 
      ,TO_CHAR(SUM(GZC_AOKENG)OVER(ORDER BY T.DT),'FM999990.000')         GZC_AOKENG_LJ  
      ,TO_CHAR(SUM(GZC_HENGLIE)OVER(ORDER BY T.DT),'FM999990.000')         GZC_HENGLIE_LJ  
      ,TO_CHAR(SUM(GZC_QITA)OVER(ORDER BY T.DT),'FM999990.000')         GZC_QITA_LJ    
      ,TO_CHAR(SUM(GZC_ZONGLIE)OVER(ORDER BY T.DT),'FM999990.000')         GZC_ZONGLIE_LJ    
      ,TO_CHAR(SUM(GZC_LEIJI)OVER(ORDER BY T.DT),'FM999990.000')         GZC_LEIJI_LJ   
      ,T.*
      FROM 
        (SELECT
        SUBSTR(H02CC.MILL_DTIME,1,8) DT
        ,to_char(SUM(G02SC.SLAB_WGT)/1000,'FM999990.000') SLAB_WGT        --原料
        ,to_char((SUM(G02SC.SLAB_WGT)-SUM(CASE WHEN  H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '1'
        THEN H02CC.ACT_WGT ELSE 0 END)*1.064)/1000,'FM999990.000') HGYL                --合格原料 = 原料 - 钢质次小计                                                                
        ,TO_CHAR(SUM((CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.ORD_FL   = '2' THEN H02CC.ACT_WGT END))/1000,'FM999990.000') HGL                                                  --合格量
        ,(CASE WHEN SUM(G02SC.SLAB_WGT) != 0 AND to_char(SUM((CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL THEN H02CC.ACT_WGT/1000 END)),'FM999990.000') != 0
        THEN TO_CHAR((SUM(CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL THEN H02CC.ACT_WGT END)
        /(SUM(G02SC.SLAB_WGT)-SUM(CASE WHEN  H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '1'THEN H02CC.ACT_WGT ELSE 0 END)*1.064)
        )*100,'FM990.00') 
        ELSE ' ' END)CCL  
        ,(CASE WHEN SUM((CASE WHEN H02CC.TOT_DEC_GRD = '1'  OR H02CC.TOT_DEC_GRD IS NULL THEN H02CC.ACT_WGT END)) != 0 
        THEN TO_CHAR((SUM((CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL THEN H02CC.ACT_WGT END))
        /(SUM((CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL THEN H02CC.ACT_WGT END))+SUM(CASE WHEN (H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '2') 
        OR (H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 IS NULL AND H02CC.MATLQLTY_DEC_GRD != '1' )
        OR H02CC.EXTSHAPE_DEC_GRD = '4' THEN H02CC.ACT_WGT ELSE 0 END))
        )*100,'FM990.00') 
        ELSE ' ' END)HGLV                                                          --成材率
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '10' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_TUDU                                                --工艺次,凸度 
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '09' THEN
        H02CC.ACT_WGT END ))/1000,'FM999990.000') GYC_LANGXING       --工艺次,浪形 
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '08' THEN
        H02CC.ACT_WGT END ))/1000,'FM999990.000') GYC_GUNYIN         --工艺次,辊印
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '24' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_YAHENG         --工艺次,压痕
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '03' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_HUASHANG       --工艺次,划伤
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '01' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_TUKUAI         --工艺次,凸块
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '02' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_YIWU           --工艺次,异物压入
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '56' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_QITA           --工艺次,其他
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '53' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_GUANGLIANG     --工艺次,光亮带
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '55' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_ZHONGLIANG     --工艺次,重量不良
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '52' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_BIANSUN        --工艺次,边损
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '54' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_HOUDU          --工艺次,厚度不良 
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '43' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_XIEXING        --工艺次,楔形
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '39' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_MADIAN         --工艺次,麻点
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '29' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_KUANDU         --工艺次,宽度不良
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '26' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_JUANXING       --工艺次,卷形不良
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '24' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_ZHEDIE         --工艺次,折叠
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '22' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_TAXING         --工艺次,塔形
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '20' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000') GYC_BUPING         --工艺次,不平度
        ,TO_CHAR(SUM(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 IS NULL AND H02CC.MATLQLTY_DEC_GRD != '1' 
        THEN H02CC.ACT_WGT END)/1000,'FM999990.000')                               GYC_XINGNENG      --工艺次,性能次   
        ,TO_CHAR(SUM(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '4' THEN
        H02CC.ACT_WGT END)/1000,'FM999990.000')                                    GYC_FEIPIN        --工艺次，废品
        --,TO_CHAR(SUM(SQ_QD_COMMON.FIND_COIL_GYC(H02CC.COIL_NO))/1000,'FM999990.000')           GYC_LEIJI 
        ,TO_CHAR(SUM(CASE WHEN (H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '2') 
        OR (H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 IS NULL AND H02CC.MATLQLTY_DEC_GRD != '1' )
        OR H02CC.EXTSHAPE_DEC_GRD = '4' THEN H02CC.ACT_WGT END)/1000,'FM999990.000') GYC_LEIJI        --工艺次，累计
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '37' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_QIPAO         --钢质次,气泡
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '38' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_FENCENG       --钢质次,分层
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '42' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_JIAZA         --钢质次,夹杂
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '11' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_BIANLIE       --钢质次,边裂
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '06' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_AOKENG        --钢质次,凹坑
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '57' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_HENGLIE       --钢质次,横裂
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '59' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_QITA          --钢质次,其它(钢质)
        ,TO_CHAR(SUM((CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '58' THEN
        H02CC.ACT_WGT END))/1000,'FM999990.000')  GZC_ZONGLIE       --钢质次,纵裂
        --,TO_CHAR(SUM(SQ_QD_COMMON.FIND_COIL_GZC(H02CC.COIL_NO))/1000,'FM999990.000')           GZC_LEIJI
                 --钢质次，累计 
        ,TO_CHAR(SUM(CASE WHEN  H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '1'
        THEN H02CC.ACT_WGT END)/1000,'FM999990.000') GZC_LEIJI          
        FROM TBH02_COIL_COMM H02CC
        ,TBG02_SLAB_COMM G02SC
        ,(SELECT T.SM_CD,T.SM_CFNM,T.CD_SEQ 
        FROM TBZ00_COMMCD T WHERE T.LG_CD = 'H02001' AND T.CD_SEQ IS NOT NULL) Z00C
      ,(SELECT T.SM_CD,T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'B01132') CDDM    
        WHERE H02CC.SLAB_NO = G02SC.SLAB_NO(+)
        AND   H02CC.CRK_CD1 = Z00C.SM_CD(+)
        AND    SUBSTR(G02SC.SLAB_NO,3,1) = CDDM.SM_CD      
        AND   H02CC.MILL_DTIME >= SUBSTR(:1,1,6)||'01000000'
        AND   H02CC.MILL_DTIME <= :2||'235959'
		AND   H02CC.MISSNO_CLF_CD IS NULL		
        GROUP BY 
        SUBSTR(H02CC.MILL_DTIME,1,8)  
        ORDER BY   SUBSTR(H02CC.MILL_DTIME,1,8)) T
        ]]>   
    </query> 
</queryMap>