XinYu_RZMES/.svn/pristine/fd/fd8ce48118dbd05d912d4adce0ef2c8f42986151.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="炼钢非计划统计">
  <query id="UIG070040_01.SELECT" desc="一钢库炼钢非计划统计">
	   <![CDATA[
	   	SELECT TT.* FROM (SELECT '一炼钢厂'DW,'一炼钢厂'CCMID,'板坯总计' STEELCODE,'合计'ISFJH,ROUND(SUM(T.THEORYWEIGHT),3)YLKC FROM KCG_STUFFLIST@XGCX T WHERE T.STORAGESTATUS='1' AND T.SLAB_SOURCE='4001LG0' AND T.PLATETYPE='1'
		AND T.STATE IN ('0','3')
		UNION
		SELECT ''DW,'一炼钢厂'CCMID,'有计划料' STEELCODE,'小计'ISFJH,ROUND(SUM(T.THEORYWEIGHT),3)YLKC FROM KCG_STUFFLIST@XGCX T WHERE T.STORAGESTATUS='1' AND T.ORDERNO1 IS NOT NULL AND T.SLAB_SOURCE='4001LG0' AND T.PLATETYPE='1'
		AND T.STATE IN ('0','3')
		UNION
		SELECT ''DW,'一炼钢厂'CCMID,'非计划料' STEELCODE,'小计'ISFJH,ROUND(SUM(T.THEORYWEIGHT),3)YLKC FROM KCG_STUFFLIST@XGCX T WHERE T.STORAGESTATUS='1' AND T.ORDERNO1 IS NULL AND T.SLAB_SOURCE='4001LG0' AND T.PLATETYPE='1'
		AND T.STATE IN ('0','3')
		UNION
		SELECT ''DW,T.CCMID|| '#铸机',T.STEELCODE,'非计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCG_STUFFLIST@XGCX T WHERE T.STORAGESTATUS='1' AND T.ORDERNO1 IS NULL AND T.SLAB_SOURCE='4001LG0' AND T.PLATETYPE='1'
		AND T.STATE IN ('0','3')
		GROUP BY T.CCMID,T.STEELCODE
		UNION
		SELECT ''DW,T.CCMID|| '#铸机',T.STEELCODE,'有计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCG_STUFFLIST@XGCX T WHERE T.STORAGESTATUS='1' AND T.ORDERNO1 IS NOT NULL AND T.SLAB_SOURCE='4001LG0' AND T.PLATETYPE='1'
		AND T.STATE IN ('0','3')
		GROUP BY T.CCMID,T.STEELCODE)TT
		ORDER BY TT.DW,TT.STEELCODE DESC
        ]]> 
  </query>
  
  <query id="UIG070040_02.SELECT" desc="二钢库炼钢非计划统计">
	   <![CDATA[
	    SELECT TT.* FROM (SELECT '二炼钢厂'DW,'二炼钢厂'CCMID,'板坯总计' STEELCODE,'合计'ISFJH,ROUND(sum(slab_wgt) / 1000,3)YLKC FROM tbb02_stat_sms T WHERE T.yn_inkc = 'Y' and T.cc_devno is not null
		UNION
		SELECT ''DW,'二炼钢厂'CCMID,'有计划料' STEELCODE,'小计'ISFJH,ROUND(sum(slab_wgt) / 1000,3)YLKC FROM tbb02_stat_sms T WHERE T.yn_inkc = 'Y' and T.cc_devno is not null AND yn_fjh='Y'
		UNION
		SELECT ''DW,'二炼钢厂'CCMID,'非计划料' STEELCODE,'小计'ISFJH,ROUND(sum(slab_wgt) / 1000,3)YLKC FROM tbb02_stat_sms T WHERE T.yn_inkc = 'Y' and T.cc_devno is not null AND yn_fjh='N'
		UNION
		SELECT ''DW,replace(cc_devno, 'J') || '#铸机' ccmid,stl_grd steelcode,'非计划料'ISFJH,ROUND(sum(slab_wgt) / 1000,3)YLKC FROM tbb02_stat_sms T WHERE T.yn_inkc = 'Y' and T.cc_devno is not null AND yn_fjh='N'
		GROUP BY T.cc_devno,T.stl_grd
		UNION
		SELECT ''DW,replace(cc_devno, 'J') || '#铸机' ccmid,stl_grd steelcode,'有计划料'ISFJH,ROUND(sum(slab_wgt) / 1000,3)YLKC FROM tbb02_stat_sms T WHERE T.yn_inkc = 'Y' and T.cc_devno is not null AND yn_fjh='Y'
		GROUP BY T.cc_devno,T.stl_grd)TT
		ORDER BY TT.DW,TT.STEELCODE DESC
        ]]> 
  </query>
  
  
  <query id="UIG070040_04.SELECT" desc="厚板非计划统计">
	   <![CDATA[
	   	SELECT TT.* FROM (SELECT '厚板线'DW,''CCMID,'总计' STEELCODE,'合计'ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCH_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002'
		UNION
		SELECT ''DW,''CCMID,'有计划料' STEELCODE,'小计'ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCH_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NOT NULL
		UNION
		SELECT ''DW,''CCMID,'非计划料' STEELCODE,'小计'ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCH_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NULL
		UNION
		SELECT ''DW,''CCMID,T.BIGSHOPSIGN STEELCODE,'非计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCH_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NULL
		GROUP BY T.BIGSHOPSIGN
		UNION
		SELECT ''DW,''CCMID,T.BIGSHOPSIGN STEELCODE,'有计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCH_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NOT NULL
		GROUP BY T.BIGSHOPSIGN)TT
		ORDER BY TT.DW,TT.STEELCODE DESC
        ]]> 
  </query>
  
  <query id="UIG070040_03.SELECT" desc="中板非计划统计">
	   <![CDATA[
	   	SELECT TT.* FROM (SELECT '中板线'DW,''CCMID,'总计' STEELCODE,''ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCZ_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002'
		UNION
		SELECT ''DW,''CCMID,'有计划料' STEELCODE,''ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCZ_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NOT NULL
		UNION
		SELECT ''DW,''CCMID,'非计划料' STEELCODE,''ISFJH,SUM(T.THEORYWEIGHT)YLKC FROM KCZ_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NULL
		UNION
		SELECT ''DW,''CCMID,T.BIGSHOPSIGN STEELCODE,'非计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCZ_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NULL
		GROUP BY T.BIGSHOPSIGN
		UNION
		SELECT ''DW,''CCMID,T.BIGSHOPSIGN STEELCODE,'有计划料'ISFJH,SUM(T.THEORYWEIGHT) YLKC FROM KCZ_STORAGELIST@XGCX T WHERE T.STORAGESTATUS='501002' AND T.ORDERNOS IS NOT NULL
		GROUP BY T.BIGSHOPSIGN)TT
		ORDER BY TT.DW,TT.STEELCODE DESC
        ]]> 
  </query>
    
</queryMap>