XinYu_RZMES/.svn/pristine/fc/fcb17296b515686d2b6082250918983666872233.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="炉次号成分判定查询">
  
    <query id="UIB020040_CHARGE_01.SELECT" desc="炉次号信息查询">
	 /*  SELECT        T.STL_GRD                                      --厂内钢号
		           , DECODE(T.BEF_STL_GRD , NULL , T.STL_GRD , T.BEF_STL_GRD)||' / '|| T.STL_GRD    THE_STL_GRD
                   , SUBSTR(T.LST_INGR_CD ,3 )    LST_INGR_CD       --最后工序
                   , DECODE(T.INGR_DEC_GRD , '1' , '合格' , '2' , '不合格' , '待判' )              INGR_DEC          --成分判定等级
		           , T.INGR_DEC_DTIME             TIME              --判定时间    
		           , '炼钢放行'  SLAB_INGR_DEC_KIND 
       FROM          TBG02_CHARGE_COMM_D    T 
	   WHERE         T.CHARGE_NO           =      ?*/
	   
	   
	   SELECT *
  		FROM (WITH WK AS (SELECT T.CHARGE_NO,
                           T.CHEM_SEQ,
                           T.PROC_CD,
                           T.CHEM_L2_PROC_CD
                      FROM TBB02_WORK_INGR T
                     WHERE T.PROC_CD = 'JJ'
                       AND T.CHEM_CD = 'C')
         SELECT T.STL_GRD --厂内钢号
               ,
                DECODE(T.BEF_STL_GRD, NULL, T.STL_GRD, T.BEF_STL_GRD) ||
                ' / ' || T.STL_GRD THE_STL_GRD,
                WK.CHEM_SEQ LST_INGR_CD --最后工序
               ,
                DECODE(T.INGR_DEC_GRD, '1', '合格', '2', '不合格', '待判') INGR_DEC --成分判定等级
               ,
                T.INGR_DEC_DTIME TIME --判定时间    
               ,
                '炼钢放行' SLAB_INGR_DEC_KIND
           FROM TBG02_CHARGE_COMM_D T, WK
          WHERE T.CHARGE_NO = ?
            AND T.CHARGE_NO = WK.CHARGE_NO
          ORDER BY LST_INGR_CD DESC)
          WHERE ROWNUM = 1
    </query>
	
	
	<query id="UIB020040_CHARGE_02.SELECT" desc="">
	  SELECT 
      DECODE(SQ.COMP_YN , 'Y' , SQ.COMP_CAL , SQ.CHEM_CD)  CHEM_CD  , FF.* , TT.* ,WW.* ,DD.*  FROM 
      TBB01_SEQ_INGR    SQ
    ,(SELECT  S1.CHEM_CD CHEMCD , T1.CHEM_MIN CHEM_MIN2 , T1.CHEM_MAX CHEM_MAX2 FROM TBB01_FAC_INGR   T1 , TBB01_SEQ_INGR  S1  WHERE  T1.CHEM_CD(+)   =   S1.CHEM_CD  AND  T1.FAC_STL_GRD(+)   =  ?  )   FF
    ,(SELECT  S2.CHEM_CD CHEMCD , T2.CHEM_MIN CHEM_MIN3 , T2.CHEM_MAX CHEM_MAX3 FROM TBB01_TAFAC_INGR T2 , TBB01_SEQ_INGR  S2  WHERE  T2.CHEM_CD(+)   =   S2.CHEM_CD  AND  T2.TAFAC_STL_GRD(+) =  ?  )   TT
    ,(SELECT  S3.CHEM_CD CHEMCD , W1.CHEM_VAL           , W1.CHEM_L2_VAL        FROM TBB02_WORK_INGR  W1 , TBB01_SEQ_INGR  S3  WHERE  W1.CHEM_CD(+)   =   S3.CHEM_CD  AND  W1.PROC_CD(+) = 'JJ' AND  W1.CHARGE_NO(+)     =  ?   AND W1.CHEM_SEQ(+) = ? )   WW
    ,(SELECT  S4.CHEM_CD CHEMCD , D1.CHEM_DCS_RST                               FROM TBB02_DCS_INGR   D1 , TBB01_SEQ_INGR  S4  WHERE  D1.CHEM_CD(+)   =   S4.CHEM_CD  AND  D1.PROC_CD(+) = 'JJ' AND  D1.CHARGE_NO(+)     =  ?   AND D1.CHEM_SEQ(+) = ? )   DD
      WHERE       SQ.CHEM_CD        =          FF.CHEMCD
      AND         SQ.CHEM_CD        =          TT.CHEMCD
      AND         SQ.CHEM_CD        =          WW.CHEMCD 
      AND         SQ.CHEM_CD        =          DD.CHEMCD
      ORDER BY SQ.COMP_YN , SQ.DISPLAY_SEQ
	</query>
	
	<query id="UIB020040_SLAB_01.SELECT" desc="">
		 SELECT 
         T.STL_GRD
		,XB_PACKAGE.GF_COMNNAME_FIND('E01002' , T.CUR_PROG_CD)                          CUR_PROG_CD
		,XB_PACKAGE.GF_COMNNAME_FIND('B01133' , T.SLAB_INGR_DEC_KIND)                   SLAB_INGR_DEC_KIND
		,DECODE(T.BEF_STL_GRD , NULL , T.STL_GRD , T.BEF_STL_GRD)||' / '|| T.STL_GRD    THE_STL_GRD
        ,DECODE(T.ORD_FL , '1' ,  T.ORD_NO||T.ORD_SEQ , T.ORG_ORD_NO||T.ORG_ORD_SEQ )   ORD
		,DECODE(T.ORD_FL , '1' ,  T.ORD_NO  , T.ORG_ORD_NO)   ORD_NO
        ,DECODE(T.ORD_FL , '1' ,  T.ORD_SEQ , T.ORG_ORD_SEQ)  ORD_SEQ
        ,DECODE(T.ORD_FL , '1' ,  '订单材' , '余材')           ORD_FL
        ,T.SF_METHOD
        ,DECODE(T.SLAB_INGR_DEC_DTIME , NULL , B.INGR_DEC_DTIME ,T.SLAB_INGR_DEC_DTIME  ) TIME
        ,DECODE(T.SLAB_INGR_DEC_GRD , '1' , '合格' , '2' ,'不合格' , '待判')  INGR_DEC
        ,DECODE(T.EXTSHAPE_GRD , '1' , '合格' , '2' ,'不合格' , '待判')       EXTSHAPE_GRD
        ,SUBSTR(B.LST_INGR_CD , 3)                                          LST_INGR_CD 
		 FROM     TBG02_SLAB_COMM    T , TBG02_CHARGE_COMM_D B
		 WHERE    SUBSTR(T.SLAB_NO , 1,10) =     B.CHARGE_NO
		 AND      T.SLAB_NO                =     ?
	</query>
	
	<query id="UIB020040_SLAB_02.SELECT" desc="">
		SELECT  DECODE(X.COMP_YN , 'Y' , X.COMP_CAL , X.CHEM_CD) CHEM_CD ,A.* , B.* , C.*  ,D.* ,E.* ,F.* FROM 
		(SELECT  S.CHEM_CD CHEMCD, T.CHEM_MIN  CHEM_MIN1 , T.CHEM_MAX  CHEM_MAX1 FROM TBB01_ORD_INGR T , TBB01_SEQ_INGR S WHERE T.CHEM_CD(+) = S.CHEM_CD AND T.ORD_NO(+)  = ? AND T.ORD_SEQ(+) = ?  AND T.DSN_KIND(+) = '1') A,
		(SELECT  S.CHEM_CD CHEMCD, T.CHEM_MIN  CHEM_MIN3 , T.CHEM_MAX  CHEM_MAX3 FROM TBB01_ORD_INGR T , TBB01_SEQ_INGR S WHERE T.CHEM_CD(+) = S.CHEM_CD AND  T.ORD_NO(+) = ? AND T.ORD_SEQ(+) = ?  AND T.DSN_KIND(+) = '3') B,
		(SELECT  S.CHEM_CD CHEMCD, T.CHEM_MIN  CHEM_MIN4 , T.CHEM_MAX  CHEM_MAX4 FROM TBB01_ORD_INGR T , TBB01_SEQ_INGR S WHERE T.CHEM_CD(+) = S.CHEM_CD AND  T.ORD_NO(+) = ? AND T.ORD_SEQ(+) = ?  AND T.DSN_KIND(+) = '2') C,
		(SELECT  S.CHEM_CD CHEMCD, T1.CHEM_MIN CHEM_MIN2 , T1.CHEM_MAX CHEM_MAX2 FROM TBB01_FAC_INGR  T1, TBB01_SEQ_INGR S WHERE T1.CHEM_CD(+) = S.CHEM_CD AND  T1.FAC_STL_GRD(+) = ?) D,
		(SELECT  S.CHEM_CD CHEMCD, T2.CHEM_DCS_RST                               FROM TBB02_DCS_INGR  T2, TBB01_SEQ_INGR S WHERE T2.CHEM_CD(+) = S.CHEM_CD AND T2.CHARGE_NO(+) = ? AND T2.PROC_CD(+) = 'JJ' AND T2.CHEM_SEQ(+) = ?) E,
		(SELECT  S.CHEM_CD CHEMCD, T3.CHEM_VAL , T3.CHEM_L2_VAL                  FROM TBB02_WORK_INGR T3, TBB01_SEQ_INGR S WHERE T3.CHEM_CD(+) = S.CHEM_CD AND T3.CHARGE_NO(+) = ? AND T3.PROC_CD(+) = 'JJ' AND T3.CHEM_SEQ(+) = ? )F,
		TBB01_SEQ_INGR  X
		WHERE      A.CHEMCD     =  X.CHEM_CD
		AND        B.CHEMCD     =  X.CHEM_CD
		AND        C.CHEMCD     =  X.CHEM_CD
		AND        D.CHEMCD     =  X.CHEM_CD
		AND        E.CHEMCD     =  X.CHEM_CD
		AND        F.CHEMCD     =  X.CHEM_CD
		ORDER BY   X.COMP_YN  , X.DISPLAY_SEQ
	</query>
	
</queryMap>