XinYu_RZMES/.svn/pristine/6b/6b8a3d8e35401433d0ba7f07874e91a965ce8654.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIG020500_01.select" desc="  " fetchSize="10">
        <![CDATA[
	SELECT SUBSTR(A.HM_STR_DTIME, 1, 8) CHARGE_START_DTIME --计划炼钢日期
      ,
       SUBSTR(C.CASTING_END_DTIME, 1, 8) CHARGE_END_DTIME --炼钢完成日期
      ,
       C.CHARGE_NO --炉次号
      ,
       C.STL_GRD --钢种
      ,
       C.CAST_NO,
       NVL(E.PLAN_SLAB_CNT, 0) PLAN_SLAB_CNT --计划生产块数
      ,
       NVL(E.PLAN_SLAB_WGT / 1000, 0) PLAN_SLAB_WGT --计划产量
      ,
       NVL(E.SHRT_PLAN_SLAB_CNT, 0) SHRT_PLAN_SLAB_CNT --计划生产块数
      ,
       NVL(E.SHRT_PLAN_SLAB_WGT / 1000, 0) SHRT_PLAN_SLAB_WGT --计划产量
      ,
       NVL(E.LONG_PLAN_SLAB_CNT, 0) LONG_PLAN_SLAB_CNT --计划生产块数
      ,
       NVL(E.LONG_PLAN_SLAB_WGT / 1000, 0) LONG_PLAN_SLAB_WGT --计划产量
      ,
       NVL(B.SLAB_CNT, 0) SLAB_CNT --实际生产块数
      ,
       NVL(B.SLAB_WGT / 1000, 0) SLAB_WGT --实际产量
      ,
       NVL(D.SLAB_CNT, 0) ORD_SLAB_CNT --订单块数
      ,
       NVL(D.SLAB_WGT / 1000, 0) ORD_SLAB_WGT --订单重量
      ,
       NVL(K.SLAB_CNT, 0) ORD2_SLAB_CNT --历库块数
      ,
       NVL(K.SLAB_WGT / 1000, 0) ORD2_SLAB_WGT --历库重量
      ,
       case
                 when  ((NVL(E.PLAN_SLAB_CNT,0) - NVL(D.SLAB_CNT,0)) > 0) 
                 then  (NVL(E.PLAN_SLAB_CNT,0) - NVL(D.SLAB_CNT,0))
                 else  0
       end    DEBT_SLAB_CNT  --欠量块数
      , 
       case
                 when  ((NVL(E.PLAN_SLAB_WGT,0) - NVL(D.SLAB_WGT,0)) > 0) 
                 then  (NVL(E.PLAN_SLAB_WGT,0) - NVL(D.SLAB_WGT,0))/1000
                 else  0
       end    DEBT_SLAB_WGT  --欠量重量
      ,
       NVL(G.SLAB_CNT, 0) DP_SLAB_CNT
      ,
       NVL(G.SLAB_WGT / 1000, 0) DP_SLAB_WGT
      ,
       NVL(F.SLAB_CNT, 0) NORD_SLAB_CNT --非计划块数
      ,
       NVL(F.SLAB_WGT / 1000, 0) NORD_SLAB_CWGT --非计划重量
      ,
       NVL(ROUND(NVL(F.SLAB_CNT, 0) / (NVL(B.SLAB_CNT,0) - NVL(G.SLAB_CNT,0)) * 100,2),0) NORD_SLAB_CNT_RATE  --非计划率
      ,
       NVL(ROUND(NVL(F.SLAB_WGT / 1000, 0) / (NVL(B.SLAB_WGT / 1000, 0) - NVL(G.SLAB_WGT / 1000, 0)) * 100,2),0) NORD_SLAB_WGT_RATE --非计划率
      ,
       NVL(ROUND(D.SLAB_CNT / E.PLAN_SLAB_CNT * 100, 2), 0) SLAB_CNT_RATE --块数完成率
      ,
       NVL(ROUND(D.SLAB_WGT / E.PLAN_SLAB_WGT * 100, 2), 0) SLAB_WGT_RATE --重量完成率
      ,
       H.ORD_NO ORD_NOS
       
      ,I.SLAB_SIZE
      
      ,H.YDZ_WGT_MIN||'-'||H.YDZ_WGT_MAX YDZ_WGT
      
      ,H.DEL_TO_DATE
      
      ,H.RCVORD_CLF
  FROM TBF01_SPEC_CHARGE A,
       TBG02_CHARGE_COMM C,
       ( --计划
        SELECT TT.CHARGE_MANA_NO
                ,COUNT(T.CHARGE_MANA_NO) PLAN_SLAB_CNT
                ,SUM(T.SLAB_WGT) PLAN_SLAB_WGT
                ,COUNT(CASE WHEN T.SLAB_LEN <= (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT 
                                               WHERE TTT.LG_CD LIKE 'D01003' 
                                               AND TTT.CD_SEQ = '2')  
                           THEN T.CHARGE_MANA_NO  
                           END  ) SHRT_PLAN_SLAB_CNT
                ,SUM(CASE WHEN T.SLAB_LEN <= (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT 
                                               WHERE TTT.LG_CD LIKE 'D01003' 
                                               AND TTT.CD_SEQ = '2')  
                           THEN T.SLAB_WGT  
                           END  ) SHRT_PLAN_SLAB_WGT
                ,COUNT(CASE WHEN T.SLAB_LEN > (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT 
                                               WHERE TTT.LG_CD LIKE 'D01003' 
                                               AND TTT.CD_SEQ = '2')  
                           THEN T.CHARGE_MANA_NO  
                           END  ) LONG_PLAN_SLAB_CNT
                ,SUM(CASE WHEN T.SLAB_LEN > (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT 
                                               WHERE TTT.LG_CD LIKE 'D01003' 
                                               AND TTT.CD_SEQ = '2')  
                           THEN T.SLAB_WGT  
                           END  ) LONG_PLAN_SLAB_WGT 
          FROM TBF01_SPEC_SLAB T, TBF01_SPEC_CHARGE TT
         WHERE '1' = '1'
           AND T.CHARGE_MANA_NO = TT.CHARGE_MANA_NO
              --AND TT.CHARGE_MANA_NO = '391-03547A'
           AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'  --：2
         GROUP BY TT.CHARGE_MANA_NO) E,
       ( --实际完成
        SELECT TT.CHARGE_NO CHARGE_NO,
                COUNT(T.SLAB_NO) SLAB_CNT,
                SUM(T.SLAB_WGT) SLAB_WGT
          FROM TBG02_SLAB_COMM T, TBG02_CHARGE_COMM TT
         WHERE SUBSTR(T.SLAB_NO, 1, 10) = TT.CHARGE_NO
           AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
           AND T.SLAB_STAT <> '1'
              --AND   TT.CHARGE_NO = 'J91-03556A'
           AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'	--：4
         GROUP BY TT.CHARGE_NO) B,
       
       ( --计划内
        SELECT SUBSTR(T.SLAB_NO, 1, 10) CHARGE_NO,
                COUNT(T.SLAB_NO) SLAB_CNT,
                SUM(T.SLAB_WGT) SLAB_WGT
          FROM TBG02_SLAB_COMM T
               ,TBF01_SPEC_SLAB TT
         WHERE '1' = '1'
           AND T.PLAN_SLAB_NO = TT.SLAB_MANA_NO
           AND T.ORD_NO = TT.ORD_NO
           AND T.ORD_SEQ = TT.ORD_SEQ
           AND T.ORD_FL = '1'
           AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
           AND T.CUR_PROG_CD <> 'RAB'
           AND T.SLAB_STAT <> '1'
              --AND   SUBSTR(T.SLAB_NO,1,10)  = 'J91-03547A'
           AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'				--：6
         GROUP BY SUBSTR(T.SLAB_NO, 1, 10)) D,
        ( --历库量
        SELECT SUBSTR(T.SLAB_NO, 1, 10) CHARGE_NO,
                COUNT(T.SLAB_NO) SLAB_CNT,
                SUM(T.SLAB_WGT) SLAB_WGT
          FROM TBG02_SLAB_COMM T
               ,TBF01_SPEC_SLAB TT
         WHERE '1' = '1'
           AND T.PLAN_SLAB_NO = TT.SLAB_MANA_NO
           AND (T.ORD_NO <> TT.ORD_NO OR T.ORD_SEQ <> TT.ORD_SEQ)
           AND T.ORD_FL = '1'
           AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
           AND T.CUR_PROG_CD <> 'RAB'
           AND T.SLAB_STAT <> '1'
              --AND   SUBSTR(T.SLAB_NO,1,10)  = 'J91-03547A'
           AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'				--：8
         GROUP BY SUBSTR(T.SLAB_NO, 1, 10)) K,
       ( --非计划
        SELECT SUBSTR(T.SLAB_NO, 1, 10) CHARGE_NO,
                COUNT(T.SLAB_NO) SLAB_CNT,
                SUM(T.SLAB_WGT) SLAB_WGT
          FROM TBG02_SLAB_COMM T
         WHERE T.ORD_FL <> '1'
           AND T.CUR_PROG_CD <> 'RAB'
           AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
           AND T.SLAB_STAT <> '1'
            --  AND   SUBSTR(T.SLAB_NO,1,10)  = 'J91-04269A'
          AND nvl(T.ORD_NO,' ') like ?||'%'			
           AND nvl(T.ORD_SEQ,' ') like ?||'%'			
         GROUP BY SUBSTR(T.SLAB_NO, 1, 10)) F,
       ( --待判
        SELECT SUBSTR(T.SLAB_NO, 1, 10) CHARGE_NO,
                COUNT(T.SLAB_NO) SLAB_CNT,
                SUM(T.SLAB_WGT) SLAB_WGT
          FROM TBG02_SLAB_COMM T
         WHERE T.CUR_PROG_CD = 'RAB'
           AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
           AND T.SLAB_STAT <> '1'
           AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'					--：12
         GROUP BY SUBSTR(T.SLAB_NO, 1, 10)) G,
       ( --合同号
        select charge_no, wmsys.wm_concat(ord_no) ord_no
                ,min(ydz_wgt_max)/1000 ydz_wgt_max
                ,max(ydz_wgt_min)/1000 ydz_wgt_min
                ,min(DEL_TO_DATE) del_to_date
                ,DECODE(MIN(RCVORD_CLF),'0','出口','否')RCVORD_CLF
          from (select distinct substr(t.slab_no, 1, 10) charge_no
                                ,(tt.WGT_ACVAL_MAX - tt.ORD_WGT) YDZ_WGT_MAX --溢短装量
                                ,(tt.ORD_WGT - tt.WGT_ACVAL_MIN) YDZ_WGT_MIN --溢短装量下限
                                ,t.ord_no || t.ord_seq ord_no
                                ,tt.DEL_TO_DATE
                                ,CASE WHEN tt.RCVORD_CLF = 'W'
                                      THEN  0
                                      ELSE 1 
                                 END RCVORD_CLF           
                   from tbg02_slab_comm t
                        ,tbe02_ord_prc tt
                  where t.ORD_NO || t.ORD_SEQ is not null
                    and t.ORD_NO = tt.ORD_NO
                    and t.ORD_SEQ = tt.ORD_SEQ    
                    AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           			AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'
                    )
         group by charge_no) H,
       (SELECT CHARGE_NO, WMSYS.WM_CONCAT(SLAB_SIZE) SLAB_SIZE
          FROM (SELECT DISTINCT SUBSTR(T.SLAB_NO, 1, 10) CHARGE_NO,
                   T.SLAB_THK||'*'||T.SLAB_WTH  SLAB_SIZE
                   FROM TBG02_SLAB_COMM T
                    WHERE '1' = '1'
					 AND T.SLAB_THK  IS NOT NULL
                    AND T.SLAB_WTH IS NOT NULL 
					AND NVL(T.ORD_NO,' ') LIKE ?||'%'		--：3
           			AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'				--：16
                    )
         GROUP BY CHARGE_NO)I  
 WHERE C.PLAN_CHARGE_NO = A.CHARGE_MANA_NO
   AND C.CHARGE_NO = H.CHARGE_NO(+)
   AND C.CHARGE_NO = I.CHARGE_NO
   AND A.CHARGE_MANA_NO = E.CHARGE_MANA_NO
   AND C.CHARGE_NO = B.CHARGE_NO
   AND C.CHARGE_NO = D.CHARGE_NO(+)
   AND C.CHARGE_NO = F.CHARGE_NO(+)
   AND C.CHARGE_NO = G.CHARGE_NO(+)
   AND C.CHARGE_NO = K.CHARGE_NO(+)
   and   decode(?,NULL,0,SUBSTR(H.DEL_TO_DATE,1,8)) BETWEEN NVL(?,0) AND NVL(?,0)
   --AND NVL(SUBSTR(H.DEL_TO_DATE, 1, 8),' ') >= nvl(?, '10000000')
   --AND NVL(SUBSTR(H.DEL_TO_DATE, 1, 8),' ' <= nvl(?, '50000000')
   AND SUBSTR(C.CASTING_END_DTIME, 1, 8) >= NVL(?, '10000000')		--:19
   AND SUBSTR(C.CASTING_END_DTIME, 1, 8) <= NVL(?, '50000000')		--:20
   AND SUBSTR(A.HM_STR_DTIME, 1, 8) >= NVL(?, '10000000')			--:21
   AND SUBSTR(A.HM_STR_DTIME, 1, 8) <= NVL(?, '50000000')			--:22
 ORDER BY C.CHARGE_NO
        ]]>
    </query> 
</queryMap>