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

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc=" 转炉、CAS、RH、LF实绩查询共用 ">  
	 <query id="UIG020COMM_01.select" desc=" 铁合金实绩  " fetchSize="10">
        <![CDATA[
          SELECT b.MAINRAW_CD,b.MAINRAW_CD_DESC,MAINRAW_WGT 
          FROM 
          (  
             SELECT  MAINRAW_CD ,MAINRAW_CD_DESC,MAINRAW_WGT
               FROM TBG03_MAINRAW
              WHERE CHARGE_NO = ?
                AND SUBSTR(PROC_CD,2,1) = ?
			    --AND SUBSTR(MAINRAW_CD,1,1) = 'H'
          ) a,
          (
             SELECT SM_CD MAINRAW_CD,SM_CFNM MAINRAW_CD_DESC
               FROM TBZ00_COMMCD
              WHERE LG_CD = 'G03006'
               --AND  SUBSTR(SM_CD,1,1) = 'H'
          ) b		
         WHERE 	b.MAINRAW_CD = a.MAINRAW_CD(+)	
			
        ]]>
    </query>
	<query id="UIG020COMM_01_single.select" desc=" 按铁合金实绩表的CODE查询单条记录  " fetchSize="10">
        <![CDATA[
			select CD_DESC from  tbz00_commcd  WHERE LG_CD = 'G03001' AND SM_CD = ?  
        ]]>
    </query>
	<query id="UIG020COMM_02.select" desc=" 辅料实绩 " fetchSize="10">
        <![CDATA[
         
	     SELECT b.MAINRAW_CD,b.MAINRAW_CD_DESC,MAINRAW_WGT 
          FROM 
          (  
             SELECT  MAINRAW_CD ,MAINRAW_CD_DESC,MAINRAW_WGT
               FROM TBG03_MAINRAW
              WHERE CHARGE_NO = ?
                AND SUBSTR(PROC_CD,2,1) = ?
			    --AND SUBSTR(MAINRAW_CD,1,1) = 'B'
          ) a,
          (
             SELECT SM_CD MAINRAW_CD,SM_CFNM MAINRAW_CD_DESC
               FROM TBZ00_COMMCD
              WHERE LG_CD = 'G03007'
               --AND  SUBSTR(SM_CD,1,1) = 'B'
          ) b		
         WHERE 	b.MAINRAW_CD = a.MAINRAW_CD(+)			
        ]]>
    </query>
	<query id="UIG020COMM_03.select" desc=" 废料实绩   " fetchSize="10">
        <![CDATA[
        
		 SELECT b.MAINRAW_CD,b.MAINRAW_CD_DESC,MAINRAW_WGT 
          FROM 
          (  
             SELECT  MAINRAW_CD ,MAINRAW_CD_DESC,MAINRAW_WGT
               FROM TBG03_MAINRAW
              WHERE CHARGE_NO = ?
                AND SUBSTR(PROC_CD,2,1) = ?
			    --AND SUBSTR(MAINRAW_CD,1,1) = 'S'
          ) a,
          (
             SELECT SM_CD MAINRAW_CD,SM_CFNM MAINRAW_CD_DESC
               FROM TBZ00_COMMCD
              WHERE LG_CD = 'G03002'
               --AND  SUBSTR(SM_CD,1,1) = 'S'
          ) b		
         WHERE 	b.MAINRAW_CD = a.MAINRAW_CD(+)		
        ]]>
    </query>
		<query id="UIG020COMM_01.callStatment" desc="按铁合金或辅原料或废料实绩表的CODE更新或插入单条记录  " fetchSize="10">
        <![CDATA[
		    {call UP_IN_TBG03_MAINRAW(?,?,?,?,?)}
        ]]>
    </query>
    <query id="UIG020COMM_CHEM.select" desc=" 成分实际  " fetchSize="10">
        <![CDATA[
	    SELECT CHEM_SEQ
              ,CHEM_CD 
              ,TO_CHAR(CHEM_VAL) CHEM_VAL
        FROM   
              TBB02_WORK_INGR  
        WHERE   substr(PROC_CD,2,1) = DECODE(?,'C','1',?)
          AND  CHARGE_NO = ?
		  ORDER BY CHEM_SEQ ASC
        ]]>
    </query>
    <query id="UIG020COMM_CHEM_CD.select" desc=" 成分范围  " fetchSize="10">
        <![CDATA[
	    /**  
       SELECT b.CHEM_CD
           ,TO_CHAR(a.CHEM_AIM) CHEM_AIM
           ,TO_CHAR(a.CHEM_MIN) CHEM_MIN
           ,TO_CHAR(a.CHEM_MAX) CHEM_MAX
           FROM TBB01_FAC_INGR a,
              (
                SELECT  CHEM_CD ,STL_GRD
                  FROM   TBB02_WORK_INGR a,TBG02_CHARGE_COMM b  
                 WHERE substr( PROC_CD,2,1) = DECODE(?,'C','1',?)
                   AND   a.CHARGE_NO = b.CHARGE_NO 
                   AND   b.CHARGE_NO = ?
                 GROUP BY CHEM_CD,STL_GRD 
              ) b  
          WHERE b.CHEM_CD =  a.CHEM_CD(+) 
           AND  b.STL_GRD  =  a.FAC_STL_GRD(+) **/
			/**
			SELECT b.CHEM_CD
           --,TO_CHAR(a.CHEM_AIM) CHEM_AIM
			,TO_CHAR(b.CHEM_VAL) CHEM_AIM
           ,TO_CHAR(a.CHEM_MIN) CHEM_MIN
           ,TO_CHAR(a.CHEM_MAX) CHEM_MAX
           
           FROM TBB01_FAC_INGR a,
              (
                SELECT  CHEM_CD ,STL_GRD,CHEM_VAL
                  FROM   TBB02_WORK_INGR a,TBG02_CHARGE_COMM b ,
                         (
                            SELECT MAX(CHEM_SEQ) CHEM_SEQ FROM TBB02_WORK_INGR 
                            WHERE  substr( PROC_CD,2,1) = DECODE(?,'C','1',?)
                              AND CHARGE_NO = ?
                            
                         )  c  
                 WHERE  a.CHEM_SEQ = c.CHEM_SEQ
                   AND  substr( a.PROC_CD,2,1) = ?
                   AND   a.CHARGE_NO = b.CHARGE_NO 
                   AND   b.CHARGE_NO = ?
                 GROUP BY CHEM_CD,STL_GRD ,CHEM_VAL
              ) b  
          WHERE b.CHEM_CD =  a.CHEM_CD(+) 
           AND  b.STL_GRD  =  a.FAC_STL_GRD(+)   **/
           
            SELECT A.CHEM_CD
			       ,A.CHEM_VAL
			       ,B.FAC_CHEM_VAL
			       ,C.TAFAC_CHEM_VAL
			       ,D.SPEC_CHEM_VAL
			  FROM (SELECT CHEM_CD, STL_GRD, to_char(CHEM_VAL) CHEM_VAL
			          FROM TBB02_WORK_INGR a,
			               TBG02_CHARGE_COMM b,
			               (SELECT MAX(CHEM_SEQ) CHEM_SEQ
			                  FROM TBB02_WORK_INGR
			                 WHERE substr(PROC_CD, 2, 1) = DECODE(?,'C','1',?)
			                   AND CHARGE_NO = ?
			                
			                ) c
			         WHERE a.CHEM_SEQ = c.CHEM_SEQ
			           AND substr(a.PROC_CD, 2, 1) = DECODE(?,'C','1',?)
			           AND a.CHARGE_NO = b.CHARGE_NO
			           AND b.CHARGE_NO = ?
			         GROUP BY CHEM_CD, STL_GRD, CHEM_VAL
			        ) A
			       ,(SELECT T.CHEM_CD
			               ,NVL(T.CHEM_MIN,0)||'~'||T.CHEM_MAX FAC_CHEM_VAL
			               ,T.FAC_STL_GRD 
			        FROM TBB01_FAC_INGR T)B
			       ,(SELECT T.CHEM_CD
			               ,NVL(T.CHEM_MIN,0)||'~'||T.CHEM_MAX TAFAC_CHEM_VAL
			               ,T.TAFAC_STL_GRD
			        FROM TBB01_TAFAC_INGR T)C
			       ,(SELECT T.CHEM_CD
			               ,NVL(T.CHEM_MIN,0)||'~'||T.CHEM_MAX SPEC_CHEM_VAL
			               ,T.SPEC_STL_GRD
			        FROM tbb01_spec_ingr t)D
			       
			 WHERE '1' = '1'
			 AND   A.CHEM_CD = B.CHEM_CD(+)
			 AND   A.STL_GRD = B.FAC_STL_GRD(+)
			 AND   A.CHEM_CD = C.CHEM_CD(+)
			 AND   A.STL_GRD = C.TAFAC_STL_GRD(+)
			 AND   A.CHEM_CD = D.CHEM_CD(+)
			 AND   A.STL_GRD = D.SPEC_STL_GRD(+)
        ]]>
    </query>
	<query id="UIG020COMM_CHEM_SPE.select" desc=" 特殊要求成分信息查询 " fetchSize="10">
		<![CDATA[
		    SELECT DISTINCT A.CHARGE_NO,B.ORD_NO||B.ORD_SEQ ORD_NO,C.CHEM_CD,NVL(C.CHEM_MIN,0)||'-'||NVL(C.CHEM_MAX,1) CHEM_VALUE
			FROM TBG02_CHARGE_COMM A
				 ,TBG02_SLAB_COMM B
				 ,TBB01_ORD_INGR C
				 ,TBB01_ORD_HEAD D
			WHERE A.CHARGE_NO = SUBSTR(B.SLAB_NO,1,10)   
			AND   B.ORD_NO = C.ORD_NO
			AND   B.ORD_SEQ = C.ORD_SEQ
			AND   B.ORD_NO = D.ORD_NO
			AND   B.ORD_SEQ = D.ORD_SEQ  
			AND   C.DSN_KIND = '1'
			AND   D.CUST_SPEC_NO IS NOT NULL
			AND   A.CHARGE_NO = ?
			GROUP BY A.CHARGE_NO,B.ORD_NO,B.ORD_SEQ,C.CHEM_CD,C.CHEM_MIN,C.CHEM_MAX 
			ORDER BY ORD_NO
		]]>
	</query>
  <query id="UIG020COMM_GROUP.select" desc=" 公共的组信息查询 " fetchSize="10">
		<![CDATA[
		 SELECT SM_CFNM    label      
              , SM_CD   value 
           FROM   TBZ00_COMMCD   
          WHERE LG_CD = 'Z05002' 
		]]>
	</query>
	<query id="UIG020COMM_SHIFT.select" desc=" 公共的班次信息查询 " fetchSize="10">
		<![CDATA[
		  SELECT SM_CFNM    label      
               , SM_CD   value 
           FROM  TBZ00_COMMCD   
          WHERE LG_CD = 'Z05001' 
		]]>
	</query>
	<query id="UIG020COMM_MAINRAW.select" desc=" 公共的班次信息查询 " fetchSize="10">
		<![CDATA[
			SELECT CHARGE_NO , PROC_CD,MAINRAW_CD FROM TBG03_MAINRAW 
			WHERE MAINRAW_CD = ?  
			AND SUBSTR(PROC_CD,2,1) = ? 
			AND CHARGE_NO = ? 
		]]>
	</query>
	<query id="UIG020COMM_MAINRAW.update" desc=" 公共的班次信息查询 " fetchSize="10">
		<![CDATA[
			UPDATE TBG03_MAINRAW SET
			MAINRAW_WGT = ?   
            WHERE CHARGE_NO = ?
            AND SUBSTR(PROC_CD,2,1) = ?
            AND MAINRAW_CD = ?
		]]>
	</query>
	<query id="UIG020COMM_MAINRAW.insert" desc=" 公共的班次信息查询 " fetchSize="10">
		<![CDATA[
			INSERT INTO  TBG03_MAINRAW( MAINRAW_CD,MAINRAW_CD_DESC, MAINRAW_WGT,PROC_CD,CHARGE_NO)  
			values(?,?,?, 'J'||?,?)
		]]>
	</query>
</queryMap>