XinYu_RZMES/.svn/pristine/4a/4a5e411f9fc3baad6359bd309b4bfdcc80a0dce3.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="废品详情查询">
  
	
	
	<query id="UIB030300_01.SELECT">
	<![CDATA[
		SELECT SUBSTR(T2.CC_DEVNO,2,1)||'#连铸机'       ZJH       --连铸机号
       ,T1.STL_GRD                                     PH    --牌号
       ,SUBSTR(T1.SLAB_NO, 1, 10)                   LH    --炉次号
       ,T1.SLAB_NO                                     BPH       --板坯号
       ,(SELECT SM_CFNM FROM TBZ00_COMMCD  WHERE SM_CD = T1.CRK_CD1 AND LG_CD = 'G03004')   FPYY --废品原因      
       ,T1.SLAB_TOT_DEC_GRD_DTIME                             PDSJ    --判定时间                
       ,(T1.SLAB_WGT)/1000                                   FPZL    --废品重量
    FROM TBG02_SLAB_COMM T1,TBG02_CHARGE_COMM T2 
    WHERE SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO(+),1,9)       
        AND (T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8')
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) >= :1
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) <= :2
        AND T1.CRK_CD1 IN (SELECT SM_CD FROM TBZ00_COMMCD  WHERE SM_CFNM LIKE ('%'||:3||'%') AND LG_CD = 'G03004')
        AND NVL(T2.CC_DEVNO,'NULL') LIKE :4
     ORDER BY T2.CC_DEVNO,SUBSTR(T1.SLAB_NO, 1, 10),T1.SLAB_NO
		 ]]>   
	</query>
	
	
	<query id="UIB030300_02.SELECT">
	<![CDATA[
		SELECT SUBSTR(T2.CC_DEVNO,2,1)||'#连铸机'       ZJH       --连铸机号
       ,T1.STL_GRD                                     PH    --牌号
       ,SUBSTR(T1.SLAB_NO, 1, 10)                   LH    --炉次号
       ,T1.SLAB_NO                                     BPH       --板坯号
       ,NVL((SELECT SM_CFNM FROM TBZ00_COMMCD  WHERE SM_CD = T1.CRK_CD1 AND LG_CD = 'G03004'),'未知原因')   FPYY --废品原因      
       ,T1.SLAB_TOT_DEC_GRD_DTIME                             PDSJ    --判定时间                
       ,(T1.SLAB_WGT)/1000                                   FPZL    --废品重量
    FROM TBG02_SLAB_COMM T1,TBG02_CHARGE_COMM T2 
    WHERE SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO(+),1,9)       
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) >= :1
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) <= :2
        AND (((T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8') 
        AND (NVL(T1.SLAB_INGR_DEC_GRD_EXT,'K') <> '8' AND NVL(T1.SLAB_INGR_DEC_GRD_EXT,'K') <> '7' 
        AND T1.CRK_CD1 IS NULL)) OR
        (((T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8') /*AND T1.EXTSHAPE_GRD_EXT = '8'*/) 
        AND ((T1.CRK_CD1 <> '02' AND T1.CRK_CD1 <> '03' AND T1.CRK_CD1 <> '04' AND 
        	 T1.CRK_CD1 <> '07' AND T1.CRK_CD1 <> '09' AND T1.CRK_CD1 <> '10' AND
             T1.CRK_CD1 <> '14' AND T1.CRK_CD1 <> '18' AND T1.CRK_CD1 <> '30' AND
             T1.CRK_CD1 <> '31' AND T1.CRK_CD1 <> '32' AND T1.CRK_CD1 <> '33' AND
             T1.CRK_CD1 <> '34' AND T1.CRK_CD1 <> '35' AND T1.CRK_CD1 <> '36' AND 
             T1.CRK_CD1 <> '44' AND T1.CRK_CD1 <> '47' AND T1.CRK_CD1 <> '48' AND 
             T1.CRK_CD1 <> '49' AND T1.CRK_CD1 <> '50'))))
        AND NVL(T2.CC_DEVNO,'NULL') LIKE :3
     ORDER BY T2.CC_DEVNO,SUBSTR(T1.SLAB_NO, 1, 10),T1.SLAB_NO
		 ]]>   
	</query>
	
	
	<query id="UIB030300_03.SELECT">
	<![CDATA[
		SELECT SUBSTR(T2.CC_DEVNO,2,1)||'#连铸机'       ZJH       --连铸机号
       ,T1.STL_GRD                                     PH    --牌号
       ,SUBSTR(T1.SLAB_NO, 1, 10)                   LH    --炉次号
       ,T1.SLAB_NO                                     BPH       --板坯号
       ,CASE WHEN ((T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8') AND  (T1.SLAB_INGR_DEC_GRD_EXT = '7' OR T1.SLAB_INGR_DEC_GRD_EXT = '8')  AND T1.CRK_CD1 IS NULL   )
             THEN  '化废'
             WHEN (SELECT SM_CFNM FROM TBZ00_COMMCD  WHERE SM_CD = T1.CRK_CD1 AND LG_CD = 'G03004') IS NULL
             THEN  '未知原因'
             ELSE (SELECT SM_CFNM FROM TBZ00_COMMCD  WHERE SM_CD = T1.CRK_CD1 AND LG_CD = 'G03004') END   FPYY --废品原因      
       ,T1.SLAB_TOT_DEC_GRD_DTIME                             PDSJ    --判定时间                
       ,(T1.SLAB_WGT)/1000                                   FPZL    --废品重量
    FROM TBG02_SLAB_COMM T1,TBG02_CHARGE_COMM T2 
    WHERE SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO(+),1,9)       
        AND (T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8')
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) >= :1
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) <= :2
        AND NVL(T2.CC_DEVNO,'NULL') LIKE :3
     ORDER BY T2.CC_DEVNO,SUBSTR(T1.SLAB_NO, 1, 10),T1.SLAB_NO
		 ]]>   
	</query>
	<query id="UIB030300_04.SELECT">
	<![CDATA[
		SELECT SUBSTR(T2.CC_DEVNO,2,1)||'#连铸机'       ZJH       --连铸机号
       ,T1.STL_GRD                                     PH    --牌号
       ,SUBSTR(T1.SLAB_NO, 1, 10)                   LH    --炉次号
       ,T1.SLAB_NO                                     BPH       --板坯号
       ,'化废'   FPYY --废品原因      
       ,T1.SLAB_TOT_DEC_GRD_DTIME                             PDSJ    --判定时间                
       ,(T1.SLAB_WGT)/1000                                   FPZL    --废品重量
    FROM TBG02_SLAB_COMM T1,TBG02_CHARGE_COMM T2 
    WHERE SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO(+),1,9)       
        AND (T1.SLAB_TOT_DEC_GRD = '7' OR T1.SLAB_TOT_DEC_GRD = '8')
        AND (T1.SLAB_INGR_DEC_GRD_EXT ='8' OR T1.SLAB_INGR_DEC_GRD_EXT ='7')
        AND T1.CRK_CD1 IS NULL
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) >= :1
        AND SUBSTR(T1.SLAB_TOT_DEC_GRD_DTIME ,1,8 ) <= :2
        AND NVL(T2.CC_DEVNO,'NULL') LIKE :3
     ORDER BY T2.CC_DEVNO,SUBSTR(T1.SLAB_NO, 1, 10),T1.SLAB_NO
		 ]]>   
	</query>
</queryMap>