XinYu_RZMES/.svn/pristine/3d/3dc7b0f2c0c40900413834b969a0a880e927edcf.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="材料进程分析现状">
	<query id="UIE012060.Slab-Coil.Select" desc="" fetchSize="10">
		<![CDATA[
			SELECT (NVL(A.MAT_WGT, 0) + NVL(B.MAT_WGT, 0)) MAT_WGT,
				A.L_SLAB_WGT, A.Z_SLAB_WGT,
				B.SBB, B.SCB, B.SDB, B.SED, B.SFA, B.SFB,
				DECODE(C.FLAG, 1, '订单材', 2, '余材', '在库天数') FLAG,
				C.NAME
			FROM (
				SELECT SUM(SLAB_WGT) MAT_WGT,
					SUM(DECODE(SUBSTR(CUR_LOAD_LOC, 1, 1), 'Z', '', SLAB_WGT)) L_SLAB_WGT,
					SUM(DECODE(SUBSTR(CUR_LOAD_LOC, 1, 1), 'Z', SLAB_WGT, '')) Z_SLAB_WGT,
					DECODE(ORD_FL, 1, 'XB0', NVL(NONORD_CAUSE_CD, 'NON')) NONORD_CAUSE_CD
				FROM TBG02_SLAB_COMM
				WHERE SLAB_STAT = '2'
				  AND (ORD_FL = '2' OR (ORD_FL = '1' AND CUR_PROG_CD = 'RBA'))
				  AND STL_GRD LIKE ?||'%'		-- 参数：钢号
				GROUP BY ORD_FL, NONORD_CAUSE_CD
				UNION ALL
				SELECT SUM(SLAB_WGT),
					SUM(DECODE(SUBSTR(CUR_LOAD_LOC, 1, 1), 'Z', '', SLAB_WGT)) L_SLAB_WGT,
					SUM(DECODE(SUBSTR(CUR_LOAD_LOC, 1, 1), 'Z', SLAB_WGT, '')) Z_SLAB_WGT,
					CASE
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) <= 1 THEN 'XB1'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 4 THEN 'XB2'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 6 THEN 'XB3'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 8 THEN 'XB4'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 11 THEN 'XB5'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 16 THEN 'XB6'
					ELSE 'XB7' END
				FROM TBG02_SLAB_COMM
				WHERE SLAB_STAT = '2'
				  AND (ORD_FL = '2' OR (ORD_FL = '1' AND CUR_PROG_CD = 'RBA'))
				  AND STL_GRD LIKE ?||'%'		-- 参数：钢号
				GROUP BY CASE
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) <= 1 THEN 'XB1'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 4 THEN 'XB2'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 6 THEN 'XB3'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 8 THEN 'XB4'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 11 THEN 'XB5'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 16 THEN 'XB6'
					ELSE 'XB7' END) A, (
				SELECT SUM(ACT_WGT) MAT_WGT,
					SUM(DECODE(CUR_PROG_CD, 'SBB', ACT_WGT, '')) SBB,
					SUM(DECODE(CUR_PROG_CD, 'SCB', ACT_WGT, '')) SCB,
					SUM(DECODE(CUR_PROG_CD, 'SDB', ACT_WGT, '')) SDB,
					SUM(DECODE(CUR_PROG_CD, 'SED', ACT_WGT, '')) SED,
					SUM(DECODE(CUR_PROG_CD, 'SFA', ACT_WGT, '')) SFA,
					SUM(DECODE(CUR_PROG_CD, 'SFB', ACT_WGT, '')) SFB,
					DECODE(ORD_FL, 1, 'XB0', NVL(NONORD_CAUSE_CD, 'NON')) NONORD_CAUSE_CD
				FROM TBH02_COIL_COMM
				WHERE COIL_STAT = '2'
				  AND CUR_PROG_CD in ('SBB', 'SCB', 'SDB', 'SED', 'SFA', 'SFB')
				  AND STL_GRD LIKE ?||'%'		-- 参数：钢号
				  AND COIL_THK BETWEEN ? AND ?	-- 参数：厚度
				  AND COIL_WTH BETWEEN ? AND ?	-- 参数：宽度
				GROUP BY ORD_FL, NONORD_CAUSE_CD
				UNION ALL
				SELECT SUM(ACT_WGT),
					SUM(DECODE(CUR_PROG_CD, 'SBB', ACT_WGT, '')) SBB,
					SUM(DECODE(CUR_PROG_CD, 'SCB', ACT_WGT, '')) SCB,
					SUM(DECODE(CUR_PROG_CD, 'SDB', ACT_WGT, '')) SDB,
					SUM(DECODE(CUR_PROG_CD, 'SED', ACT_WGT, '')) SED,
					SUM(DECODE(CUR_PROG_CD, 'SFA', ACT_WGT, '')) SFA,
					SUM(DECODE(CUR_PROG_CD, 'SFB', ACT_WGT, '')) SFB,
					CASE
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) <= 1 THEN 'XB1'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 4 THEN 'XB2'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 6 THEN 'XB3'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 8 THEN 'XB4'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 11 THEN 'XB5'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 16 THEN 'XB6'
					ELSE 'XB7' END
				FROM TBH02_COIL_COMM
				WHERE COIL_STAT = '2'
				  AND CUR_PROG_CD in ('SBB', 'SCB', 'SDB', 'SED', 'SFA', 'SFB')
				  AND STL_GRD LIKE ?||'%'		-- 参数：钢号
				  AND COIL_THK BETWEEN ? AND ?	-- 参数：厚度
				  AND COIL_WTH BETWEEN ? AND ?	-- 参数：宽度
				GROUP BY CASE
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) <= 1 THEN 'XB1'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 4 THEN 'XB2'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 6 THEN 'XB3'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 8 THEN 'XB4'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 11 THEN 'XB5'
					WHEN TRUNC(SYSDATE - TO_DATE(SUBSTR(CUR_PROG_CD_DTIME, 1, 8), 'YYYYMMDD')) < 16 THEN 'XB6'
					ELSE 'XB7' END) B, (
				SELECT 1 FLAG, 1 SEQ, 'XB0' NONORD_CAUSE_CD, '正常物料' NAME FROM DUAL
				UNION ALL
				SELECT * FROM (
					SELECT 2, CD_SEQ, SM_CD, SM_CFNM FROM TBZ00_COMMCD
					WHERE LG_CD = 'E01010'
					ORDER BY CD_SEQ)
				UNION ALL
				SELECT 2, 1, 'NON', '无原因(非法)' NAME FROM DUAL
				UNION ALL
				SELECT 3, 1, 'XB1', '1天' FROM DUAL
				UNION ALL
				SELECT 3, 2, 'XB2', '2~3天' FROM DUAL
				UNION ALL
				SELECT 3, 3, 'XB3', '4~5天' FROM DUAL
				UNION ALL
				SELECT 3, 4, 'XB4', '6~7天' FROM DUAL
				UNION ALL
				SELECT 3, 5, 'XB5', '8~10天' FROM DUAL
				UNION ALL
				SELECT 3, 6, 'XB6', '11~15天' FROM DUAL
				UNION ALL
				SELECT 3, 7, 'XB7', '15天以上' FROM DUAL) C
			WHERE C.NONORD_CAUSE_CD = A.NONORD_CAUSE_CD(+)
			  AND C.NONORD_CAUSE_CD = B.NONORD_CAUSE_CD(+)
			  AND (A.NONORD_CAUSE_CD IS NOT NULL OR B.NONORD_CAUSE_CD IS NOT NULL)
			ORDER BY C.FLAG, C.SEQ
		]]>
	</query> 
</queryMap>