XinYu_RZMES/.svn/pristine/5e/5e1ed79e38e435a05308e3ed2fc1104669b1c3bb.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="">
			  
     <query id="UIB030030.SELECT" desc="" fetchSize="10">  
        <![CDATA[
SELECT H02CC.COIL_NO 
,SUBSTR(H02CC.MILL_DTIME,1,8) COIL_END_DTIME                            --日期(加入炉出炉时间，不确定？)                                                                 --钢卷完成时间
,CDDM.SM_CFNM CD                                                                   --产地                                                                        --产地
,G02SC.SLAB_THK                                                             --坯料（厚度）                                                                        --坯料
,F02SM.ROLL_MANA_NO                                                         --计划号（不确定？）                                                                                                      --计划号
,SUBSTR(H02CC.OLD_SAMPL_NO,1,10) PIHAO                                      --批号                                                                        --轧批号
,SUBSTR(H02CC.OLD_SAMPL_NO,11,3) JUANHAO                                    --卷号                                                                        --卷号
,SUBSTR(G02SC.SLAB_NO,1,10)      CHARGENO                                                                                   --炉次号 
,F02SM.SPEC_STL_GRD 
,H02CC.SPEC_STL_GRD SPEC_STL_GRD1                                                                                           --计划钢号
,H02CC.COIL_THK                                                                                                             --钢卷厚度
,H02CC.COIL_WTH                                                                                                             --钢卷厚度
,H02CC.COIL_LEN                                                                                                             --钢卷长度
,to_char(G02SC.SLAB_WGT/1000,'FM990.000') SLAB_WGT                                                              --原料
,(CASE WHEN H02CC.TOT_DEC_GRD = '1' THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END) JYL    --检验量
,(CASE WHEN SUBSTR(H02CC.SAMPL_NO,14) <> 'XX' AND H02CC.MATLQLTY_DEC_GRD = '1' THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END) FYL                                         --复样量
,(CASE WHEN G02SC.SLAB_WGT != 0 AND (CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL  THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END) != 0
THEN TO_CHAR((H02CC.ACT_WGT/G02SC.SLAB_WGT)*100,'FM990.00') 
ELSE ' ' END)CCL                                                            --成材率
,(CASE WHEN H02CC.TOT_DEC_GRD = '1' OR H02CC.TOT_DEC_GRD IS NULL THEN TO_CHAR(H02CC.ACT_WGT/1000,'FM990.000') END) HGL 
,(CASE WHEN H02CC.ORD_FL      = '2' THEN TO_CHAR(H02CC.ACT_WGT/1000,'FM990.000') END) FJH                                                 --合格量
,(CASE WHEN H02CC.TOT_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD = H02CC.SPEC_STL_GRD THEN TO_CHAR(H02CC.ACT_WGT/1000,'FM990.000')  END) PZL       --品种量 
,H02CC.ACT_WGT
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '10' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000')  END) GYC_TUDU                                                --工艺次,凸度 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '09' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000')  END ) GYC_LANGXING       --工艺次,浪形 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '08' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000')  END ) GYC_GUNYIN         --工艺次,辊印
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '24' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_YAHENG         --工艺次,压痕
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '03' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_HUASHANG       --工艺次,划伤
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '01' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_TUKUAI         --工艺次,凸块
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '02' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_YIWU           --工艺次,异物压入
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '56' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_QITA           --工艺次,其他
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '53' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_GUANGLIANG     --工艺次,光亮带
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '55' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_ZHONGLIANG     --工艺次,重量不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '52' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_BIANSUN        --工艺次,边损
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '54' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_HOUDU          --工艺次,厚度不良 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '43' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_XIEXING        --工艺次,楔形
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '39' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_MADIAN         --工艺次,麻点
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '29' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_KUANDU         --工艺次,宽度不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '26' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_JUANXING       --工艺次,卷形不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '24' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_ZHEDIE         --工艺次,折叠
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '22' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_TAXING         --工艺次,塔形
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '20' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYC_BUPING         --工艺次,不平度
,CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 IS NULL AND H02CC.MATLQLTY_DEC_GRD != '1' 
THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END                               GYC_XINGNENG      --工艺次,性能次   
,CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '4' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END                                    GYC_FEIPIN        --工艺次，废品
--,SQ_QD_COMMON.FIND_COIL_GYC(H02CC.COIL_NO)           GYC_LEIJI         --工艺次，累计
,(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 to_char(H02CC.ACT_WGT/1000,'FM990.000')END) GYC_LEIJI
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '37' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_QIPAO         --钢质次,气泡
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '38' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_FENCENG       --钢质次,分层
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '42' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_JIAZA         --钢质次,夹杂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '11' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_BIANLIE       --钢质次,边裂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '06' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_AOKENG        --钢质次,凹坑
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '57' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_HENGLIE       --钢质次,横裂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '59' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_QITA          --钢质次,其它(钢质)
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '3' AND H02CC.CRK_CD1 = '58' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZC_ZONGLIE       --钢质次,纵裂
--,SQ_QD_COMMON.FIND_COIL_GZC(H02CC.COIL_NO)           GZC_LEIJI         --钢质次，累计
,(CASE WHEN  H02CC.EXTSHAPE_DEC_GRD = '3' AND Z00C.CD_SEQ = '1'
THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GZC_LEIJI        
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '10' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_TUDU           --工艺改判,凸度 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '09' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_LANGXING       --工艺改判,浪形 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '08' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_GUNYIN         --工艺改判,辊印
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '24' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_YAHENG         --工艺改判,压痕
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '03' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_HUASHANG       --工艺改判,划伤
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '01' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_TUKUAI         --工艺改判,凸块
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '02' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_YIWU           --工艺改判,异物压入
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '56' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_QITA           --工艺改判,其他
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '53' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_GUANGLIANG     --工艺改判,光亮带
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '55' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_ZHONGLIANG     --工艺改判,重量不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '52' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_BIANSUN        --工艺改判,边损
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '54' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_HOUDU          --工艺改判,厚度不良 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '43' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_XIEXING        --工艺改判,楔形
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '39' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_MADIAN         --工艺改判,麻点
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '29' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_KUANDU         --工艺改判,宽度不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '26' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_JUANXING       --工艺改判,卷形不良
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '24' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_ZHEDIE         --工艺改判,折叠
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '22' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_TAXING         --工艺改判,塔形
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '20' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_BUPING         --工艺改判,不平度
,CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND H02CC.CRK_CD1 IS NULL 
AND H02CC.MATLQLTY_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END                               GYGP_XINGNENG      --工艺改判,性能次
--,SQ_QD_COMMON.FIND_COIL_GYGP(H02CC.COIL_NO)           GYGP_LEIJI        --工艺改判，累计
,(CASE WHEN (H02CC.CRK_CD1 IS NULL OR Z00C.CD_SEQ = '2') 
AND H02CC.EXTSHAPE_DEC_GRD = '1'
AND H02CC.MATLQLTY_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD
THEN to_char(H02CC.ACT_WGT/1000,'FM990.000') END) GYGP_LEIJI
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD
AND H02CC.CRK_CD1 = '37' THEN 
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_QIPAO         --钢质改判,气泡
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '38' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_FENCENG       --钢质改判,分层
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '42' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_JIAZA         --钢质改判,夹杂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '11' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_BIANLIE       --钢质改判,边裂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '06' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_AOKENG        --钢质改判,凹坑
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '57' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_HENGLIE       --钢质改判,横裂
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '59' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_QITA          --钢质改判,其它(钢质)
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND H02CC.CRK_CD1 = '58' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_ZONGLIE       --钢质改判,纵裂
--,SQ_QD_COMMON.FIND_COIL_GZGP(H02CC.COIL_NO)           GZGP_LEIJI         --钢质改判，累计 
,(CASE WHEN H02CC.EXTSHAPE_DEC_GRD = '1' AND F02SM.SPEC_STL_GRD != H02CC.SPEC_STL_GRD 
AND Z00C.CD_SEQ = '1' THEN
to_char(H02CC.ACT_WGT/1000,'FM990.000') END)  GZGP_LEIJI                     
FROM TBH02_COIL_COMM H02CC
,TBG02_SLAB_COMM G02SC
,TBF02_SPEC_MILL F02SM
,(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.COIL_NO = F02SM.COIL_NO(+)
AND   H02CC.CRK_CD1 = Z00C.SM_CD(+)
AND   H02CC.MISSNO_CLF_CD IS NULL					
AND	  SUBSTR(G02SC.SLAB_NO,3,1) = CDDM.SM_CD	
AND   H02CC.MILL_DTIME  >= ?||'000000'
AND   H02CC.MILL_DTIME  <= ?||'235959'
AND	  H02CC.SPEC_STL_GRD LIKE ?||'%'
ORDER BY 
CDDM.SM_CFNM
,SUBSTR(H02CC.OLD_SAMPL_NO,1,10) 
,SUBSTR(H02CC.OLD_SAMPL_NO,11,3)      						
        ]]>   
    </query> 
</queryMap>