XinYu_LZMES/.svn/pristine/7e/7e54fa2c5fd6519dc9ff74c93b48e1562f12d989.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="酸轧实绩查询">
	<query id="UIK020050_01.SELECT"  desc="查询酸轧作业实绩冷轧卷基础信息">
		<![CDATA[
		
		SELECT 'False' CHK,
           T.OLD_SAMPL_NO C_COIL_NO,
           --T.COIL_NO,
           T.MILL_DTIME,
           T.COIL_LEN,
           M.COIL_WGT ENT_COIL_WGT,
           T.ACT_WGT COIL_WGT,
           T.COIL_INDIA,
         --  R.NO_STRIP,
        --   R.NO_DEFECT,
           U.USERNAME MILL_REG_ID,
           T.CUT_EDGE_YN,
           T.INSTR_COIL_THK CLASSTHK,
           T.INSTR_COIL_THK,
       	   T.INSTR_COIL_WTH,
           T.COIL_THK,
           T.COIL_WTH,
           K.COIL_WTH ORI_WTH,
           K.COIL_WTH-T.COIL_WTH SUBWTH,
           T.SPEC_STL_GRD,
           T.WORK_OPE_DTIME,
           DECODE(T.MILL_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') MILL_SHIFT ,
               DECODE(T.MILL_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') MILL_GROUP
      FROM S_TBF03_SPEC_MILL M
           ,C_TBL02_COIL_COMM T
           ,C_TBK02_COIL_COMM K
           ,core_app_user U
     WHERE K.COIL_NO=t.H_COIL_NO1
     and   T.MILL_REG_ID = U.USERID(+)
     and   m.COIL_NO=k.OLD_SAMPL_NO
		   AND M.ROLL_MANA_NO||'&' LIKE :1||'%'
		   and substr(t.MILL_DTIME,1,8) between nvl(:2,'00000000') and nvl(:3,'99999999')
		   and t.OLD_SAMPL_NO||'&' like :4||'%'
		   and m.STATUS_CD<>'E'
		   ORDER BY M.ROLL_COIL_SEQ
		 ]]>
	</query>
	
	<query id="UIK020050_SHIFT.UPDATE"  desc="查询酸轧作业实绩冷轧卷基础信息">
		<![CDATA[
      UPDATE       C_TBL02_COIL_COMM        T
      SET          T.MILL_REG_ID            =   ?
                   ,T.MILL_SHIFT            =   ?
                   ,T.MILL_GROUP            =   ?
                   ,T.CUT_EDGE_YN           =   ?
                   ,T.MILL_DTIME            =   ?
                   ,T.WORK_OPE_DTIME        =   TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
      WHERE         T.COIL_NO               =   ?
		 ]]>
	</query>
	
	<query id="UIK020050_group.UPDATE"  desc="查询酸轧作业实绩冷轧卷基础信息">
		<![CDATA[
      UPDATE       C_TBL02_COIL_COMM        T
      SET         
                   T.MILL_GROUP            =   ?
                   ,t.mill_shift				=?
                   
                   ,T.WORK_OPE_DTIME        =   TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
      WHERE         T.COIL_NO               =   ?
		 ]]>
	</query>
	
		<query id="UIK020050_CUT.UPDATE"  desc="切边实绩更新">
		<![CDATA[
      UPDATE       C_TBL02_COIL_COMM        T
      SET         
                   T.CUT_EDGE_YN           =   'True'
                   
                 ----  ,T.WORK_OPE_DTIME        =   TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
      WHERE         T.COIL_NO               =   ?
		 ]]>
	</query>
		
	<query id="UIK020050_CUT_01.UPDATE"  desc="切边实绩更新">
		<![CDATA[
      UPDATE       C_TBL02_COIL_COMM        T
      SET         
                   T.CUT_EDGE_YN           =   'False'
                   
                 --  ,T.WORK_OPE_DTIME        =   TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
      WHERE         T.COIL_NO               =   ?
		 ]]>
	</query>
	<query id="UIK020050_02.SELECT"  desc="查询酸轧作业实绩">
		<![CDATA[
		select a.* from (
				select 1 roll_seq, r1.l_coil_no c_coil_no,L.H_COIL_NO1 H_COIL_NO, r1.order_no1 ord_no,r1.alloy1 stl_grd,r1.length1 h_coil_len,r1.cpl_begin1 cpl_begin		
				,r1.cpl_end1 cpl_end,r1.pl_width1 pl_width,r1.pl_speed_max1 pl_speed_max
				,r1.pl_speed_min1 pl_speed_min,r1.pl_speed_ave1 pl_speed_avg		
				,r1.rolling_begin1 rolling_begin,r1.rolling_end1 rolling_end		
				,r1.tcm_width1 tcm_width,r1.thick_ex1 thick_ex,r1.thick_setp1 thick_setup		
				,r1.thick_min1 thick_min,r1.thick_max1 thick_max,r1.thick_lmax1 thick_lmax		
				,r1.thick_lmin1 thick_lmin,r1.tcm_speed1 tcm_speed,r1.think_class1_1 think_class1		
				,r1.think_class2_1 think_class2,r1.think_class3_1 think_class3,r1.think_class4_1 think_class4		
				,r1.think_class5_1 think_class5,r1.flateness_class1_1 flateness_class1		
				,r1.flateness_class2_1 flateness_class2,r1.flateness_class3_1 flateness_class3		
				,r1.flateness_class4_1 flateness_class4,r1.flateness_class5_1 flateness_class5
		from c_tbl02_pltcm_result r1,C_TBL02_COIL_COMM L WHERE r1.L_COIL_NO = L.COIL_NO
		union		
				select 2 roll_seq, r2.l_coil_no c_coil_no,L.H_COIL_NO2 H_COIL_NO, r2.order_no2 ord_no,r2.alloy2 stl_grd,r2.length2 h_coil_len,r2.cpl_begin2 cpl_begin		
				,r2.cpl_end2 cpl_end,r2.pl_width2 pl_width,r2.pl_speed_max2 pl_speed_max		
				,r2.pl_speed_min2 pl_speed_min,r2.pl_speed_ave2 pl_speed_avg		
				,r2.rolling_begin2 rolling_begin,r2.rolling_end2 rolling_end		
				,r2.tcm_width2 tcm_width,r2.thick_ex2 thick_ex,r2.thick_setp2 thick_setup		
				,r2.thick_min2 thick_min,r2.thick_max2 thick_max,r2.thick_lmax2 thick_lmax	
				,r2.thick_lmin2 thick_lmin,r2.tcm_speed2 tcm_speed,r2.think_class1_2 think_class1	
				,r2.think_class2_2 think_class2,r2.think_class3_2 think_class3,r2.think_class4_2 think_class4		
				,r2.think_class5_2 think_class5,r2.flateness_class1_2 flateness_class1		
				,r2.flateness_class2_2 flateness_class2,r2.flateness_class3_2 flateness_class3		
				,r2.flateness_class4_2 flateness_class4,r2.flateness_class5_2 flateness_class5
		from c_tbl02_pltcm_result r2,C_TBL02_COIL_COMM L WHERE r2.L_COIL_NO = L.COIL_NO
		union		
				select 3 roll_seq, r3.l_coil_no c_coil_no,L.H_COIL_NO3 H_COIL_NO, r3.order_no3 ord_no,r3.alloy3 stl_grd,r3.length3 h_coil_len,r3.cpl_begin3 cpl_begin		
				,r3.cpl_end3 cpl_end,r3.pl_width3 pl_width,r3.pl_speed_max3 pl_speed_max	
				,r3.pl_speed_min3 pl_speed_min,r3.pl_speed_ave3 pl_speed_avg		
				,r3.rolling_begin3 rolling_begin,r3.rolling_end3 rolling_end		
				,r3.tcm_width3 tcm_width,r3.thick_ex3 thick_ex,r3.thick_setp3 thick_setup		
				,r3.thick_min3 thick_min,r3.thick_max3 thick_max,r3.thick_lmax3 thick_lmax		
				,r3.thick_lmin3 thick_lmin,r3.tcm_speed3 tcm_speed,r3.think_class1_3 think_class1		
				,r3.think_class2_3 think_class2,r3.think_class3_3 think_class3,r3.think_class4_3 think_class4		
				,r3.think_class5_3 think_class5,r3.flateness_class1_3 flateness_class1		
				,r3.flateness_class2_3 flateness_class2,r3.flateness_class3_3 flateness_class3	
				,r3.flateness_class4_3 flateness_class4,r3.flateness_class5_3 flateness_class5
		from c_tbl02_pltcm_result r3,C_TBL02_COIL_COMM L WHERE r3.L_COIL_NO = L.COIL_NO		
				) a,c_Tbf03_Spec_Mill m,C_TBL02_PLTCM_RESULT R where substr(a.c_coil_no,1,11) = substr(m.c_coil_no,1,11) and m.roll_mana_no||'&' LIKE :1||'%'		
				and (a.roll_seq < (select r.no_strip from c_tbl02_pltcm_result r where r.l_coil_no = a.c_coil_no)		
				or a.roll_seq = (select r.no_strip from c_tbl02_pltcm_result r where r.l_coil_no = a.c_coil_no))
				and a.c_coil_no = R.L_COIL_NO
				and substr(R.MILL_DTIME,1,8) between nvl(:2,'00000000') and nvl(:3,'99999999')
		   		and R.L_COIL_NO||'&' like :4||'%'
		order by a.c_coil_no,a.roll_seq
		 ]]>
	</query>
	<query id="UIK020050_03.SELECT"  desc="查询酸轧作业实绩缺陷信息">
		<![CDATA[
		select d.* from (
				select l_coil_no c_coil_no,1 defect_seq,area1 area,defect_type1 defect_type,intensity1 intensity		
				,start_pos1 start_pos,end_pos1 end_pos from c_tbl02_pltcm_result union	
				select l_coil_no c_coil_no,2 defect_seq,area2 area,defect_type2 defect_type,intensity2 intensity		
				,start_pos2 start_pos,end_pos2 end_pos from c_tbl02_pltcm_result union	
				select l_coil_no c_coil_no,3 defect_seq,area3 area,defect_type3 defect_type,intensity3 intensity		
				,start_pos3 start_pos,end_pos3 end_pos from c_tbl02_pltcm_result union	
				select l_coil_no c_coil_no,4 defect_seq,area4 area,defect_type4 defect_type,intensity4 intensity		
				,start_pos4 start_pos,end_pos4 end_pos from c_tbl02_pltcm_result union	
				select l_coil_no c_coil_no,5 defect_seq,area5 area,defect_type5 defect_type,intensity5 intensity
				,start_pos5 start_pos,end_pos5 end_pos from c_tbl02_pltcm_result
				) d,c_tbf03_spec_mill m,C_TBL02_PLTCM_RESULT R where substr(d.c_coil_no,1,11) = substr(m.c_coil_no,1,11) and m.roll_mana_no||'&' LIKE :1||'%'
				and (d.defect_seq < (select r.no_defect from c_tbl02_pltcm_result r where r.l_coil_no = d.c_coil_no)
				or d.defect_seq = (select r.no_defect from c_tbl02_pltcm_result r where r.l_coil_no = d.c_coil_no)
				)
				and d.c_coil_no = R.L_COIL_NO
				and substr(R.MILL_DTIME,1,8) between nvl(:2,'00000000') and nvl(:3,'99999999')
		   		and R.L_COIL_NO||'&' like :4||'%'
		order by d.c_coil_no,d.defect_seq
		 ]]>
	</query>
</queryMap>