XinYu_RZMES/.svn/pristine/27/27329342b5e23aba463adff6ea7e7abf3f588a4b.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="slab缺陷录入与措施">
	
	<query id="UIB020220_01_INITSELECT" desc="板坯外观缺陷代码 BugResult">
		SELECT 
		  SM_CFNM
		, SM_CD
		, 'N' CHK1
		, 'N' CHK2
		FROM TBZ00_COMMCD WHERE LG_CD='G03004'
	</query>
	
	<query id="UIB020220_02_INITSELECT" desc="板坯修整方法 MendResult">
		 SELECT 
		 SM_CFNM
		,SM_CD
		,'N' CHK
		 FROM    TBZ00_COMMCD WHERE LG_CD='G03005' ORDER BY CD_SEQ
	</query>
	
	<query id="UIB020220_03_INITSELECT" desc="datagrid2的第二列初始化 ComboBug">
		SELECT    SM_CFNM LABEL
                 ,SM_CFNM VALUE
		FROM      TBZ00_COMMCD 
		WHERE     LG_CD  =  'G03004'
	</query>
	
	<query id="UIB020220_04_INITSELECT" desc="datagrid3的第二列初始化 ComboMend">	
		SELECT    SM_CFNM LABEL
	 	         ,SM_CFNM VALUE
		FROM  	  TBZ00_COMMCD 
		WHERE     LG_CD = 'G03005' 
	</query>
	
	<query id="UIB020220_05_INITSELECT" desc="抽查备注 SpotResult">
		 SELECT  'N' CHK
		 ,SM_CFNM
		 ,SM_CD
		 FROM    TBZ00_COMMCD WHERE LG_CD='G03008' ORDER BY CD_SEQ
	</query>
	
	<query id="UIB020220_07_INITSELECT" desc="修磨备注 CopeResult">	
		SELECT    'N' CHK
		 ,SM_CFNM
		 ,SM_CD
		 FROM  	 TBZ00_COMMCD WHERE LG_CD='G03009' ORDER BY CD_SEQ
	</query>
		
	<query id="UIB020220_06_INITSELECT"  desc="  QueXian">
		SELECT 
     	SM_CFNM||'T'    LABEL
	  , SM_CD||'T'      VALUE
    	FROM TBZ00_COMMCD WHERE LG_CD='G03004'
		UNION ALL    
		SELECT 
		SM_CFNM||'B'    LABEL
      , SM_CD||'B'      VALUE
		FROM TBZ00_COMMCD WHERE LG_CD='G03004'
	</query>
	
	<query  id="UIB020220_01.SELECT" desc="板坯实绩查询 SlabResult">
	 SELECT
        'N' CHK
      , T1.SLAB_NO
      --, T1.SLAB_STAT
      ,DECODE(COILSTAT(T1.SLAB_NO),'在库','2','3') SLAB_STAT
      , DECODE(T1.INSTR_SLAB_THK||'*'||T1.INSTR_SLAB_WTH||'*'||T1.INSTR_SLAB_LEN ,'**'
             , NULL ,  T1.INSTR_SLAB_THK||'*'||T1.INSTR_SLAB_WTH||'*'||T1.INSTR_SLAB_LEN
      ) INSTR_TWL                       --指示厚度×长度×长度
      , DECODE(T1.SLAB_THK||'*'||T1.SLAB_WTH||'*'||T1.SLAB_LEN , '**'
          , NULL , T1.SLAB_THK||'*'||T1.SLAB_WTH||'*'||T1.SLAB_LEN
        ) TWL     --实绩厚度×长度×长度
      , T1.SLAB_WGT                        --理论重量
      ,	T1.WEIGHT_WGT       --实际重量 
      , T1.STL_GRD                         --厂内钢号
      , DECODE(T1.BEF_STL_GRD , NULL , T1.STL_GRD , T1.BEF_STL_GRD)  BEF_STL_GRD    
      , T1.SLAB_RT                         --板坯去向
      , T1.HCR_CLF                         --指示HCR
      , NVL(T1.SLAB_CUT_DTIME ,'')   SLAB_CUT_DTIME   --切割时间
      , NVL(T1.SMS_YARD_EXT_DTIME, T1.SLAB_STOCK_ENT_DTIME)   SMS_YARD_EXT_DTIME   --出库时间
      , decode(T1.DIFF_STL_GRD_STA_END_YN , '1' , null , '2', 'Y','3','Y',T1.DIFF_STL_GRD_STA_END_YN)     DIFF_STL_GRD_STA_END_YN   --头尾板坯
      , T1.SURPLMATL_YN                    --错误板坯
      , T1.SLAB_INGR_DEC_GRD               --板坯成分判定等级
      , T1.SLAB_INGR_DEC_KIND
      , T1.EXTSHAPE_GRD                    --板坯外观等级
      , T1.SF_METHOD                       --板坯修整方法
      , T1.ORD_FL                          --余才区分
      , CASE WHEN T1.MATRL_END_CAUSE_CD = '5' THEN '否'
        ELSE '是' END    MATRL_END_CAUSE_CD
      , T2.CC_DEVNO               --铸机号  
      , T1.CRK_CD1||T1.CRK_CD1_LOC CRK1     --缺陷1
      , T1.CRK_CD2||T1.CRK_CD2_LOC CRK2     --缺陷2
      , T1.CRK_CD3||T1.CRK_CD3_LOC CRK3     --缺陷3
      , T1.CRK_CD4||T1.CRK_CD4_LOC CRK4     --缺陷4
      , T1.CRK_CD5||T1.CRK_CD5_LOC CRK5     --缺陷5
      , T1.CRK_CD6||T1.CRK_CD6_LOC CRK6     --缺陷6
      , T1.CRK_CD7||T1.CRK_CD7_LOC CRK7     --缺陷7
      , T1.CRK_CD8||T1.CRK_CD8_LOC CRK8     --缺陷8
      , T1.CRK_CD9||T1.CRK_CD9_LOC CRK9     --缺陷9
      , T1.CRK_CD10||T1.CRK_CD1_LOC CRK10   --缺陷10
      , T3.SPOT_CHECK_REMARK SPOT_CHECK_REMARK
      , T3.COPING_REMARK COPING_REMARK
      , T3.REG_DTIME REMARK_TIME
      , T1.EXTSHAPE_DEC_EMP_ID
      , S.GR_GROUP
      , T1.EXTSHAPE_DEC_DTIME
      , T1.SLAB_INGR_DEC_EMP_ID
      , T1.SLAB_INGR_DEC_DTIME
      , T1.SF_METHOD_STD_ID
      , T1.SF_METHOD_STD_DTIME
      , T1.SF_METHOD_ID
      , T1.SF_METHOD_DTIME
      , T1.SF_METHOD_END_ID
      , T1.SF_METHOD_END_DTIME
      
        -- , T1.LAST_SLAB_YN                               --头尾板坯
        -- , (T1.DIFF_STL_GRD_STA_END_YN = '2' OR T1.DIFF_STL_GRD_STA_END_YN = '3') --2为头板坯，3为尾板坯，只有头或者尾板坯出来
      , T1.EXTSHAPE_GRD   EXTSHAPE_GRD1    --板坯外观等级
      , T1.SF_METHOD      SF_METHOD1       --板坯修整方法
      , T1.CUR_LOAD_LOC                    --板坯位置
      , ''   ROWSTATUS2
      , ''   ROWSTATUS3
      --,ROUND(SUM(NVL(T1.WEIGHT_WGT,T1.SLAB_WGT/1000)) OVER (PARTITION BY T1.CHARGE_NO ORDER BY T1.SLAB_NO)/NVL((C.PFBALEWGT - C.BALELEAVEWGT),9999)*100,2) WEIGHT_RATE,
      --,ROUND((SELECT SUM(NVL(TA.WEIGHT_WGT,TA.SLAB_WGT/1000)) FROM TBG02_SLAB_COMM TA WHERE TA.SLAB_NO LIKE SUBSTR(T1.SLAB_NO,1,9)||'%' AND TA.SLAB_NO &lt;= T1.SLAB_NO AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5')))/NVL((C.PFBALEWGT - C.BALELEAVEWGT),9999)*100,2) WEIGHT_RATE
      --,ROUND((SELECT SUM(NVL(TA.WEIGHT_WGT,TA.SLAB_WGT/1000)) FROM TBG02_SLAB_COMM TA WHERE TA.SLAB_NO LIKE SUBSTR(T1.SLAB_NO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5')))/NVL((C.PFBALEWGT - C.BALELEAVEWGT),9999)*100,2) WEIGHT_RATE1
      FROM      TBG02_SLAB_COMM  T1  , TBG02_CHARGE_COMM T2, TBB02_SHIFT_GROUP S   ,  TBZ01_REMARK_COMM T3
       		    --,(SELECT * FROM STL_CCM_OPTINFO@XGEJGZXT_WGT UNION SELECT * FROM J#STL_CCM_OPTINFO@XGEJGZXT_WGT) C
      WHERE     SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO, 1, 9)
      --T1.SLAB_NO     LIKE  SUBSTR(T2.CHARGE_NO ,1,9)||'%'
      AND       T1.EXTSHAPE_DEC_EMP_ID = S.OPE_ID(+)
      AND		T1.SLAB_NO = T3.SLAB_NO(+)
      --AND 		SUBSTR(T1.SLAB_NO, 1, 9)||'A' = C.HEATNO(+)
      --AND       '0'||SUBSTR(T1.PROC_CD,2,1) = C.STATIONCODE(+)
      AND       T1.SLAB_NO     LIKE  ?
      AND       T1.SLAB_CUT_DTIME  BETWEEN  ? AND ?    --默认最近三天
      AND		NVL(CASE WHEN T1.SLAB_STAT !='3' THEN '' ELSE T1.SMS_YARD_EXT_DTIME END,'0000') BETWEEN ? AND ?
      AND		T2.CC_DEVNO like     ?
      AND       T1.SLAB_RT     like  ?
      AND       T1.HCR_CLF     like  ?
      AND 		(T1.MATRL_END_CAUSE_CD IS NULL OR T1.MATRL_END_CAUSE_CD NOT IN ('4','5'))
      ORDER   BY    T1.SLAB_NO  DESC
	</query>
	
	<query  id="UIB020220_11.SELECT" desc="按班组、操作人来查询 SlabResult">
		SELECT 'N' CHK,
			T1.SLAB_NO,
			T1.EXTSHAPE_DEC_EMP_ID,
			S.GR_GROUP,S.GR_SHIFT,
			T1.SLAB_STAT,
			CASE WHEN T1.MATRL_END_CAUSE_CD = '5' THEN '否' ELSE '是' END MATRL_END_CAUSE_CD,
			DECODE(T1.INSTR_SLAB_THK || '*' || T1.INSTR_SLAB_WTH || '*' || T1.INSTR_SLAB_LEN,'**',NULL,
		     T1.INSTR_SLAB_THK || '*' || T1.INSTR_SLAB_WTH || '*' || T1.INSTR_SLAB_LEN) INSTR_TWL --指示厚度×长度×长度
		  ,DECODE(T1.SLAB_THK || '*' || T1.SLAB_WTH || '*' || T1.SLAB_LEN,'**',NULL,
		     T1.SLAB_THK || '*' || T1.SLAB_WTH || '*' || T1.SLAB_LEN) TWL --实绩厚度×长度×长度
			,T1.SLAB_WGT --理论重量
			,T1.WEIGHT_WGT --实际重量
			,T1.STL_GRD --厂内钢号
			,DECODE(T1.BEF_STL_GRD, NULL, T1.STL_GRD, T1.BEF_STL_GRD) BEF_STL_GRD,
			T1.SLAB_RT --板坯去向
			,T1.HCR_CLF --指示HCR
			,NVL(T1.SLAB_CUT_DTIME, '') SLAB_CUT_DTIME --切割时间
			,decode(T1.DIFF_STL_GRD_STA_END_YN,'1',null,'2','Y','3','Y',T1.DIFF_STL_GRD_STA_END_YN) DIFF_STL_GRD_STA_END_YN --头尾板坯
			,T1.EXTSHAPE_GRD --板坯外观等级
			,T1.SF_METHOD --板坯修整方法
			,T1.SURPLMATL_YN --错误板坯
			,T1.CRK_CD1 || T1.CRK_CD1_LOC CRK1 --缺陷1
			,T1.CRK_CD2 || T1.CRK_CD2_LOC CRK2 --缺陷2
			,T1.CRK_CD3 || T1.CRK_CD3_LOC CRK3 --缺陷3
			,T1.CRK_CD4 || T1.CRK_CD4_LOC CRK4 --缺陷4
			,T1.CRK_CD5 || T1.CRK_CD5_LOC CRK5 --缺陷5
			,T1.CRK_CD6 || T1.CRK_CD6_LOC CRK6 --缺陷6
			,T1.CRK_CD7 || T1.CRK_CD7_LOC CRK7 --缺陷7
			,T1.CRK_CD8 || T1.CRK_CD8_LOC CRK8 --缺陷8
			,T1.CRK_CD9 || T1.CRK_CD9_LOC CRK9 --缺陷9
			,T1.CRK_CD10 || T1.CRK_CD1_LOC CRK10 --缺陷10
			,T1.ORD_FL --余才区分,
			,T1.SLAB_INGR_DEC_GRD --板坯成分判定等级,
			,T1.EXTSHAPE_GRD EXTSHAPE_GRD1 --板坯外观等级,
			,T1.SF_METHOD SF_METHOD1 --板坯修整方法,
			,T1.CUR_LOAD_LOC --板坯位置,
			,T1.EXTSHAPE_DEC_EMP_ID,
			T1.EXTSHAPE_DEC_DTIME,
			T1.SLAB_INGR_DEC_EMP_ID,
			T1.SLAB_INGR_DEC_DTIME,
			T1.SF_METHOD_STD_ID,
			T1.SF_METHOD_STD_DTIME,
			T1.SF_METHOD_ID,
			T1.SF_METHOD_DTIME,
			T1.SF_METHOD_END_ID,
			T1.SF_METHOD_END_DTIME,
			T2.CC_DEVNO, --铸机号
			'' ROWSTATUS2,
			'' ROWSTATUS3,
			T1.SLAB_INGR_DEC_KIND
			FROM TBG02_SLAB_COMM T1, TBG02_CHARGE_COMM T2, TBB02_SHIFT_GROUP S
			WHERE T1.EXTSHAPE_DEC_EMP_ID = S.OPE_ID(+)
			AND SUBSTR(T1.SLAB_NO, 1, 9) = SUBSTR(T2.CHARGE_NO, 1, 9)
			AND T1.SLAB_CUT_DTIME BETWEEN ? AND ?
			AND DECODE(S.GR_GROUP,null,'%','','%',S.GR_GROUP) like ?
			AND DECODE(T1.EXTSHAPE_DEC_EMP_ID,null,'%','','%',T1.EXTSHAPE_DEC_EMP_ID) LIKE ?
			AND T2.CC_DEVNO like ?
			AND T1.SLAB_RT like ?
			AND T1.HCR_CLF like ?
		ORDER BY T1.SLAB_NO DESC
	</query>
	<query id="UIB020220_02.UPDATE" desc="板坯缺陷录入">
		 UPDATE TBG02_SLAB_COMM     SET
		  EXTSHAPE_GRD = ?   --板坯外观等级
		, CRK_CD1 = ?  ,  CRK_CD1_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD2 = ?  ,  CRK_CD2_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD3 = ?  ,  CRK_CD3_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD4 = ?  ,  CRK_CD4_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD5 = ?  ,  CRK_CD5_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD6 = ?  ,  CRK_CD6_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD7 = ?  ,  CRK_CD7_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD8 = ?  ,  CRK_CD8_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD9 = ?  ,  CRK_CD9_LOC  = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, CRK_CD10= ?  ,  CRK_CD10_LOC = ?    --缺陷代码与缺陷位置(上表面T或下表面B)
		, EXTSHAPE_DEC_EMP_ID          = ?
		, EXTSHAPE_DEC_DTIME           = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		WHERE       SLAB_NO    =  ?
		AND         SLAB_STAT  =  '2'
	</query>
	
	<query id="UIB020220_03.UPDATE" desc="抽查，修磨备注修改">
		 UPDATE TBZ01_REMARK_COMM     SET
		  COPING_REMARK = ?   --修磨备注
		, SPOT_CHECK_REMARK = ?  --抽查备注
		, REG_DTIME  = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')    --修改时间
		, REG_ID = ?  		  --修改人
		WHERE       SLAB_NO    =  ?
	</query>
	
	<query id="UIB020220_07.UPDATE" desc="修磨核减">
		 update tbg02_slab_comm t set t.WEIGHT_WGT = (case
         	when t.WEIGHT_WGT is null then
          	  ROUND((1 - 0.0025) * SLAB_WGT / 1000, 3)
         	else
              ROUND((1 - 0.0025) * WEIGHT_WGT, 3)
            end)
 		 where t.SLAB_NO = ?
	</query>
	
	<query id="UIB020220_08.UPDATE" desc="全剥核减">
		 update tbg02_slab_comm t set t.WEIGHT_WGT = (case
                        when t.WEIGHT_WGT is null then
                         ROUND(t.SLAB_WGT / 1000 - 7.85 * ? * SLAB_WTH * SLAB_LEN / 1000000000,3)
                        else
                         ROUND(t.WEIGHT_WGT - 7.85 * ? * SLAB_WTH * SLAB_LEN / 1000000000,3)
                      end)
 	      where t.SLAB_NO = ?
	</query>
	
	<query id="UIB020220_03.INSERT" desc="抽查，修磨备注录入">
		 INSERT INTO TBZ01_REMARK_COMM  VALUES (?,?,?,TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS'),?)
	</query>
	
	<query id="UIB020220_08_SELECT" desc="根据缺陷代码查中文名">
		  SELECT  SM_CFNM
		FROM TBZ00_COMMCD WHERE LG_CD='G03004' AND SM_CD = ?
	</query>
	<query id="UIB020220_09_SELECT" desc="根据精整代码查中文名">
		  SELECT   SM_CFNM
     FROM  TBZ00_COMMCD WHERE LG_CD='G03005' AND SM_CD = ?
	</query>
	<query id="UIB020220_HIS.INSERT" desc="将板坯外观判定记录插入到板坯历史记录表中">
		 INSERT INTO TBB02_HIS_SLAB
			  (SLAB_NO,
			   SLAB_DCS_FLAG,
			   CUR_PROG_CD,
			   CUR_PROG_CD_PGM,
			   CUR_PROG_CD_DTIME,
			   ORG_ORD_NO,
			   ORG_ORD_SEQ,
			   ORD_NO,
			   ORD_SEQ,
			   ORD_FL,
			   STL_GRD,
			   BEF_STL_GRD,
			   SLAB_INGR_DEC_KIND,
			   SLAB_INGR_DEC_GRD,
			   EXTSHAPE_GRD,
			   SLAB_INGR_DEC_GRD_EXT,
			   EXTSHAPE_GRD_EXT,
			   SLAB_TOT_DEC_GRD,
			   REG_ID,
			   REG_DTIME,
			   INGR_EXT_NM,
			   INGR_EXT_DTIME,
			   HIS_DESC)
			  SELECT T.SLAB_NO,
			         '9',
			         T.CUR_PROG_CD,
			         T.CUR_PROG_CD_PGM,
			         T.CUR_PROG_CD_DTIME,
			         T.ORG_ORD_NO,
			         T.ORG_ORD_SEQ,
			         T.ORD_NO,
			         T.ORD_SEQ,
			         T.ORD_FL,
			         T.STL_GRD,
			         T.BEF_STL_GRD,
			         T.SLAB_INGR_DEC_KIND,
			         T.SLAB_INGR_DEC_GRD,
			         ?,
			         T.SLAB_INGR_DEC_GRD_EXT,
			         T.EXTSHAPE_GRD_EXT,
			         T.SLAB_TOT_DEC_GRD,
			         ?,
			         TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
			         T.INGR_EXT_NM,
			         T.INGR_EXT_DTIME,
			         ?
			    FROM TBG02_SLAB_COMM T
		   	WHERE T.SLAB_NO = ?
	</query>
		
	<query id="UIB020220_04.UPDATE" desc="精整待机">
		 UPDATE      TBG02_SLAB_COMM  SC
		 SET         SC.SF_METHOD_STD_ID       =    ?
		            ,SC.SF_METHOD_STD_DTIME    =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		 WHERE       SC.SLAB_NO                =    ?
		 AND         SLAB_STAT                 =    '2'
	</query>
	
	<query id="UIB020220_05.UPDATE" desc="精整操作">
		 UPDATE      TBG02_SLAB_COMM  SC
		 SET         SC.SF_METHOD_ID           =    ?
		            ,SC.SF_METHOD              =    ?
		            ,SC.SF_METHOD_DTIME        =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		 WHERE       SC.SLAB_NO                =    ? 
		 AND         SLAB_STAT                 =   '2'
	</query>
	
	<query id="UIB020220_06.UPDATE" desc="精整完成">
		 UPDATE      TBG02_SLAB_COMM  SC
		 SET         SC.SF_METHOD_END_ID       =    ?
		            ,SC.SF_METHOD_END_DTIME    =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
		 WHERE       SC.SLAB_NO                =    ? 
		AND         SLAB_STAT                  =   '2'
	</query>
	
	<query id="UIB020220_01.CALLSTATEMENT" desc="板坯切割时，其外观等级或修整方法发生变动时调用">
		{call NIB020221(?,?,?,?,?)}
	</query>
	
	<query id="UIB020220_03.CALLSTATEMENT" desc="改判将余材">
		{call GP_OS_COMM(?,?,?)}
	</query>
	
	<query id="UIB020220_02.SELECT" desc="用于发送信息到进程管理">
		SELECT   SLAB_NO , SLAB_WGT , ORD_NO , ORD_SEQ ,  CUR_PROG_CD , BEF_PROG_CD  
		FROM     TBG02_SLAB_COMM  
		WHERE    SLAB_NO      =    ? 
		AND      SLAB_STAT    =   '2'
	</query>
	
</queryMap>