XinYu_RZMES/.svn/pristine/e7/e7ecba1b60e89a930c8a0c09f27ac826fb19b91f.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="出钢参数接收现况分析(中厚板)">
	<query id="UID050020.findByK" desc="求各炉次所接受的热轧板坯参数" fetchSize="10">
        <![CDATA[
			
/*******************************UID050020.findByK***********************************/

SELECT  T.WTH_GRP_CD
        ,DECODE(T.WGT_WTH_GRP,0,'',T.WGT_WTH_GRP)                                          WGT_WTH_GRP
        ,DECODE(T.RATE_WTH_GRP,'0%','',T.RATE_WTH_GRP)                                     RATE_WTH_GRP
        ,DECODE(T.CNT_KCL,0,'',T.CNT_KCL)                                                  CNT_KCL
        ,DECODE(T.CNT_CUT,0,'',T.CNT_CUT)                                                  CNT_CUT
        ,DECODE(T.CNT_ZSDJ,0,'',T.CNT_ZSDJ)                                                CNT_ZSDJ
        ,CNT_KCL + CNT_CUT + CNT_ZSDJ                                                      CNT_KCJX_SUM                                              
        ,DECODE(T.DXL,0,'',T.DXL)                                                          DXL
       , DECODE(T.WGT_WTH_GRP - T.CNT_KCL + T.DXL,0,'',T.WGT_WTH_GRP - T.CNT_KCL + T.DXL)  FHL
  FROM(
WITH T1 AS (
SELECT   Y.WTH_GRP_CD
        ,NVL(Y.SLAB_CNT,0) *    Z.SUM_ROLL_CNT                                              WGT_WTH_GRP, --预定使用量（块）
        ROUND(Y.SLAB_CNT/ X.WGT_WTH_GRP*100,1)||'%'                                         RATE_WTH_GRP --预定使用率
FROM          (SELECT  SUM(A.SLAB_CNT)                      WGT_WTH_GRP --预定板坯总块数
                 FROM  TBF02_ROLL_WTH_GRP A )     X
             ,TBF02_ROLL_WTH_GRP                  Y
             ,(SELECT  SUM(B.D_00_CNT + B.D_01_CNT + B.D_02_CNT +
                          B.D_03_CNT + B.D_04_CNT + B.D_05_CNT +
                          B.D_06_CNT + B.D_07_CNT + B.D_08_CNT + B.D_09_CNT) SUM_ROLL_CNT
                  FROM TBD00_PLAN_ROLL  B
                WHERE B.PLAN_SEQ = (SELECT MAX(PLAN_SEQ )FROM TBD00_PLAN_ROLL )) Z
UNION ALL
SELECT  '小计'                                                              WTH_GRP_CD , 
        SUM(A.SLAB_CNT)* Z.SUM_ROLL_CNT                                                     WGT_WTH_GRP, --预定板坯总块数
        '100%'                                                              RATE_WTH_GRP                                                            
  FROM  TBF02_ROLL_WTH_GRP A
	   ,(SELECT  SUM(B.D_00_CNT + B.D_01_CNT + B.D_02_CNT +
								  B.D_03_CNT + B.D_04_CNT + B.D_05_CNT +
								  B.D_06_CNT + B.D_07_CNT + B.D_08_CNT + B.D_09_CNT) SUM_ROLL_CNT
			FROM TBD00_PLAN_ROLL  B
		   WHERE B.PLAN_SEQ = (SELECT MAX(PLAN_SEQ )FROM TBD00_PLAN_ROLL )) Z
GROUP BY Z.SUM_ROLL_CNT)

SELECT   T1.WTH_GRP_CD,
         NVL(T1.WGT_WTH_GRP,0)  WGT_WTH_GRP,
         NVL(T1.RATE_WTH_GRP,0) RATE_WTH_GRP,
         NVL(T2.CNT_KCL,0)      CNT_KCL,
         NVL(T3.CNT_CUT,0)      CNT_CUT,
         NVL(T4.CNT_ZSDJ,0)     CNT_ZSDJ,
         NVL(T5.DXL,0)          DXL
        ,T1.WGT_WTH_GRP - T2.CNT_KCL + T5.DXL
  FROM  T1,
        (

            SELECT DECODE(Z.WTH_GRP_CD,NULL,'小计',Z.WTH_GRP_CD)  WTH_GRP_CD,
                   NVL(COUNT(Z.SLAB_NO),0)            CNT_KCL                --库存量
             FROM (
            SELECT C.SLAB_NO,
                   C.INSTR_COIL_WTH,
                   C.ORD_FL           
                   ,(SELECT T.WTH_GRP_CD
                      FROM TBF02_ROLL_WTH_GRP T --宽度组代码
                     WHERE T.PLT = 'H'
                       AND DECODE(ORD_FL,'1',C.INSTR_COIL_WTH,'2',C.SLAB_WTH - 50) >= T.MINI
                       AND DECODE(ORD_FL,'1',C.INSTR_COIL_WTH,'2',C.SLAB_WTH - 50) < T.MAXI ) WTH_GRP_CD
              FROM TBG02_SLAB_COMM C
             WHERE C.SLAB_STAT = '2'
               AND C.SLAB_RT   = 'H'
               AND C.CUR_PROG_CD IN ('RRC','RBA'))Z
            GROUP BY ROLLUP (Z.WTH_GRP_CD) 
            ORDER BY Z.WTH_GRP_CD  ) T2
        ,(
            SELECT DECODE(C.WTH_GRP_CD,NULL,'小计',C.WTH_GRP_CD)  WTH_GRP_CD,
                   COUNT(B.SLAB_EDT_SEQ)                          cnt_cut   --切割待机量
              FROM TBF01_SPEC_CHARGE   A
                   ,TBF01_SPEC_SLAB    B
                   ,TBF02_ROLL_WTH_GRP C
            WHERE  A.CHARGE_EDT_SEQ  =  B.CHARGE_EDT_SEQ
              AND  A.PROG_CD IN ('JBB','JBF','JCR','JRF','JJB')
              AND  A.STATUS_CD IN ('A','B')
              AND  C.PLT       = 'H'
              AND  B.SLAB_RT   = 'H'
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) >= C.MINI
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) <=  C.MAXI
            GROUP BY ROLLUP (WTH_GRP_CD) ) T3
        ,(
            SELECT DECODE(C.WTH_GRP_CD,NULL,'小计',C.WTH_GRP_CD)  WTH_GRP_CD,
                   NVL(COUNT(B.SLAB_EDT_SEQ),0)                          CNT_ZSDJ  --出钢指示待机量
              FROM TBF01_SPEC_CHARGE   A
                   ,TBF01_SPEC_SLAB    B
                   ,TBF02_ROLL_WTH_GRP C
            WHERE  A.CHARGE_EDT_SEQ  =  B.CHARGE_EDT_SEQ
              AND  A.PROG_CD = 'JBA'
              AND  A.STATUS_CD = 'A'
              AND  C.PLT       = 'H'
              AND  B.SLAB_RT   = 'H'
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) >= C.MINI
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) <=  C.MAXI
            GROUP BY ROLLUP (WTH_GRP_CD) ) T4
        ,( 
            SELECT DECODE(Z.WTH_GRP_CD,NULL,'小计',Z.WTH_GRP_CD)       WTH_GRP_CD
                   ,NVL(SUM(Z.DXL),0)  DXL                                     --对象量
              FROM(
                    WITH BB_DXL AS (
                    SELECT  WTH_GRP_CD
                           ,SUM(A.ORD_REM_WGT) ORD_REM_WGT_TOT
                      FROM TBE02_ORD_PRC         A
                           ,TBF02_ROLL_WTH_GRP   B
                    WHERE   A.ORD_PROG_CD IN ('D','E')                  --D 投入确定 --E生产进行
                      AND   B.PLT = 'H'
                      AND   A.ORD_WTH >= B.MINI
                      AND   A.ORD_WTH <B.MAXI
                    GROUP BY   ROLLUP(WTH_GRP_CD))
                    SELECT  T1.WTH_GRP_CD,
                            CEIL(T1.ORD_REM_WGT_TOT/T2.UNIT_SLAB_WGT) DXL --对象量（块）
                      FROM  BB_DXL T1
                           ,TBF02_ROLL_WTH_GRP T2
                     WHERE T1.WTH_GRP_CD = T2.WTH_GRP_CD)Z
                GROUP BY   ROLLUP(Z.WTH_GRP_CD)) T5
WHERE T1.WTH_GRP_CD = T2.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T3.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T4.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T5.WTH_GRP_CD(+)
ORDER BY T1.WTH_GRP_CD ASC)T

/*******************************UID050020.findByK***********************************/

        ]]>
    </query>  
	
<query id="UID050020.findByW" desc="求各炉次所接受的热轧板坯参数" fetchSize="10">
        <![CDATA[
			
/*******************************UID050020.findByW***********************************/

SELECT  T.WTH_GRP_CD
        ,DECODE(T.WGT_WTH_GRP,0,'',T.WGT_WTH_GRP)                                          WGT_WTH_GRP
        ,DECODE(T.RATE_WTH_GRP,'0%','',T.RATE_WTH_GRP)                                     RATE_WTH_GRP
        ,DECODE(T.CNT_KCL,0,'',T.CNT_KCL)                                                  CNT_KCL
        ,DECODE(T.CNT_CUT,0,'',T.CNT_CUT)                                                  CNT_CUT
        ,DECODE(T.CNT_ZSDJ,0,'',T.CNT_ZSDJ)                                                CNT_ZSDJ
        ,CNT_KCL + CNT_CUT + CNT_ZSDJ                                                      CNT_KCJX_SUM                                              
        ,DECODE(T.DXL,0,'',T.DXL)                                                          DXL
       , DECODE(T.WGT_WTH_GRP - T.CNT_KCL + T.DXL,0,'',T.WGT_WTH_GRP - T.CNT_KCL + T.DXL)  FHL
  FROM(
WITH T1 AS (
SELECT   Y.WTH_GRP_CD
        ,NVL(Y.UNIT_SLAB_WGT * Y.SLAB_CNT * Z.SUM_ROLL_CNT,0)                               WGT_WTH_GRP,
        ROUND(Y.UNIT_SLAB_WGT * Y.SLAB_CNT/ X.WGT_WTH_GRP*100,1)||'%'                       RATE_WTH_GRP
FROM (
             SELECT  SUM(A.UNIT_SLAB_WGT * A.SLAB_CNT)                                      WGT_WTH_GRP
               FROM  TBF02_ROLL_WTH_GRP A )     X
             ,TBF02_ROLL_WTH_GRP                Y
             ,(SELECT  SUM(B.D_00_CNT + B.D_01_CNT + B.D_02_CNT +
                                      B.D_03_CNT + B.D_04_CNT + B.D_05_CNT +
                                      B.D_06_CNT + B.D_07_CNT + B.D_08_CNT + B.D_09_CNT) SUM_ROLL_CNT
                              FROM TBD00_PLAN_ROLL  B
                            WHERE B.PLAN_SEQ = (SELECT MAX(PLAN_SEQ )FROM TBD00_PLAN_ROLL )) Z
UNION ALL
SELECT  '小计'                                                              WTH_GRP_CD ,
        SUM(A.UNIT_SLAB_WGT * A.SLAB_CNT) * Z.SUM_ROLL_CNT                  WGT_WTH_GRP,
        '100%'                                                              RATE_WTH_GRP                                                              
  FROM  TBF02_ROLL_WTH_GRP A
		,(SELECT  SUM(B.D_00_CNT + B.D_01_CNT + B.D_02_CNT +
								  B.D_03_CNT + B.D_04_CNT + B.D_05_CNT +
								  B.D_06_CNT + B.D_07_CNT + B.D_08_CNT + B.D_09_CNT) SUM_ROLL_CNT
			FROM TBD00_PLAN_ROLL  B
		WHERE B.PLAN_SEQ = (SELECT MAX(PLAN_SEQ )FROM TBD00_PLAN_ROLL )) Z
GROUP BY Z.SUM_ROLL_CNT )
SELECT   T1.WTH_GRP_CD,
         NVL(T1.WGT_WTH_GRP,0)  WGT_WTH_GRP,
         NVL(T1.RATE_WTH_GRP,0) RATE_WTH_GRP,
         NVL(T2.CNT_KCL,0)      CNT_KCL,
         NVL(T3.CNT_CUT,0)      CNT_CUT,
         NVL(T4.CNT_ZSDJ,0)     CNT_ZSDJ,
         NVL(T5.DXL,0)          DXL
        ,T1.WGT_WTH_GRP - T2.CNT_KCL + T5.DXL
  FROM  T1,
        (
            SELECT DECODE(Z.WTH_GRP_CD,NULL,'小计',Z.WTH_GRP_CD)  WTH_GRP_CD,
                   SUM(Z.SLAB_WGT)             CNT_KCL                --库存量
             FROM (
            SELECT  C.SLAB_WGT
                   ,C.INSTR_COIL_WTH   
                   ,(SELECT T.WTH_GRP_CD
                      FROM TBF02_ROLL_WTH_GRP T --宽度组代码
                     WHERE T.PLT = 'H'
                       AND DECODE(ORD_FL,'1',C.INSTR_COIL_WTH,'2',C.SLAB_WTH - 50) >= T.MINI
                       AND DECODE(ORD_FL,'1',C.INSTR_COIL_WTH,'2',C.SLAB_WTH - 50) < T.MAXI ) WTH_GRP_CD
              FROM TBG02_SLAB_COMM C
             WHERE C.SLAB_STAT = '2'
               AND C.SLAB_RT   = 'H'
               AND C.CUR_PROG_CD IN ('RRC','RBA'))Z
            GROUP BY ROLLUP (Z.WTH_GRP_CD) 
            ORDER BY Z.WTH_GRP_CD ) T2
        ,(
            SELECT DECODE(C.WTH_GRP_CD,NULL,'小计',C.WTH_GRP_CD)  WTH_GRP_CD,
                   SUM(B.SLAB_WGT)                          cnt_cut   --切割待机量
              FROM TBF01_SPEC_CHARGE   A
                   ,TBF01_SPEC_SLAB    B
                   ,TBF02_ROLL_WTH_GRP C
            WHERE  A.CHARGE_EDT_SEQ  =  B.CHARGE_EDT_SEQ
              AND  A.PROG_CD IN ('JBB','JBF','JCR','JRF','JJB')
              AND  A.STATUS_CD IN ('A','B')
              AND  C.PLT       = 'H'
              AND  B.SLAB_RT   = 'H'
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) >= C.MINI
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) <=  C.MAXI
            GROUP BY ROLLUP (WTH_GRP_CD) ) T3
        ,(
            SELECT DECODE(C.WTH_GRP_CD,NULL,'小计',C.WTH_GRP_CD)  WTH_GRP_CD,
                   NVL( SUM(B.SLAB_WGT),0)                          CNT_ZSDJ  --出钢指示待机量
              FROM TBF01_SPEC_CHARGE   A
                   ,TBF01_SPEC_SLAB    B
                   ,TBF02_ROLL_WTH_GRP C
            WHERE  A.CHARGE_EDT_SEQ  =  B.CHARGE_EDT_SEQ
              AND  A.PROG_CD = 'JBA'
              AND  A.STATUS_CD = 'A'
              AND  C.PLT       = 'H'
              AND  B.SLAB_RT   = 'H'
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) >= C.MINI
              AND  DECODE(B.ORD_FL,'1',B.ORD_WTH,B.SLAB_WTH - 50) <=  C.MAXI
            GROUP BY ROLLUP (WTH_GRP_CD) ) T4
        ,( 
            SELECT DECODE(Z.WTH_GRP_CD,NULL,'小计',Z.WTH_GRP_CD)       WTH_GRP_CD
                   ,NVL(SUM(Z.DXL),0)  DXL                                     --对象量
              FROM(
                    WITH BB_DXL AS (
                    SELECT  WTH_GRP_CD
                           ,SUM(A.ORD_REM_WGT) ORD_REM_WGT_TOT
                      FROM TBE02_ORD_PRC         A
                           ,TBF02_ROLL_WTH_GRP   B
                    WHERE   A.ORD_PROG_CD IN ('D','E')                  --D 投入确定 --E生产进行
                      AND   B.PLT = 'H'
                      AND   A.ORD_WTH >= B.MINI
                      AND   A.ORD_WTH <B.MAXI
                    GROUP BY   (WTH_GRP_CD))
                    SELECT  T1.WTH_GRP_CD,
                            T1.ORD_REM_WGT_TOT  DXL
                      FROM  BB_DXL T1)Z
                GROUP BY   ROLLUP(Z.WTH_GRP_CD)) T5
WHERE T1.WTH_GRP_CD = T2.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T3.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T4.WTH_GRP_CD(+)
  AND T1.WTH_GRP_CD = T5.WTH_GRP_CD(+)
ORDER BY T1.WTH_GRP_CD ASC)T

/*******************************UID050020.findByW***********************************/

        ]]>
    </query>  
		
	<query id="UID050020.findRollWthGrp" desc="求各炉次所接受的中厚板板坯参数" fetchSize="10">
        <![CDATA[
			
				SELECT WTH_GRP_CD lb ,WTH_GRP_CD vl
				  FROM TBF02_ROLL_WTH_GRP 
				UNION ALL
				SELECT 'sum' WTH_GRP_CD ,'小计' vl  FROM DUAL
				 ORDER BY lb ASC
			
        ]]>
    </query>
</queryMap>