XinYu_RZMES/.svn/pristine/5a/5a188ea030c2e74b4072674359f01a465921238d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="综合判定结果查询">
	
<query id="UIB020260_01.SELECT" desc="试样进程现状">
	<![CDATA[	
		SELECT * FROM 
		(SELECT TO_CHAR(SUM(DECODE(T1.COIL_NO,NULL,'',1))) CNT1              --轧钢指示(钢卷数)
			   ,TO_CHAR(SUM(DECODE(UPPER(T1.SMP_DIR_PIC_YN),'Y',1,''))) CNT2 --采样指示(钢卷数)
			   ,TO_CHAR(SUM(DECODE(UPPER(T1.SAMPL_PICK_YN),'Y',1,''))) CNT3  --采样(钢卷数)
			   FROM TBH02_COIL_COMM T1 
			   WHERE T1.MILL_DTIME LIKE ?||'%'
		) T1,
		--复样采集原因
		(SELECT TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'1',1,''))) CNT4_1         --未采样
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'2',1,''))) CNT4_2         --样片未入库  
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'3',1,''))) CNT4_3         --样片不良
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'4',1,''))) CNT4_4         --材质不合格
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'5',1,''))) CNT4_5         --其他
		FROM TBB02_LOT_COM T1
		WHERE T1. SMP_DIR_DTIME LIKE ?||'%'
		) T2
		UNION ALL
		SELECT * FROM 
		(SELECT TO_CHAR(SUM(DECODE(T1.COIL_NO,NULL,'',1))) CNT1              --轧钢指示(钢卷数)
			   ,TO_CHAR(SUM(DECODE(UPPER(T1.SMP_DIR_PIC_YN),'Y',1,''))) CNT2 --采样指示(钢卷数)
			   ,TO_CHAR(SUM(DECODE(UPPER(T1.SAMPL_PICK_YN),'Y',1,''))) CNT3  --采样(钢卷数)
			   FROM TBH02_COIL_COMM T1 
			   WHERE T1.MILL_DTIME LIKE SUBSTR(?,1,6)||'%'
		) T1,
		--复样采集原因
		(SELECT TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'1',1,''))) CNT4_1         --未采样
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'2',1,''))) CNT4_2         --样片未入库  
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'3',1,''))) CNT4_3         --样片不良
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'4',1,''))) CNT4_4         --材质不合格
			   ,TO_CHAR(SUM(DECODE(T1.OVER_SMP_TP,'5',1,''))) CNT4_5         --其他
		FROM TBB02_LOT_COM T1
		WHERE T1. SMP_DIR_DTIME LIKE SUBSTR(?,1,6)||'%'
		) T2
	]]>
</query>
	
	
<query id="UIB020260_02.SELECT" desc="材质判定">
	<![CDATA[	
			 SELECT TO_CHAR(SUM(DECODE(T1.COIL_NO,NULL,'',1))) CNT1          --材质判定(钢卷数)
 			,TO_CHAR(SUM(DECODE(T1.MATLQLTY_DEC_GRD,'1',1,''))) CNT2  --材质判定合格(钢卷数)
 			,TO_CHAR(SUM(DECODE(T1.MATLQLTY_DEC_GRD,'2',1,''))) CNT4  --材质判定不合格(钢卷数)
			,TO_CHAR((SUM(DECODE(T1.MATLQLTY_DEC_GRD,'1',1,''))/SUM(DECODE(T1.COIL_NO,NULL,'',1)))*100,'999.99')||'%' AS CNT3
			 FROM TBH02_COIL_COMM T1
			 WHERE T1.MATLQLTY_DEC_DTIME LIKE ?||'%'
				UNION ALL
			 SELECT TO_CHAR(SUM(DECODE(T1.COIL_NO,NULL,'',1))) CNT1          --材质判定(钢卷数)
 			,TO_CHAR(SUM(DECODE(T1.MATLQLTY_DEC_GRD,'1',1,''))) CNT2  --材质判定合格(钢卷数)
 			,TO_CHAR(SUM(DECODE(T1.MATLQLTY_DEC_GRD,'2',1,''))) CNT4  --材质判定不合格(钢卷数)
			,TO_CHAR((SUM(DECODE(T1.MATLQLTY_DEC_GRD,'1',1,''))/SUM(DECODE(T1.COIL_NO,NULL,'',1)))*100,'999.99')||'%' AS CNT3
			 FROM TBH02_COIL_COMM T1
			 WHERE T1.MATLQLTY_DEC_DTIME LIKE SUBSTR(?,1,6)||'%'	
	]]>
</query>	
	
<query id="UIB020260_03.SELECT" desc="不合格原因现况(日)">
	<![CDATA[	
			 SELECT T3.QLTY_CD_CFNM QLTY_CD,TO_CHAR(COUNT(T1.COIL_NO)) CNT
			 FROM TBH02_COIL_COMM T1,TBB02_DCS_QLTY T2,TBB01_SEQ_QLTY T3
		   	 WHERE T1.COIL_NO = T2.COIL_NO
			   AND T2.QLTY_CD = T3.QLTY_CD
			   AND T1.MATLQLTY_DEC_KIND = '2'
			   AND T2.QLTY_DCS_RST = '2'
			   AND T1.MATLQLTY_DEC_DTIME LIKE ?||'%' 
			   GROUP BY T3.QLTY_CD_CFNM 
			   ORDER BY CNT DESC 
	]]>
</query>
		
<query id="UIB020260_04.SELECT" desc="不合格原因现况（月）">
	<![CDATA[		
		       SELECT T3.QLTY_CD_CFNM QLTY_CD,TO_CHAR(COUNT(T1.COIL_NO)) CNT
			   FROM TBH02_COIL_COMM T1,TBB02_DCS_QLTY T2,TBB01_SEQ_QLTY T3
		   	   WHERE T1.COIL_NO = T2.COIL_NO
	     	   AND T2.QLTY_CD = T3.QLTY_CD
			   AND T1.MATLQLTY_DEC_KIND = '2'
			   AND T2.QLTY_DCS_RST = '2'
			   AND T1.MATLQLTY_DEC_DTIME LIKE SUBSTR(?,1,6)||'%' 
			   GROUP BY T3.QLTY_CD_CFNM 
			   ORDER BY CNT DESC 
	]]>	
</query>		
	
</queryMap>