XinYu_RZMES/.svn/pristine/0c/0cb8835e958d1136b2c5236d81b121738496563a.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="板坏设计组合与分解">
	<query id="UID010060_1" desc="板坏设计结果钢卷厚度分析">
        <![CDATA[
SELECT DECODE(C.RN,1,A.LINE_NO ,NULL) AS LINE_NO
     , DECODE(C.RN,1,TO_CHAR(A.FR_VALUE,'FM90.0'),NULL) AS FR_VALUE
     , DECODE(C.RN,1,TO_CHAR(A.TO_VALUE,'FM90.0'),NULL) AS TO_VALUE
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_01 ELSE NULL END) AS CNT_01
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_02 ELSE NULL END) AS CNT_02
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_03 ELSE NULL END) AS CNT_03
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_04 ELSE NULL END) AS CNT_04
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_05 ELSE NULL END) AS CNT_05
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_06 ELSE NULL END) AS CNT_06
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_07 ELSE NULL END) AS CNT_07
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_08 ELSE NULL END) AS CNT_08
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_09 ELSE NULL END) AS CNT_09
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_10 ELSE NULL END) AS CNT_10
     , SUM(CASE WHEN (A.FR_VALUE < B.COIL_THK AND B.COIL_THK <= A.TO_VALUE) THEN B.CNT_AA ELSE NULL END) AS CNT_AA
FROM (SELECT ROWNUM                                AS LINE_NO
           , X.RNUM                                AS FR_VALUE
           , LEAD(X.RNUM,1) OVER (ORDER BY X.RNUM) AS TO_VALUE
      FROM (SELECT 0 AS RNUM  FROM TBZ00_COPY WHERE RNUM  = 001
            UNION ALL
            SELECT RNUM * :1  FROM TBZ00_COPY WHERE RNUM <= 160) X
          ,(SELECT MIN(NUM_MIN) AS MIN_VALUE, MAX(NUM_MAX) AS MAX_VALUE FROM TBD00_MATDN_STD  WHERE BIZ_TP = 'CoilThickGroup') Y
      WHERE X.RNUM = 0 OR (Y.MIN_VALUE <= X.RNUM AND X.RNUM <= Y.MAX_VALUE)) A
    ,(SELECT X.COIL_THK
           , SUM(CASE WHEN (                      X.SLAB_WTH <=  700) THEN 1 ELSE NULL END) AS CNT_01
           , SUM(CASE WHEN ( 700 < X.SLAB_WTH AND X.SLAB_WTH <=  800) THEN 1 ELSE NULL END) AS CNT_02
           , SUM(CASE WHEN ( 800 < X.SLAB_WTH AND X.SLAB_WTH <=  900) THEN 1 ELSE NULL END) AS CNT_03
           , SUM(CASE WHEN ( 900 < X.SLAB_WTH AND X.SLAB_WTH <= 1000) THEN 1 ELSE NULL END) AS CNT_04
           , SUM(CASE WHEN (1000 < X.SLAB_WTH AND X.SLAB_WTH <= 1100) THEN 1 ELSE NULL END) AS CNT_05
           , SUM(CASE WHEN (1100 < X.SLAB_WTH AND X.SLAB_WTH <= 1200) THEN 1 ELSE NULL END) AS CNT_06
           , SUM(CASE WHEN (1200 < X.SLAB_WTH AND X.SLAB_WTH <= 1300) THEN 1 ELSE NULL END) AS CNT_07
           , SUM(CASE WHEN (1300 < X.SLAB_WTH AND X.SLAB_WTH <= 1400) THEN 1 ELSE NULL END) AS CNT_08
           , SUM(CASE WHEN (1400 < X.SLAB_WTH AND X.SLAB_WTH <= 1430) THEN 1 ELSE NULL END) AS CNT_09
           , SUM(CASE WHEN (1430 < X.SLAB_WTH                       ) THEN 1 ELSE NULL END) AS CNT_10
           , COUNT(*) AS CNT_AA
      FROM TBD01_SLABDN_RST X
      WHERE X.P_DSN_SLAB_NO IS NULL
      AND 1 = :2
      GROUP BY X.COIL_THK
      UNION ALL
      SELECT X.COIL_THK
           , SUM(CASE WHEN (                      X.SLAB_WTH <=  700) THEN X.SLAB_WGT ELSE NULL END) AS CNT_01
           , SUM(CASE WHEN ( 700 < X.SLAB_WTH AND X.SLAB_WTH <=  800) THEN X.SLAB_WGT ELSE NULL END) AS CNT_02
           , SUM(CASE WHEN ( 800 < X.SLAB_WTH AND X.SLAB_WTH <=  900) THEN X.SLAB_WGT ELSE NULL END) AS CNT_03
           , SUM(CASE WHEN ( 900 < X.SLAB_WTH AND X.SLAB_WTH <= 1000) THEN X.SLAB_WGT ELSE NULL END) AS CNT_04
           , SUM(CASE WHEN (1000 < X.SLAB_WTH AND X.SLAB_WTH <= 1100) THEN X.SLAB_WGT ELSE NULL END) AS CNT_05
           , SUM(CASE WHEN (1100 < X.SLAB_WTH AND X.SLAB_WTH <= 1200) THEN X.SLAB_WGT ELSE NULL END) AS CNT_06
           , SUM(CASE WHEN (1200 < X.SLAB_WTH AND X.SLAB_WTH <= 1300) THEN X.SLAB_WGT ELSE NULL END) AS CNT_07
           , SUM(CASE WHEN (1300 < X.SLAB_WTH AND X.SLAB_WTH <= 1400) THEN X.SLAB_WGT ELSE NULL END) AS CNT_08
           , SUM(CASE WHEN (1400 < X.SLAB_WTH AND X.SLAB_WTH <= 1430) THEN X.SLAB_WGT ELSE NULL END) AS CNT_09
           , SUM(CASE WHEN (1430 < X.SLAB_WTH                       ) THEN X.SLAB_WGT ELSE NULL END) AS CNT_10
           , SUM(X.SLAB_WGT) AS CNT_AA
      FROM TBD01_SLABDN_RST X
      WHERE X.P_DSN_SLAB_NO IS NULL
      AND 2 = :3
      GROUP BY X.COIL_THK) B
    ,(SELECT RNUM AS RN FROM TBZ00_COPY WHERE RNUM <= 2) C
GROUP BY DECODE(C.RN,1,A.LINE_NO ,NULL)
       , DECODE(C.RN,1,TO_CHAR(A.FR_VALUE,'FM90.0'),NULL)
       , DECODE(C.RN,1,TO_CHAR(A.TO_VALUE,'FM90.0'),NULL)
ORDER BY 1,2,3 NULLS LAST
        ]]>
    </query>
	<query id="UID010060_2" desc="板坏设计结果去向分析">
        <![CDATA[
          SELECT DECODE(Z.RN,1,X.SM_CD  ,NULL) AS PRODNM_CD
    			 , DECODE(Z.RN,1,X.SM_CFNM,'合计') AS PRODNM_NM
				 , SUM(Y.CNT_01)                 AS CNT_01
				 , SUM(Y.CNT_02)                 AS CNT_02
				 , SUM(Y.CNT_03)                 AS CNT_03
				 , SUM(Y.CNT_04)                 AS CNT_04
				 , SUM(Y.CNT_05)                 AS CNT_05
				 , SUM(Y.CNT_06)                 AS CNT_06
				 , SUM(Y.CNT_07)                 AS CNT_07
				 , SUM(Y.CNT_08)                 AS CNT_08
				 , SUM(Y.CNT_09)                 AS CNT_09
				 , SUM(Y.CNT_10)                 AS CNT_10
				 , SUM(Y.CNT_AA)                 AS CNT_AA
			FROM (SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01004' AND USED_YN = 'Y') X
			   , (SELECT B.PRODNM_CD AS PRODNM_CD
					   , SUM(CASE WHEN (                      A.SLAB_WTH <=  700) THEN 1 ELSE NULL END) AS CNT_01
					   , SUM(CASE WHEN ( 700 < A.SLAB_WTH AND A.SLAB_WTH <=  800) THEN 1 ELSE NULL END) AS CNT_02
					   , SUM(CASE WHEN ( 800 < A.SLAB_WTH AND A.SLAB_WTH <=  900) THEN 1 ELSE NULL END) AS CNT_03
					   , SUM(CASE WHEN ( 900 < A.SLAB_WTH AND A.SLAB_WTH <= 1000) THEN 1 ELSE NULL END) AS CNT_04
					   , SUM(CASE WHEN (1000 < A.SLAB_WTH AND A.SLAB_WTH <= 1100) THEN 1 ELSE NULL END) AS CNT_05
					   , SUM(CASE WHEN (1100 < A.SLAB_WTH AND A.SLAB_WTH <= 1200) THEN 1 ELSE NULL END) AS CNT_06
					   , SUM(CASE WHEN (1200 < A.SLAB_WTH AND A.SLAB_WTH <= 1300) THEN 1 ELSE NULL END) AS CNT_07
					   , SUM(CASE WHEN (1300 < A.SLAB_WTH AND A.SLAB_WTH <= 1400) THEN 1 ELSE NULL END) AS CNT_08
					   , SUM(CASE WHEN (1400 < A.SLAB_WTH AND A.SLAB_WTH <= 1430) THEN 1 ELSE NULL END) AS CNT_09
					   , SUM(CASE WHEN (1430 < A.SLAB_WTH                       ) THEN 1 ELSE NULL END) AS CNT_10
					   , COUNT(*)                                                                       AS CNT_AA
				  FROM TBD01_SLABDN_RST A
					 , TBE02_ORD_PRC B
				  WHERE A.P_DSN_SLAB_NO IS NULL
			      AND 1 = :1
				  AND A.ORD_NO = B.ORD_NO(+)
				  AND A.ORD_SEQ = B.ORD_SEQ(+)
				  GROUP BY B.PRODNM_CD
				  UNION ALL
				SELECT B.PRODNM_CD AS PRODNM_CD
					   , SUM(CASE WHEN (                      A.SLAB_WTH <=  700) THEN A.SLAB_WGT ELSE NULL END) AS CNT_01
					   , SUM(CASE WHEN ( 700 < A.SLAB_WTH AND A.SLAB_WTH <=  800) THEN A.SLAB_WGT ELSE NULL END) AS CNT_02
					   , SUM(CASE WHEN ( 800 < A.SLAB_WTH AND A.SLAB_WTH <=  900) THEN A.SLAB_WGT ELSE NULL END) AS CNT_03
					   , SUM(CASE WHEN ( 900 < A.SLAB_WTH AND A.SLAB_WTH <= 1000) THEN A.SLAB_WGT ELSE NULL END) AS CNT_04
					   , SUM(CASE WHEN (1000 < A.SLAB_WTH AND A.SLAB_WTH <= 1100) THEN A.SLAB_WGT ELSE NULL END) AS CNT_05
					   , SUM(CASE WHEN (1100 < A.SLAB_WTH AND A.SLAB_WTH <= 1200) THEN A.SLAB_WGT ELSE NULL END) AS CNT_06
					   , SUM(CASE WHEN (1200 < A.SLAB_WTH AND A.SLAB_WTH <= 1300) THEN A.SLAB_WGT ELSE NULL END) AS CNT_07
					   , SUM(CASE WHEN (1300 < A.SLAB_WTH AND A.SLAB_WTH <= 1400) THEN A.SLAB_WGT ELSE NULL END) AS CNT_08
					   , SUM(CASE WHEN (1400 < A.SLAB_WTH AND A.SLAB_WTH <= 1430) THEN A.SLAB_WGT ELSE NULL END) AS CNT_09
					   , SUM(CASE WHEN (1430 < A.SLAB_WTH                       ) THEN A.SLAB_WGT ELSE NULL END) AS CNT_10
					   , SUM(A.SLAB_WGT)                                                                       AS CNT_AA
				  FROM TBD01_SLABDN_RST A
					 , TBE02_ORD_PRC B
				  WHERE A.P_DSN_SLAB_NO IS NULL
				  AND 2 = :2
				  AND A.ORD_NO = B.ORD_NO(+)
				  AND A.ORD_SEQ = B.ORD_SEQ(+)
				  GROUP BY B.PRODNM_CD) Y
			   , (SELECT RNUM AS RN FROM TBZ00_COPY WHERE RNUM <= 2) Z
			WHERE X.SM_CD = Y.PRODNM_CD(+)
			GROUP BY DECODE(Z.RN,1,X.SM_CD  ,NULL)
				   , DECODE(Z.RN,1,X.SM_CFNM,'合计')
			ORDER BY 1 NULLS LAST
        ]]>
    </query>
	<query id="UID010060_3" desc="板坏设计结果牌号组分析">
        <![CDATA[
			SELECT DECODE(Z.RN,1,X.FAC_STL_GRP  ,NULL) AS FAC_STL_GRP
  			     , DECODE(Z.RN,1,X.FAC_STL_GRPNM,'合计') AS FAC_STL_GRPNM
				 , SUM(Y.CNT_01)                       AS CNT_01
				 , SUM(Y.CNT_02)                       AS CNT_02
				 , SUM(Y.CNT_03)                       AS CNT_03
				 , SUM(Y.CNT_04)                       AS CNT_04
				 , SUM(Y.CNT_05)                       AS CNT_05
				 , SUM(Y.CNT_06)                       AS CNT_06
				 , SUM(Y.CNT_07)                       AS CNT_07
				 , SUM(Y.CNT_08)                       AS CNT_08
				 , SUM(Y.CNT_09)                       AS CNT_09
				 , SUM(Y.CNT_10)                       AS CNT_10
				 , SUM(Y.CNT_AA)                       AS CNT_AA
			FROM TBD00_STLGRD_GRP X
			   ,(SELECT A.FAC_STL_GRP AS FAC_STL_GRP
					  , SUM(CASE WHEN (                      A.SLAB_WTH <=  700) THEN 1 ELSE NULL END) AS CNT_01
					  , SUM(CASE WHEN ( 700 < A.SLAB_WTH AND A.SLAB_WTH <=  800) THEN 1 ELSE NULL END) AS CNT_02
					  , SUM(CASE WHEN ( 800 < A.SLAB_WTH AND A.SLAB_WTH <=  900) THEN 1 ELSE NULL END) AS CNT_03
					  , SUM(CASE WHEN ( 900 < A.SLAB_WTH AND A.SLAB_WTH <= 1000) THEN 1 ELSE NULL END) AS CNT_04
					  , SUM(CASE WHEN (1000 < A.SLAB_WTH AND A.SLAB_WTH <= 1100) THEN 1 ELSE NULL END) AS CNT_05
					  , SUM(CASE WHEN (1100 < A.SLAB_WTH AND A.SLAB_WTH <= 1200) THEN 1 ELSE NULL END) AS CNT_06
					  , SUM(CASE WHEN (1200 < A.SLAB_WTH AND A.SLAB_WTH <= 1300) THEN 1 ELSE NULL END) AS CNT_07
					  , SUM(CASE WHEN (1300 < A.SLAB_WTH AND A.SLAB_WTH <= 1400) THEN 1 ELSE NULL END) AS CNT_08
					  , SUM(CASE WHEN (1400 < A.SLAB_WTH AND A.SLAB_WTH <= 1430) THEN 1 ELSE NULL END) AS CNT_09
					  , SUM(CASE WHEN (1430 < A.SLAB_WTH                       ) THEN 1 ELSE NULL END) AS CNT_10
					  , COUNT(*)                                                                       AS CNT_AA
				 FROM TBD01_SLABDN_RST A
				 WHERE A.P_DSN_SLAB_NO IS NULL
			     AND 1 = :1
				 GROUP BY A.FAC_STL_GRP
			     UNION ALL
			SELECT A.FAC_STL_GRP AS FAC_STL_GRP
					  , SUM(CASE WHEN (                      A.SLAB_WTH <=  700) THEN A.SLAB_WGT ELSE NULL END) AS CNT_01
					  , SUM(CASE WHEN ( 700 < A.SLAB_WTH AND A.SLAB_WTH <=  800) THEN A.SLAB_WGT ELSE NULL END) AS CNT_02
					  , SUM(CASE WHEN ( 800 < A.SLAB_WTH AND A.SLAB_WTH <=  900) THEN A.SLAB_WGT ELSE NULL END) AS CNT_03
					  , SUM(CASE WHEN ( 900 < A.SLAB_WTH AND A.SLAB_WTH <= 1000) THEN A.SLAB_WGT ELSE NULL END) AS CNT_04
					  , SUM(CASE WHEN (1000 < A.SLAB_WTH AND A.SLAB_WTH <= 1100) THEN A.SLAB_WGT ELSE NULL END) AS CNT_05
					  , SUM(CASE WHEN (1100 < A.SLAB_WTH AND A.SLAB_WTH <= 1200) THEN A.SLAB_WGT ELSE NULL END) AS CNT_06
					  , SUM(CASE WHEN (1200 < A.SLAB_WTH AND A.SLAB_WTH <= 1300) THEN A.SLAB_WGT ELSE NULL END) AS CNT_07
					  , SUM(CASE WHEN (1300 < A.SLAB_WTH AND A.SLAB_WTH <= 1400) THEN A.SLAB_WGT ELSE NULL END) AS CNT_08
					  , SUM(CASE WHEN (1400 < A.SLAB_WTH AND A.SLAB_WTH <= 1430) THEN A.SLAB_WGT ELSE NULL END) AS CNT_09
					  , SUM(CASE WHEN (1430 < A.SLAB_WTH                       ) THEN A.SLAB_WGT ELSE NULL END) AS CNT_10
					  , SUM(A.SLAB_WGT)                                                                       AS CNT_AA
				 FROM TBD01_SLABDN_RST A
				 WHERE A.P_DSN_SLAB_NO IS NULL
			     AND 2 = :2
				 GROUP BY A.FAC_STL_GRP) Y
			   ,(SELECT RNUM AS RN FROM TBZ00_COPY WHERE RNUM <= 2) Z
			WHERE X.FAC_STL_GRP = Y.FAC_STL_GRP(+)
			GROUP BY DECODE(Z.RN,1,X.FAC_STL_GRP  ,NULL)
				   , DECODE(Z.RN,1,X.FAC_STL_GRPNM,'合计')
			ORDER BY 1 NULLS LAST
        ]]>
    </query>
</queryMap>