XinYu_RZMES/.svn/pristine/2a/2a7a639dc2103929e34cdb0c6225db9546a02fdf.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="炉次号成分实绩录入">

    <query id="UIB020030_01.INITSELECT" desc="炉次号选择查询" fetchSize="10">  
        <![CDATA[
          SELECT          DISTINCT 
		  CHARGE_NO       LABEL
		, CHARGE_NO       VALUE 
		  FROM            TBG02_CHARGE_COMM_D  CHM
		  WHERE	         (CHARGE_STAT          =  '2'    OR 	 CHARGE_STAT     =  '3' )   --外购板坯
          AND             CHM.LST_INGR_CD  IS NULL
          AND             CHM.CHARGE_NO    LIKE      'J0%'
          ORDER BY        CHARGE_NO
        ]]>   
    </query> 
	
	<query id="UIB020030_02.INITSELECT" desc=" 工厂工序代码">
		SELECT SM_CD VALUE , SM_CFNM LABEL FROM TBZ00_COMMCD WHERE LG_CD = 'B01129'
	</query>
	
	<query id="UIB020030_03.INITSELECT" desc="未输入连铸成分炉号">
		SELECT     T.CHARGE_NO       LABEL
				  ,T.CHARGE_NO       VALUE 
		FROM       TBG02_CHARGE_COMM_D     T
		WHERE NOT  EXISTS (SELECT   'X'  FROM  TBB02_WORK_INGR W
						   WHERE     W.CHARGE_NO  =    T.CHARGE_NO
						   AND       W.PROC_CD    =   'JJ'
						   AND       ROWNUM       =    1)
		AND        T.CHARGE_NO            LIKE         'J0%'           
		ORDER BY   T.CHARGE_NO
	</query> 
	
	<query id="UIB020030_00.SELECT" desc="获得炉次号对应的厂内钢号">
		SELECT       CC.STL_GRD  
                    ,''   PRODNM_CD
                    ,CC.SLAB_RT                 --板坯去向（生产线）
                    ,CC.CHARGE_INGR_DEC_EMP_ID  --判定人员
                    ,CC.LST_INGR_REG_DTIME      --判定时间
                    ,''   REGID                  --检验人员
        FROM        TBG02_CHARGE_COMM_D    CC  
        WHERE       CC.CHARGE_NO           =      ?
	</query>
	
	<query id="UIB020030_01.SELECT" desc="炉次号信息查询" fetchSize="10">  
        <![CDATA[
          SELECT       DISTINCT
          WI.CHARGE_NO
        , WI.PROC_CD
        , WI.CHEM_SEQ
    --  , WI.UPDATE_NO_TP        --是否已发质保书  
		, decode(CC.LST_INGR_CD , 'JJ'||WI.CHEM_SEQ ,  CC.INGR_DEC_GRD  )	  DEC_YN
		,(SELECT     CHEM_L2_PROC_CD   
	      FROM       TBB02_WORK_INGR T 
	      WHERE      T.CHARGE_NO     =    WI.CHARGE_NO  
          AND        T.PROC_CD       =    WI.PROC_CD  AND  T.CHEM_SEQ = WI.CHEM_SEQ  AND T.CHEM_L2_PROC_CD IS NOT NULL  AND    ROWNUM = 1   )    CHEM_L2_PROC_CD
          FROM        TBB02_WORK_INGR  WI  ,  TBG02_CHARGE_COMM_D   CC 
          WHERE       CHEM_VAL_TP      =   'L'                --'L'表是炉次的信息
          AND         WI.CHARGE_NO     =   CC.CHARGE_NO
          AND         WI.CHARGE_NO     =   ?
          AND         WI.PROC_CD       =   ?
		  ORDER BY    WI.CHEM_SEQ	
        ]]>   
    </query> 
		
	<query id="UIB020030_02.SELECT" desc="非复合元素查询" fetchSize="10">   
        <![CDATA[
			WITH   WF   AS
			( 
			SELECT      SIR.CHEM_CD 
			          , TO_CHAR(WIR.CHEM_VAL, 'FM990.0000999')  CHEM_VAL
			          , SIR.DISPLAY_SEQ 
			          , DECODE(WIR.CHEM_CD , NULL , 'Y')    EX   --值为Y表示要进行insert，否则为update
			          , SIR.DISPLAY_LEN
			FROM        TBB02_WORK_INGR WIR , TBB01_SEQ_INGR SIR 
			WHERE       WIR.CHEM_CD(+)      =   SIR.CHEM_CD
			AND         SIR.COMP_YN         =   'N'               --非复合元素
			AND         WIR.CHARGE_NO(+)    =   ?
			AND         WIR.PROC_CD(+)      =   ?
			AND         WIR.CHEM_SEQ(+)     =   ?  
			)
			SELECT      WF.* 
			          , TO_CHAR(FI.CHEM_MIN , 'FM0.'||SUBSTR(WF.DISPLAY_LEN , 2))  CHEM_MIN
			          , TO_CHAR(FI.CHEM_MAX , 'FM0.'||SUBSTR(WF.DISPLAY_LEN , 2))  CHEM_MAX
			FROM        TBB01_TAFAC_INGR FI  ,  WF
			WHERE       FI.CHEM_CD(+)       =   WF.CHEM_CD         --所有元素均要显示出来
			AND         FI.TAFAC_STL_GRD(+)   =   ?
			ORDER BY    WF.DISPLAY_SEQ
        ]]>   
    </query> 
	
	<query id="UIB020030_03.SELECT" desc="复合元素查询">
			WITH   WF   AS
			( 
			SELECT      SIR.CHEM_CD 
		              , TO_CHAR(WIR.CHEM_VAL, 'FM990.0000999')  CHEM_VAL
		              , SIR.DISPLAY_SEQ 
		              , SIR.COMP_CAL 
		              , SIR.COMP_DETAIL
		              , DECODE(WIR.CHEM_CD , NULL , 'Y') EX
		              , SIR.DISPLAY_LEN
			FROM        TBB02_WORK_INGR WIR , TBB01_SEQ_INGR SIR 
			WHERE       WIR.CHEM_CD(+)     =   SIR.CHEM_CD
			AND         SIR.COMP_YN        =   'Y'
			AND         WIR.CHARGE_NO(+)   =   ?
			AND         WIR.PROC_CD(+)     =   ?
			AND         WIR.CHEM_SEQ(+)    =   ?
			)
			SELECT      WF.*                --复合元素不必要所有都显示，只需要有标准的
					  , TO_CHAR(FI.CHEM_MIN , 'FM0.'||SUBSTR(WF.DISPLAY_LEN , 2))  CHEM_MIN
			          , TO_CHAR(FI.CHEM_MAX , 'FM0.'||SUBSTR(WF.DISPLAY_LEN , 2))  CHEM_MAX
			FROM        TBB01_TAFAC_INGR FI , WF
			WHERE       FI.CHEM_CD(+)             =   WF.CHEM_CD
			AND         FI.TAFAC_STL_GRD(+)       =   ?
			ORDER BY    FI.CHEM_MIN(+)||FI.CHEM_MAX(+)    ASC
	</query>
	
	<query id="UIB020030_04.SELECT" desc="在表格1中增加列时需要将TBB01_FAC_INGR的单一元素放入表格2、3、4中">
		SELECT   
        SIR.CHEM_CD  
      , FIR.CHEM_MIN 
      , FIR.CHEM_MAX 
        FROM            TBB01_TAFAC_INGR    FIR ,  TBB01_SEQ_INGR    SIR
        WHERE           FIR.CHEM_CD(+)          =    SIR.CHEM_CD
		AND             SIR.COMP_YN             =    'N'
		AND             FIR.TAFAC_STL_GRD(+)    =     ?
		ORDER BY        SIR.DISPLAY_SEQ
	</query>
	
	<query id="UIB020030_05.SELECT" desc="在表格1中增加列时需要将TBB01_FAC_INGR的复合元素放入表格5中">
		SELECT          FIR.CHEM_CD 
		              , FIR.CHEM_MIN 
		              , FIR.CHEM_MAX 
		              , SI.COMP_CAL 
		              , SI.COMP_DETAIL
		FROM            TBB01_TAFAC_INGR      FIR  , TBB01_SEQ_INGR SI
		WHERE           SI.COMP_YN             =     'Y'  --复合元素
        AND             FIR.CHEM_CD            =     SI.CHEM_CD
        AND             FIR.TAFAC_STL_GRD      =    ?
        ORDER BY        FIR.CHEM_MIN||FIR.CHEM_MAX    ASC
	</query>

	<query id="UIB020030_06.SELECT" desc="炉次号选择查询" fetchSize="10">  
        <![CDATA[
           SELECT          DISTINCT 
           CHARGE_NO       LABEL
         , CHARGE_NO       VALUE 
           FROM            TBG02_CHARGE_COMM_D
		   WHERE	      (CHARGE_STAT  =  '2'
		                   OR  CHARGE_STAT  =  '3')    --外购板坯
		   AND             SUBSTR(CHARGE_NO ,1, 3) != 'J9W'	
		   AND             CHARGE_NO LIKE  ?
		   ORDER BY        CHARGE_NO	
        ]]>   
    </query> 
	
	<query id="UIB020030_01.DELETE" desc="删除某炉次号信息">
		DELETE   FROM  TBB02_WORK_INGR    
		WHERE          CHARGE_NO = ?
		AND            PROC_CD   = ?
		AND            CHEM_SEQ  = ?
	</query>
	
	<query id="UIB020030_02.DELETE" desc="删除某具体的元素">
		DELETE     FROM        TBB02_WORK_INGR 
		WHERE      CHARGE_NO   =   ?  
		AND        PROC_CD     =   ? 
		AND        CHEM_SEQ    =   ? 
		AND        CHEM_CD     =   ?
	</query>
	
    <query id="UIB020030_01.UPDATE" desc="炉次号实绩更新">
		UPDATE         TBB02_WORK_INGR
		SET            CHEM_VAL    =  ?  
		             , CHEM_L2_VAL =  ?
		             , CHEM_VAL_TP = 'L'  --'L'表示更新的炉次的信息
		             , MOD_ID      =  ?
		             , MOD_DTIME   =  TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		WHERE          CHARGE_NO   =  ?
		AND            PROC_CD     =  ?
		AND            CHEM_SEQ    =  ?
		AND            CHEM_CD     =  ?   
	</query>
	
	<query id="UIB020030_02.UPDATE" desc="">
		UPDATE         TBB02_WORK_INGR
		SET            CHEM_L2_PROC_CD   =   ?
		              ,MOD_ID            =   ?
		              ,MOD_DTIME         =   TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		WHERE          CHARGE_NO         =   ?
		AND            PROC_CD           =   ?  
		AND            CHEM_SEQ          =   ?
	</query>
	
	<query id="UIB020030_03.UPDATE" desc="更新工序状态及取样次数">
		UPDATE      TBG02_CHARGE_COMM    
		SET         LST_INGR_CD            =    ?
		          , CHARGE_INGR_DEC_EMP_ID =    ?
		          , LST_INGR_REG_DTIME     =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		WHERE       CHARGE_NO              =    ?
	</query>
	
	<query id="UIB020030_01.INSERT" desc="炉次号成分实绩录入">
		INSERT  INTO TBB02_WORK_INGR(
									CHARGE_NO  
								  , PROC_CD
								  , CHEM_SEQ 
								  , CHEM_CD 
								  , CHEM_VAL
		                          , CHEM_L2_VAL
		                          , REG_ID
		                          , REG_DTIME
		                          , CHEM_VAL_TP 
		                          , CHEM_L2_PROC_CD 
									)
		VALUES(?,?,?,?,? ,? ,? , TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS') , 'L' , ?)            --'L'表示插入的是炉次的信息
	</query>
	
	<query id="UIB020030_01.CALLSTATEMENT" desc="成分判定">
		{call  ZL_HOST_DCS01.PIB020_WORK_INGR(?,?,?,?,?,?,?,?,?,?)}
	</query>
	
	<query id="UIB020030_02.CALLSTATEMENT" desc="删除某到工序">
		{call  ZL_HOST_DCS01.PIB020_WORK_INGR_SP1(?,?,?,?)}
	</query>
</queryMap>