XinYu_RZMES/.svn/pristine/35/35a01c50e67b9baf350d052e6ecf122eb31101b7.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIG020090_CHMNM.select" desc=" 动态成份的成份代码查询 " fetchSize="10">
		<![CDATA[
		SELECT    DECODE(X.COMP_YN, 'Y', X.COMP_CAL, X.CHEM_CD) AS CHEM
              ,X.CHEM_CD
          ,X.COMP_YN
          ,X.DISPLAY_SEQ
             FROM     TBB01_SEQ_INGR X
             WHERE    EXISTS (SELECT 'X'
                    FROM   TBB02_WORK_INGR Y
                    WHERE  '1' = '1'
                  --  AND    Y.CHEM_CD       =  X.CHEM_CD
                    AND    Y.CHEM_VAL_TP   =  'L'      --'L'表示炉次，'P'表示钢卷，'S'表示板坯
                    AND    CHEM_VAL IS NOT NULL
                    AND ROWNUM = 1)
         AND X.COMP_YN = 'N'                   
         ORDER BY X.COMP_YN , X.DISPLAY_SEQ  	  
        ]]>
	</query> 
	<query id="UIG020090_CHMNM_02.select" desc=" 动态成份的成份代码查询 " fetchSize="10">
		<![CDATA[
		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(+) = substr(?,1,2) )   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="UIG020090_INIT_CHMSEQ.select" desc=" 动态成份的成份代码查询 " fetchSize="10">
		<![CDATA[	  
		SELECT       T.CHEM_SEQ||'('||T.CHEM_L2_PROC_CD||')'  LABEL  --人工区分工序次数
		             ,T.CHEM_SEQ                              VALUE
		             ,T.CHEM_SEQ||'('||T.CHEM_L2_PROC_CD||')' VALUE2
		             ,T.CHARGE_NO 
		FROM         TBB02_WORK_INGR    T 
		WHERE        T.PROC_CD           =    'JJ' 
		AND          T.CHEM_CD           =     'C'
		AND          T.CHARGE_NO         =     ?
        ]]>
	</query>
	<query id="UIG0020090_01.CALLSTATEMENT" desc=" 切割计划变更处理 ">
		{call ZL_HOST_DCS01.SUB_CHARGE_PROC(?,?,?,?,?)}
	</query>
	<query id="UIG0020090_CHARGE_INFO.select" desc=" 分炉炉次查询 " fetchSize="10">
		<![CDATA[
			SELECT 		'' CHK
						,'C' DIVIDE_TYPE
			       		,A.CHARGE_NO
			       		,B.CHARGE_NO
			       		,B.SUB_CHARGE_NO
			       		,B.STL_GRD
			       		,B.CHARGE_WGT
			       		,B.SUB_CHARGE_CODE
			       		,B.REG_ID
			       		,B.REG_DTIME
			       		,B.REG_SHIFT
			       		,B.REG_GROUP
			       		,B.SLAB_CNT
			       		,B.SLAB_WGT
			       		,B.CHARGE_STAT
			FROM 	TBG02_CHARGE_COMM A
			     	,TBG02_CHARGE_COMM_D B
			WHERE   A.CHARGE_NO = B.SUB_CHARGE_NO 
			AND     A.CHARGE_NO = ?  	
			AND 	B.SUB_CHARGE_CODE IS NOT NULL
			ORDER BY B.SUB_CHARGE_NO  
        ]]>
	</query>
	<query id="UIG020090_SLAB_INFO.select" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			  SELECT '' CHK
	                 ,'S' DIVIDE_TYPE
	                 ,B.SUB_CHARGE_NO         --
	                 ,A.CHARGE_NO
	                 ,A.SLAB_NO
	                 ,A.SUB_SLAB_NO
	                 ,A.STL_GRD
	                 ,A.SLAB_THK||'*'||A.SLAB_WTH||'*'||A.SLAB_LEN SLAB_SIZE
	                 ,A.SLAB_WGT
	                 ,B.SUB_CHARGE_CODE
	                 ,A.SLAB_INGR_DEC_GRD
	                 ,A.REG_ID
	               ,A.REG_DTIME
	               ,'' REG_SHIFT
	               ,'' REG_GROUP
	               --,C.CHEM_SEQ||'('||C.CHEM_L2_PROC_CD||')'  CHEM_SEQ  --人工区分工序次数
                   ,A.CHARGE_NO 
                   ,C.CHEM_SEQ
	          FROM TBG02_SLAB_COMM A 
	               ,TBG02_CHARGE_COMM_D B
	               ,(SELECT CHARGE_NO,SUBSTR(LST_INGR_CD,3)CHEM_SEQ FROM TBG02_CHARGE_COMM_D WHERE CHARGE_NO = ?)C
	          WHERE '1' = '1' 
	          AND   A.CHARGE_NO = B.CHARGE_NO
	          AND   A.CHARGE_NO = C.CHARGE_NO(+)
	          AND   A.SLAB_STAT = '2'
	          AND   SUBSTR(A.SUB_SLAB_NO,1,10) = ?            
	          ORDER BY A.SLAB_NO,B.SUB_CHARGE_CODE 	  
        ]]>
	</query>
	<query id="UIG020090_SLAB_CUTED_CHEK" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			SELECT * FROM TBG02_SLAB_COMM T
			WHERE  T.SLAB_STAT = '2'
			AND    T.SLAB_NO LIKE ?||'%'  
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMM_DIVIDE_UPDATE" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			UPDATE TBG02_CHARGE_COMM A
			SET    A.DIVIDE_CHARGE_YN = ?
			WHERE  A.CHARGE_NO = ?  
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_WGT_UPDATE" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			UPDATE TBG02_CHARGE_COMM_D A
			SET    A.CHARGE_WGT = ?
			WHERE  A.SUB_CHARGE_NO = SUBSTR(A.CHARGE_NO,1,9)||? 	  
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_WGT_INSERT" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			INSERT INTO TBG02_CHARGE_COMM_D A
			(      CHARGE_NO
			       ,SUB_CHARGE_NO
			       ,STL_GRD
			       ,CHARGE_STAT
			       ,CHARGE_WGT
			       ,CUR_PROG_CD
			       ,CUR_PROG_CD_REG_PGM
			       ,CUR_PROG_CD_REG_DTIME
			       ,SLAB_CNT
			       ,SLAB_WGT
			       ,BEF_PROG_CD
			       ,BEF_PROG_CD_REG_PGM
			       ,BEF_PROG_CD_REG_DTIME
			       ,SUB_CHARGE_CODE
			       ,REG_ID
			       ,REG_DTIME
			       ,REG_SHIFT
			       ,REG_GROUP
			       ,SLAB_RT
			       ,CHARGE_INIT_DTIME
			)(SELECT 
			       SUBSTR(T.CHARGE_NO,1,9)||? SUB_CHARGE_NO
			       ,T.CHARGE_NO
			       ,T.STL_GRD
			       ,'2' CHARGE_STAT
			       ,?
			       ,CUR_PROG_CD
			       ,'CHARGEDIVIDETYPE' CUR_PROG_CD_REG_PGM
			       ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CUR_PROG_CD_REG_DTIME
			       ,0 SLAB_CNT
			       ,0 SLAB_WGT
			       ,T.CUR_PROG_CD BEF_PROG_CD
			       ,T.CUR_PROG_CD_REG_PGM BEF_PROG_CD_REG_PGM
			       ,T.CUR_PROG_CD_REG_DTIME BEF_PROG_CD_REG_DTIME
			       ,? SUB_CHARGE_CODE
			       ,? REG_ID
			       ,TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') REG_DTIME
			       ,? REG_SHIFT
			       ,? REG_GROUP
			       ,SLAB_RT
			       ,CHARGE_INIT_DTIME
			 FROM TBG02_CHARGE_COMM T
			 WHERE T.CHARGE_NO = ?
			 )  
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_WGT_UPDATE_03" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			update TBG02_CHARGE_COMM_D A
			set			(    --CHARGE_NO
			             --SUB_CHARGE_NO
			             STL_GRD
			             ,CHARGE_STAT
			             ,CHARGE_WGT
			             ,CUR_PROG_CD
			             ,CUR_PROG_CD_REG_PGM
			             ,CUR_PROG_CD_REG_DTIME
			             ,SLAB_CNT
			             ,SLAB_WGT
			             ,BEF_PROG_CD
			             ,BEF_PROG_CD_REG_PGM
			             ,BEF_PROG_CD_REG_DTIME
			             ,SUB_CHARGE_CODE
			             ,REG_ID
			             ,REG_DTIME
			             ,REG_SHIFT
			             ,REG_GROUP
			             ,SLAB_RT
			      )=(SELECT 
			             --T.CHARGE_NO
			             --SUBSTR(T.CHARGE_NO,1,9)||? SUB_CHARGE_NO
			             T.STL_GRD
			             ,'2' CHARGE_STAT
			             ,? CHARGE_WGT
			             ,CUR_PROG_CD
			             ,'CHARGEDIVIDETYPE' CUR_PROG_CD_REG_PGM
			             ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CUR_PROG_CD_REG_DTIME
			             ,0 SLAB_CNT
			             ,0 SLAB_WGT
			             ,T.CUR_PROG_CD BEF_PROG_CD
			             ,T.CUR_PROG_CD_REG_PGM BEF_PROG_CD_REG_PGM
			             ,T.CUR_PROG_CD_REG_DTIME BEF_PROG_CD_REG_DTIME
			             ,? SUB_CHARGE_CODE
			             ,? REG_ID
			             ,TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') REG_DTIME
			             ,? REG_SHIFT
			             ,? REG_GROUP
			             ,SLAB_RT
			       FROM TBG02_CHARGE_COMM T
			       WHERE T.CHARGE_NO = ?
			       )  
			 where A.CHARGE_NO = ?
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_WGT_UPDATE_02" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			UPDATE TBG02_CHARGE_COMM_D A
			SET A.CHARGE_WGT = (SELECT A.CHARGE_WGT - B.WGT
			                    FROM 
			                           (SELECT CHARGE_WGT  FROM TBG02_CHARGE_COMM  WHERE CHARGE_NO = ? )A
			                           ,(SELECT SUM(CHARGE_WGT) WGT FROM tbg02_charge_comm_d  
			                             WHERE CHARGE_NO = ?
			                             AND   SUBSTR(SUB_CHARGE_NO,10,1) <> 'A')B
			                   )
			WHERE A.CHARGE_NO = ?
			AND   A.SUB_CHARGE_NO = ?
			AND   A.CHARGE_STAT <> '3'
        ]]>
	</query>
	
	<query id="UIG020090_SLAB_COMM_UPDATE" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			  UPDATE TBG02_SLAB_COMM T 
	          SET T.CHARGE_NO = SUBSTR(T.CHARGE_NO,1,9)||?
	              ,T.SLAB_NO = SUBSTR(T.SLAB_NO,1,9)||?||SUBSTR(T.SLAB_NO,11,3)
	              ---------MODIFY BY WXY 100303-------------------------------------
	              ---------DESC:清除新分出的炉次内坯料的成分判定信息，状态回复到待判
	              ,T.CUR_PROG_CD = 'RAB'
	              ,T.SLAB_INGR_DEC_KIND = ''
	              ,T.SLAB_INGR_DEC_GRD = ''
	              ,T.SLAB_INGR_DEC_DTIME = ''
	              ,T.SLAB_INGR_DEC_EMP_ID = ''
	              ,T.SLAB_INGR_DEC_GRD_EXT = ''
	              ,T.ASK_CFM_QY_DESC = ''
	              ---------------MODIFY BY WXY 101217--------------------------------------------
	              ---------------DESC:分炉坯料清空垛位信息，重新入库-----------
	              ,T.CUR_LOAD_LOC = 'A0'
	              ---------------------------------------------------------
	          WHERE T.SUB_SLAB_NO = ?
        ]]>
	</query>
	
	<query id="UIG020090_SLAB_COMM_UPDATE_NEW" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			  UPDATE TBG02_SLAB_COMM T 
	          SET T.CHARGE_NO = SUBSTR(T.CHARGE_NO,1,9)||?
	              ,T.SLAB_NO = SUBSTR(T.SLAB_NO,1,9)||?||SUBSTR(T.SLAB_NO,11,3)
	              ---------MODIFY BY WXY 100303-------------------------------------
	              ---------DESC:清除新分出的炉次内坯料的成分判定信息，状态回复到待判
	              ,T.CUR_PROG_CD = 'RAB'
	              ,T.SLAB_INGR_DEC_KIND = ''
	              ,T.SLAB_INGR_DEC_GRD = ''
	              ,T.SLAB_INGR_DEC_DTIME = ''
	              ,T.SLAB_INGR_DEC_EMP_ID = ''
	              ,T.SLAB_INGR_DEC_GRD_EXT = ''
	              ,T.ASK_CFM_QY_DESC = ''
	              ,T.REG_ID1 = ''
	              
	              ,T.EXTSHAPE_GRD = ''
	              ,T.EXTSHAPE_DEC_EMP_ID = ''
	              ,T.EXTSHAPE_DEC_DTIME = ''
	              ,T.EXTSHAPE_GRD_EXT = ''
	              ,T.REG_ID2 = ''
	              
	              ,T.SLAB_TOT_DEC_GRD = ''
	              ,T.SLAB_TOT_DEC_GRD_ID = ''
	              ,T.SLAB_TOT_DEC_GRD_DTIME = ''
	              ,T.REG_ID3 = ''
	              
	              ,T.SLAB_OUT_TP = ''
	              ,T.REG_ID4 = ''
	              ---------------MODIFY BY WXY 101217--------------------------------------------
	              ---------------DESC:分炉坯料清空垛位信息，重新入库-----------
	              ,T.CUR_LOAD_LOC = 'A0'
	              ---------------------------------------------------------
	          WHERE T.SUB_SLAB_NO = ?
        ]]>
	</query>
	
	<query id="UIG020090_SLAB_COMM_QUERY" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			  SELECT  	T.CHARGE_NO
			  			,COUNT(T.SUB_SLAB_NO) SLAB_CNT
			  			,COUNT(T.SLAB_WGT) SLAB_WGT 
			  FROM TBG02_SLAB_COMM T  
	          WHERE SUBSTR(T.SUB_SLAB_NO,1,10) = ?
	          GROUP BY T.CHARGE_NO
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_DELETE" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
			  UPDATE TBG02_CHARGE_COMM_D T 
	          SET T.SUB_CHARGE_CODE = ''
	          WHERE T.CHARGE_NO NOT IN (SELECT  CHARGE_NO FROM TBG02_SLAB_COMM 
	          WHERE SUBSTR(SUB_SLAB_NO,1,10) = ?
	          GROUP BY CHARGE_NO)
	          AND T.SUB_CHARGE_NO = ?
        ]]>
	</query>
	<query id="UIG020090_CHARGE_COMMD_UPDATE_02" desc=" 分炉板坯查询 " fetchSize="10">
		<![CDATA[
				UPDATE TBG02_CHARGE_COMM_D T
				SET (T.CHARGE_WGT
					,T.SLAB_CNT
					,T.SLAB_WGT
					,T.CHARGE_STAT
					,T.REG_ID
					,T.REG_DTIME
					,T.REG_SHIFT
					,T.REG_GROUP
					)= 
				(SELECT CHARGE_WGT
						,SLAB_CNT
						,SLAB_WGT
						,'3' CHARGE_STAT
						,? REG_ID
						,TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS') REG_DTIME
						,? REG_SHIFT
						,? REG_GROUP 
				 FROM (
				      SELECT SUM(A.SLAB_WGT) CHARGE_WGT
				             ,COUNT(1) SLAB_CNT
				             ,SUM(A.SLAB_WGT) SLAB_WGT
				             
				      FROM TBG02_SLAB_COMM A 
				      WHERE A.CHARGE_NO = ?
				 	)
				)
				WHERE CHARGE_NO = ?
        ]]>
	</query>
</queryMap>