XinYu_LZMES/.svn/pristine/3d/3d0ccda3e32c65eaddb62a14e2554557ade9a37f.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="原料库入库管理">
	<query id="UIM010040_01.SELECT"  desc="查询库存区域">
		<![CDATA[
		select distinct(a.area_no),a.area_name||'('
		|| (select min(clf_row) from c_tbk08_coil_yard where area_no = a.AREA_NO) 
		||'-'|| (select max(clf_row) from c_tbk08_coil_yard where area_no = a.AREA_NO) ||')' area_name 
		from c_tbk08_coil_yard_area a,c_tbk08_coil_yard y
		 where a.AREA_NO = y.AREA_NO and a.area_type = ?
		 order by a.AREA_NO
		 ]]>
	</query>
	<query id="UIM010040_02.SELECT" desc="查询钢卷状态">
		<![CDATA[
SELECT       A.STL_GRD
            ,A.COIL_THK
            ,A.COIL_WTH
            ,A.COIL_LEN
            ,A.ACT_WGT
            ,DECODE(A.CR_PLANT_TRS_CLF,'Z','常化','') CR_PLANT_TRS_CLF
            ,A.CAL_WGT
            ,A.COIL_INDIA
            ,A.COIL_OUTDIA
            ,A.CRK_CD1
            ,A.CRK_CD2
            ,A.CRK_CD3
            ,A.CRK_CD4
            ,A.CRK_CD5
            ,DECODE(H.EDGE_PROC_YN , NULL , 'N' , H.EDGE_PROC_YN) EDGE_PROC_YN
            ,H.C_SPEC_STL_GRD
            ,(SELECT T1.SPM_RMK FROM TBH02_COIL_COMM T1 WHERE T1.OLD_SAMPL_NO=A.OLD_SAMPL_NO AND ROWNUM=1) RZBZ
            ,(SELECT T2.SLAB_DCS_DETAIL FROM TBG02_SLAB_COMM T2 WHERE T2.SLAB_NO = T1.SLAB_NO AND ROWNUM = 1) BPBZ
            ,F.FM_EXT_STRIP_CROWN_AVG HC_TU_DU
       		,F.FM_STRIP_WDG_AVG HC_XIE_XIN
            ,DECODE(A.CUR_PROG_CD,'PAB','基料卷入库待机','PRC','基料卷充当待机','PCA','酸轧指示待机','PCB','酸轧轧制待机','PCC','酸轧卷取待机') CUR_PROG_CD
            ,DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格',NULL,'待判定') TOT_DEC_GRD
            ,A.FM_EXT_STRIP_TEMP_AVG
            ,A.DC_TEMP_AVG
            ,(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) YARD_ENTRY_REG
            ,DECODE(A.YARD_ENTRY_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') YARD_ENTRY_SHIFT
            ,DECODE(A.YARD_ENTRY_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') YARD_ENTRY_GROUP
            ,C_PKG_UIM.GET_STDTIMESTR(A.YARD_ENTRY_USE_TIME) YARD_ENTRY_USE_TIME
          --,TO_CHAR(TO_DATE(A.YARD_ENTRY_USE_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') YARD_ENTRY_USE_TIME
            ,A.COIL_NO,A.CUR_LOAD_LOC,A.ORD_FL,B.ORD_THK||'*'||B.ORD_WTH DHOUKUAN,B.ORD_LEN,B.ORD_UNIT_WGT
            ,DECODE(A.ORD_FL,'1','订单材','2','余材') ORD_FLNM
            ,A.ORD_NO,A.ORD_SEQ
            ,A.C_EXTSHAPE_DEC_GRD
            ,A.CRK_CD_DESC
FROM         C_TBK02_COIL_COMM      A
            ,TBE02_ORD_PRC          B
            ,TBH02_MILL_RESULT 		F
       		,TBH02_COIL_COMM   		T1
       		,tbb01_ord_head h
--WHERE        A.OLD_ORD_NO           =         B.ORD_NO(+)  
--AND          A.OLD_ORD_SEQ          =         B.ORD_SEQ(+)
WHERE        A.ORD_NO           =         B.ORD_NO(+)  
AND          A.ORD_SEQ          =         B.ORD_SEQ(+)
and          A.ORD_NO           =         H.ORD_NO(+)  
AND          A.ORD_SEQ          =         H.ORD_SEQ(+)
AND 		 A.COIL_NO 				= 			T1.OLD_SAMPL_NO
AND 		 T1.COIL_NO 			= 			F.COIL_NO
AND          A.COIL_STAT            <>        '3'
AND          A.OLD_SAMPL_NO         =         ?
		]]>
	</query>
	<query id="UIM010040_06.SELECT" desc="查询钢卷状态">
		<![CDATA[
SELECT       A.STL_GRD
            ,A.COIL_THK
            ,A.COIL_WTH
            ,A.COIL_LEN
            ,DECODE(A.CR_PLANT_TRS_CLF,'Z','常化','') CR_PLANT_TRS_CLF
            ,A.ACT_WGT
            ,A.CAL_WGT
            ,A.COIL_INDIA
            ,A.COIL_OUTDIA
            ,A.CRK_CD1
            ,A.CRK_CD2
            ,A.CRK_CD3
            ,A.CRK_CD4
            ,A.CRK_CD5
            ,DECODE(A.CUR_PROG_CD,'PAB','基料卷入库待机','PRC','基料卷充当待机','PCA','酸轧指示待机','PCB','酸轧轧制待机','PCC','酸轧卷取待机') CUR_PROG_CD
            ,DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格',NULL,'待判定') TOT_DEC_GRD
            ,A.FM_EXT_STRIP_TEMP_AVG
            ,A.DC_TEMP_AVG
            ,(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) YARD_ENTRY_REG
            ,DECODE(A.YARD_ENTRY_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') YARD_ENTRY_SHIFT
            ,DECODE(A.YARD_ENTRY_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') YARD_ENTRY_GROUP
            ,C_PKG_UIM.GET_STDTIMESTR(A.YARD_ENTRY_USE_TIME) YARD_ENTRY_USE_TIME
            ,A.COIL_NO,A.CUR_LOAD_LOC,A.ORD_FL,B.ORD_THK||'*'||B.ORD_WTH DHOUKUAN,B.ORD_LEN,B.ORD_UNIT_WGT
            ,DECODE(A.ORD_FL,'1','订单材','2','余材') ORD_FLNM
            ,A.ORD_NO,A.ORD_SEQ
            ,A.C_EXTSHAPE_DEC_GRD
            ,A.CRK_CD_DESC
FROM         C_TBK02_COIL_COMM      A
            ,TBE02_ORD_PRC          B
--WHERE        A.OLD_ORD_NO           =         B.ORD_NO(+)  
--AND          A.OLD_ORD_SEQ          =         B.ORD_SEQ(+)
WHERE        A.ORD_NO           =         B.ORD_NO(+)  
AND          A.ORD_SEQ          =         B.ORD_SEQ(+)
AND          A.COIL_STAT            <>        '3'
AND          A.OLD_SAMPL_NO         =         ?
		]]>
	</query>
	<query id="UIM010040_22.SELECT" desc="查询钢卷信息、做了生产判定">
		<![CDATA[
SELECT  QQ.*, CC.VALUE_C, CC.VALUE_Mn, CC.VALUE_S,CC.VALUE_P,CC.VALUE_Si,CC.VALUE_Ti,CC.VALUE_N
 from (SELECT  A.OLD_SAMPL_NO      
 			,A.STL_GRD
 			,B.C_SPEC_ABBSYM SPEC_ABBSYM
 			,DECODE(A.CR_PLANT_TRS_CLF,'Z','常化','') CR_PLANT_TRS_CLF
            ,A.COIL_THK
            ,A.COIL_WTH
            ,A.COIL_LEN
            ,A.ACT_WGT
            ,A.CAL_WGT
            ,A.COIL_INDIA
            ,A.COIL_OUTDIA
            ,DECODE(H.EDGE_PROC_YN, NULL, 'N', H.EDGE_PROC_YN) EDGE_PROC_YN
            ,H.C_SPEC_STL_GRD
            ,A.CRK_CD1
            ,A.CRK_CD2
            ,A.CRK_CD3
            ,A.CRK_CD4
            ,A.CRK_CD5
            , t1.SPM_RMK RZBZ,
               t1.CJ_REMARK,
               t1.DEAL_REMARK,
               t1.CPCJ_REMARK
            ,(SELECT T2.SLAB_DCS_DETAIL FROM TBG02_SLAB_COMM T2 WHERE T2.SLAB_NO = T1.SLAB_NO AND ROWNUM = 1) BPBZ
            ,F.FM_EXT_STRIP_CROWN_AVG HC_TU_DU
       		,F.FM_STRIP_WDG_AVG HC_XIE_XIN
            ,DECODE(A.CUR_PROG_CD,'PAB','基料卷入库待机','PRC','基料卷充当待机','PCA','酸轧指示待机','PCB','酸轧轧制待机','PCC','酸轧卷取待机') CUR_PROG_CD
            ,DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格',NULL,'待判定') TOT_DEC_GRD
            ,A.FM_EXT_STRIP_TEMP_AVG
            ,A.DC_TEMP_AVG
            ,(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) YARD_ENTRY_REG
            ,DECODE(A.YARD_ENTRY_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') YARD_ENTRY_SHIFT
            ,DECODE(A.YARD_ENTRY_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') YARD_ENTRY_GROUP
            ,C_PKG_UIM.GET_STDTIMESTR(A.YARD_ENTRY_USE_TIME) YARD_ENTRY_USE_TIME
          --,TO_CHAR(TO_DATE(A.YARD_ENTRY_USE_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') YARD_ENTRY_USE_TIME
            ,A.COIL_NO,A.CUR_LOAD_LOC,A.ORD_FL,B.ORD_THK||'*'||B.ORD_WTH DHOUKUAN,B.ORD_LEN,B.ORD_UNIT_WGT
            ,DECODE(A.ORD_FL,'1','订单材','2','余材') ORD_FLNM
            ,A.ORD_NO,A.ORD_SEQ
            ,A.C_EXTSHAPE_DEC_GRD
            ,A.CRK_CD_DESC,A.ORI_ACT_WGT
FROM         C_TBK02_COIL_COMM      A
            ,TBE02_ORD_PRC          B
            ,TBH02_MILL_RESULT 		F
       		,TBH02_COIL_COMM   		T1
       		,tbb01_ord_head    h
WHERE        A.OLD_ORD_NO           =         B.ORD_NO(+)  
AND          A.OLD_ORD_SEQ          =         B.ORD_SEQ(+)
and 		A.ORD_NO 				= 			H.ORD_NO(+)
AND 		A.ORD_SEQ 				= 			H.ORD_SEQ(+)
AND 		 A.COIL_NO 				= 			T1.OLD_SAMPL_NO
AND 		 T1.COIL_NO 			= 			F.COIL_NO
AND          A.COIL_STAT            <>        '3'
AND          A.OLD_SAMPL_NO         =         ?) QQ 
full join (select A1.OLD_SAMPL_NO,
       max(DECODE(WI.CHEM_CD, 'C', WI.CHEM_VAL)) VALUE_C,
       max(DECODE(WI.CHEM_CD, 'Mn', WI.CHEM_VAL)) VALUE_Mn,
       max(DECODE(WI.CHEM_CD, 'S', WI.CHEM_VAL)) VALUE_S,
       max(DECODE(WI.CHEM_CD, 'P', WI.CHEM_VAL)) VALUE_P,
       max(DECODE(WI.CHEM_CD, 'Si', WI.CHEM_VAL)) VALUE_Si,
       max(DECODE(WI.CHEM_CD, 'Ti', WI.CHEM_VAL)) VALUE_Ti,
       max(DECODE(WI.CHEM_CD, 'N', WI.CHEM_VAL)) VALUE_N
     from c_tbk02_coil_comm   A1,
          TBB02_WORK_INGR     WI,
          TBG02_CHARGE_COMM_D D
    where  A1.OLD_SAMPL_NO =  ?
      AND A1.COIL_STAT <> '3'
      AND WI.CHARGE_NO = D.CHARGE_NO
      AND WI.PROC_CD || WI.CHEM_SEQ = D.LST_INGR_CD
      AND SUBSTR(A1.SLAB_NO, 1, 10) = WI.CHARGE_NO
      AND WI.CHEM_CD IN ('C','Mn','S','P','Si','Ti','N')
    GROUP BY A1.OLD_SAMPL_NO) CC
 on QQ.OLD_SAMPL_NO = CC.OLD_SAMPL_NO
		]]>
	</query>
	<query id="UIM010040_03.SELECT" desc="查询垛位是否存在钢卷">
		<![CDATA[
		select coil_no  from c_tbk08_coil_yard where CLF_NAME = substr(?,0,1) and CLF_COL = substr(?,3,2)
        and CLF_ROW = substr(?,7,2) and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')
		]]>
	</query>
	<query id="UIM010040_04.SELECT" desc="查询钢卷状态">
		<![CDATA[
		SELECT K.CUR_PROG_CD,K.BEF_PROG_CD,K.ORD_NO,K.ORD_SEQ FROM C_TBK02_COIL_COMM K WHERE K.COIL_NO = ?
		]]>
	</query>
	<query id="UIM010040_05.SELECT" desc="查询钢卷其他信息">
		<![CDATA[
		SELECT substr(A.SLAB_NO,1,10) HEAT_NO
		,DECODE(A.INGR_DEC_GRD,'0','综合判定取消','1','合格','2','不合格') INGR_DEC_GRD
        ,DECODE(A.MATLQLTY_DEC_GRD,'0','无性能要求','1','合格','2','不合格') MATLQLTY_DEC_GRD
       , DECODE(A.SIZE_DEC_RST,'1','合格','2','不合格') SIZE_DEC_RST
       , DECODE(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') EXTSHAPE_DEC_GRD
       , DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST
        FROM C_TBK02_COIL_COMM A WHERE A.COIL_NO = ? 
        AND A.COIL_STAT <> '3'
		]]>
	</query>
	<query id="UIM010040_01.UPDATE" desc="更改原料钢卷信息">
		<![CDATA[
UPDATE      C_TBK02_COIL_COMM     K 
SET         K.COIL_THK            =      ?
           ,K.COIL_WTH            =      ?
           ,K.COIL_LEN            =      ?
           ,K.ACT_WGT             =      ?
           ,K.CAL_WGT             =      ?
           ,K.COIL_INDIA          =      ?
           ,K.COIL_OUTDIA         =      ?
           ,K.CRK_CD1             =      ?
           ,K.CRK_CD2             =      ?
           ,K.CRK_CD3             =      ?
           ,K.CRK_CD4             =      ?
           ,K.CRK_CD5             =      ?
           ,K.FM_EXT_STRIP_TEMP_AVG =    ?
           ,K.DC_TEMP_AVG         =      ?
           ,K.UPD_REG             =      ?
           ,K.UPD_USE_TIME        =      TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
           ,K.C_EXTSHAPE_DEC_GRD  =      ?
           ,K.CRK_CD_DESC         =      ?
WHERE       K.COIL_NO             =      ?
		]]>
	</query>
	<query id="UIM010040_02.UPDATE" desc="降余材">
		<![CDATA[
UPDATE      C_TBK02_COIL_COMM      K 
SET         K.ORD_NO               =      ''
           ,K.ORD_SEQ              =      ''
           ,K.ORD_FL               =      2
           ,K.BEF_PROG_CD          =      K.CUR_PROG_CD
           ,K.BEF_PROG_CD_DTIME    =      K.CUR_PROG_CD_DTIME
           ,K.BEF_PROG_CD_PGM      =      K.CUR_PROG_CD_PGM
           ,K.CUR_PROG_CD          =      'PRC'
           ,K.CUR_PROG_CD_DTIME    =      TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
           ,K.CUR_PROG_CD_PGM      =      'UIM010040'
           ,K.INSTR_COIL_THK       =      ''
           ,K.INSTR_COIL_WTH       =      ''
           ,K.INSTR_COIL_LEN       =      ''
           ,K.INSTR_COIL_INDIA     =      ''
           ,K.INSTR_COIL_OUTDIA    =      ''
           ,K.INSTR_COIL_WGT       =      ''
           ,k.EXTSHAPE_DEC_GRD     =      '2'
           ,k.TOT_DEC_GRD          =      ''
WHERE       K.COIL_NO              =      ?
		]]>
	</query>
	
	<query id="UIM010040_03.UPDATE" desc="降余材">
		<![CDATA[
UPDATE      C_TBK02_COIL_COMM_D     K 
SET         K.ORD_NO                =     ''
           ,K.ORD_SEQ               =     ''
           ,K.ORD_FL                =     '2'
WHERE       K.COIL_NO               =     ?
		]]>
	</query>
</queryMap>