XinYu_RZMES/.svn/pristine/43/430c566f032cf35c70d56d13390945738eb3900f.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="炉次判定及改判">
  
	<query id="UIB030202_01.INITSELECT" desc="">
		SELECT T.SM_CD VALUE , T.SM_CFNM LABEL FROM TBZ00_COMMCD T WHERE T.LG_CD = 'E01002'
	</query>
     <query id="UIB030202_01.SELECT" desc="" fetchSize="10">  
        <![CDATA[
		 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    
		 CD.CHARGE_NO  
		,CD.SUB_CHARGE_NO        
		,CD.INGR_DEC_GRD
		,CD.CHARGE_INGR_DEC_EMP_ID
		,CD.STL_GRD
		,DECODE(CD.BEF_STL_GRD , NULL , CD.STL_GRD , CD.BEF_STL_GRD)  BEF_STL_GRD
		,WK.CHEM_L2_PROC_CD
		,CC.CC_DEVNO          ACT_CCM_RT  
		,CD.LST_INGR_CD     
		,WK.CHEM_SEQ  
		FROM      TBG02_CHARGE_COMM_D   CD , TBG02_CHARGE_COMM   CC ,WK 
		WHERE     CD.CHARGE_NO      =       WK.CHARGE_NO 
		AND       CD.CHARGE_NO      =       CC.CHARGE_NO(+)
		AND       ROWNUM            <       10000
		AND       DECODE(? , '1' , '1' , SUBSTR(CD.CHARGE_NO , 1,9))    =     ?   --炉号，将没有炉号时值默认为1
		AND       DECODE(? , '0000' ,'0000',CD.CHARGE_INIT_DTIME )  BETWEEN ?  AND ?    --生产时间,开始时间默认为0000，结束时间默认为9999
		AND       DECODE(CD.CHARGE_INGR_DEC_EMP_ID,null,'%','','%',replace(CD.CHARGE_INGR_DEC_EMP_ID,' ','')) LIKE ?  --添加成分判定人查询
		AND       ('True' <> ? OR CD.INGR_DEC_GRD IS NULL)  --查未成分判定的
		ORDER  BY    CD.CHARGE_NO
        ]]>   
    </query>
	
	<query id="UIB030202_02.SELECT" desc="">
	<![CDATA[
	 	 SELECT  DISTINCT
		 T.SLAB_NO
		,DECODE(T.ASK_TP,'0','申请待确认','1','申请已确认','未申请') ASK_TP
		,T.ASK_CFM_JR_DESC--加热要求
		,T.ASK_CFM_ZZ_DESC--轧制要求
		,T.ASK_CFM_QY_DESC--取样要求
		,T.ASK_CFM_RCL_DESC--热处理要求
		,T.ASK_CFM_LGPD_DESC--炼钢判定及处理要求
		,T.CFPDYQ           --成品判定及处理要求
        ,T.SUB_SLAB_NO
        ,T.ORD_NO
        ,T.ORD_SEQ
        ,T.ORG_ORD_NO
        ,T.ORG_ORD_SEQ
		,T.STL_GRD
		,PKG_QUALITY_COMM.FIB010200(T.ORD_NO , T.ORD_SEQ)                 CUST_SPEC_NO
		,DECODE(T.BEF_STL_GRD , NULL , T.STL_GRD , T.BEF_STL_GRD)  BEF_STL_GRD
		,T.SLAB_INGR_DEC_GRD
		,T.SLAB_RT
		,T.EXTSHAPE_GRD
		,T.SF_METHOD 
		,T.SLAB_STAT
		,T.ORD_FL
		,T.TW_SLAB_YN         --头尾坯
		,T.SURPLMATL_YN       --过渡坯
		,T.SLAB_DCS_DETAIL
		,T.CUR_PROG_CD
		,T.SLAB_INGR_DEC_KIND  
		,T.L2_SCH_NO
		,DECODE(T.SLAB_THK||'*'||T.SLAB_WTH||'*'||T.SLAB_LEN , '**'
		,NULL , T.SLAB_THK||'*'||T.SLAB_WTH||'*'||T.SLAB_LEN
		 ) TWL
	     ,D01S.L2_MEMO
         FROM        (SELECT SC.* , TS.L2_SCH_NO   FROM  TBG02_SLAB_COMM SC , TBD01_SLABDN_OBJ2 TS   WHERE TS.ORD_NO(+) = SC.ORD_NO AND TS.ORD_SEQ(+) = SC.ORD_SEQ )   T  
                    ,TBF01_SPEC_SLAB  F01S , TBD01_SLABDN_OBJ2 D01S
         WHERE       T.SLAB_NO           LIKE   ?||'%'
         AND         SLAB_CUT_DTIME  IS  NOT  NULL
         AND         T.PLAN_SLAB_NO      =    F01S.SLAB_MANA_NO(+)
         AND         F01S.DSN_SLAB_NO    =    D01S.DSN_SLAB_NO(+)
         AND        NVL(T.MATRL_END_CAUSE_CD,'NULL') <> '4'---MATRL_END_CAUSE_CD = '4' 表示母板
         AND        NVL(T.MATRL_END_CAUSE_CD,'NULL') <> '5'
         ORDER BY    T.SUB_SLAB_NO
         ]]> 
	</query>
	
	<query id="UIB030202_03.SELECT" desc="">
		SELECT 
		DECODE(SQ.COMP_YN , 'Y' , SQ.COMP_CAL , SQ.CHEM_CD)  CHEM_CD  , FF.* , TT.* ,WW.* FROM 
		TBB01_SEQ_INGR    SQ
	  ,(SELECT  S1.CHEM_CD CHEMCD , T1.CHEM_MIN CHEM_MIN1 , T1.CHEM_MAX CHEM_MAX1 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           , T2.CHEM_MAX           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                                   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
		WHERE       SQ.CHEM_CD        =          FF.CHEMCD
		AND         SQ.CHEM_CD        =          TT.CHEMCD
		AND         SQ.CHEM_CD        =          WW.CHEMCD 
		ORDER BY SQ.COMP_YN , SQ.DISPLAY_SEQ

	</query>
	
	<query id="UIB030202_04.SELECT" desc="按板坯查询">
		 SELECT  DECODE(C.COMP_YN , 'Y' , C.COMP_CAL , C.CHEM_CD) CHEM_CD ,A.* , B.* , D.* FROM 
		(SELECT  S.CHEM_CD CHEMCD, T.CHEM_MIN CHEM_MIN2 , T.CHEM_MAX CHEM_MAX2 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           , T.CHEM_MAX           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_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(+) = '2') D,
		 TBB01_SEQ_INGR  C
		 WHERE      A.CHEMCD     =  B.CHEMCD
		 AND        B.CHEMCD     =  C.CHEM_CD
		 AND        B.CHEMCD     =  D.CHEMCD
		 ORDER BY   C.COMP_YN  , C.DISPLAY_SEQ
	</query>
	
	<query id="UIB030202_05.SELECT" desc="需要重新判定的炉次">
		SELECT  T.CHARGE_NO||'('||T.CHARGE_INGR_DEC_EMP_ID||')'  CHARGE_NO FROM TBG02_CHARGE_COMM_D T WHERE T.INGR_DCS_TP = 'A'
	</query>
	
	<query id="UIB030202_06.SELECT" desc="按牌号查询炼钢放行成分">
		 SELECT  DECODE(C.COMP_YN , 'Y' , C.COMP_CAL , C.CHEM_CD) CHEM_CD ,A.* , B.* , D.* FROM 
    	(SELECT  S.CHEM_CD CHEMCD, T.CHEM_MIN CHEM_MIN2 , T.CHEM_MAX CHEM_MAX2 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, TT.CHEM_MIN           , TT.CHEM_MAX           FROM TBB01_TAFAC_INGR TT , TBB01_SEQ_INGR S WHERE TT.CHEM_CD(+) = S.CHEM_CD AND TT.TAFAC_STL_GRD = ? ) B,
    	(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(+) = '2') D,
     	TBB01_SEQ_INGR  C
     	WHERE      A.CHEMCD     =  B.CHEMCD
     	AND        B.CHEMCD     =  C.CHEM_CD
     	AND        B.CHEMCD     =  D.CHEMCD
     	ORDER BY   C.COMP_YN  , C.DISPLAY_SEQ	
	</query>
	
	<query id="UIB030202_FAC.SELECT" desc="厂内牌号">
		SELECT	DISTINCT    'N' CHK
                           ,FIR.FAC_STL_GRD
                           ,FIR.FAC_CODE
		FROM                TBB01_FAC_INGR FIR WHERE FIR.FAC_STL_GRD LIKE ? || '%'  AND  FIR.FAC_CODE like ? || '%'  AND  FIR.FAC_STL_GRD NOT LIKE 'A/F%'
		ORDER BY             FIR.FAC_CODE , FIR.FAC_STL_GRD
	</query>
	
	<query id="UIB030202_05.UPDATE" desc="直接改为合格">
		UPDATE     TBG02_SLAB_COMM               SC
		WHERE      SC.SLAB_INGR_DEC_GRD          =   '1'
				  ,SC.SLAB_INGR_DEC_KIND         =   '4'
		          ,SC.SLAB_INGR_DEC_DTIME        =   TO_CHAR(sysdate , 'YYYYMMDDHH24MISS')
		          ,SC.SLAB_INGR_DEC_EMP_ID       =   ?
		WHERE      SC.SLAB_NO                 LIKE   ?||'%'
	</query>
	
	<query id="UIB030202_01.CALLSTATEMENT" desc="成分判定">
		{call ZL_HOST_DCS01.NIB028010(?,?,?,?,?,?,?)}
	</query>
	
	<query id="UIB030202_02.CALLSTATEMENT" desc="炉次改判">
		{call ZL_HOST_DCS01.NIB028032(?,?,?,?,?,?,?)}
	</query>
	
	<query id="UIB030202_03.CALLSTATEMENT" desc="板坯改判">
		{call ZL_HOST_DCS01.NIB028033(?,?,?,?,?,?,?)}
	</query>
	
	<query id="UIB030202_04.CALLSTATEMENT" desc="判断改判是否合格">
		{call ZL_HOST_DCS01.PIB020_FAC_CHAG(?,?,?,?)}
	</query>
	
	<query id="UIB030202_05.CALLSTATEMENT" desc="正常流转或者判次">
		{call ZL_HOST_DCS01.PIB020_SLAB_ANYS(?,?,?,?,?)}
	</query>
	
	<query id="UIB030202_06.UPDATE" desc="更新ASK_TP">
	<![CDATA[
		UPDATE       TBG02_SLAB_COMM 	T
		SET          T.ASK_TP='0'
		WHERE        T.SLAB_NO=? AND (T.ASK_TP IS NULL OR T.ASK_TP!='1') AND T.SLAB_STAT='2'
	]]>
	</query>
</queryMap>