XinYu_LZMES/.svn/pristine/4d/4d1cff6042e53b5a6c0f6c591072b019e793136b.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发货实绩录入与装车明细">
	<query id="UIJ030060_xg3qTblSMP.select"  desc="查询中间库式样号">
		<![CDATA[
		select SMP_NO from c_tbl02_coil_comm T where old_sampl_no = ? and rownum = 1 
		 ]]>
	</query>
	<query id="UIJ030060_xg3qTbcSMP.select"  desc="查询成品库式样号">
		<![CDATA[
		select SMP_NO from c_tbc02_coil_comm T where old_sampl_no = ? and rownum = 1 
		 ]]>
	</query>
	<query id="UIJ030060_youteSMP11111.select"  desc="判断式样号在优特数据库中是否存在">
		<![CDATA[
		select SMP_NO from IF_RAW_MTR_COIL_PFMC T WHERE T.SMP_NO = ?  
		 ]]>
	</query>
	
		<query id="UIJ030060_youteSMP.select"  desc="判断式样号在优特数据库中是否存在">
		<![CDATA[
		select SPECIMEN_NO from QCM_JHY_PHY_RESULT T WHERE SPECIMEN_NO = ?  
		 ]]>
	</query>
	<query id="UIJ030060_xg3qTblCHG.select"  desc="查询中间库炉号">
		<![CDATA[
		select substr(slab_no, 1,10) chgNo from c_tbl02_coil_comm where old_sampl_no = ? and rownum = 1 
		 ]]>
	</query>
	<query id="UIJ030060_xg3qTbcCHG.select"  desc="查询成品库炉号">
		<![CDATA[
		select substr(slab_no, 1,10) chgNo from c_tbc02_coil_comm where old_sampl_no = ? and rownum = 1 
		 ]]>
	</query>
	<query id="UIJ030060_youteCHG.select"  desc="判断炉号在优特数据库中是否存在">
		<![CDATA[
		select HEATNO from IF_RAW_MTR_COIL_CHEM where HEATNO = ?  
		 ]]>
	</query>
	<query id="UIJ030060_xg3qQLTY111111.select"  desc="查询三期材质实绩">
		<![CDATA[
		SELECT t.smp_no, t.qlty_cd, t1.qlty_cd_cfnm, t.qlty_val_wk,
          t.qlty_unit_wk, '单位名称' pfmc_unit_name, t.qlty_type_wk,
          (SELECT sm_cfnm
              FROM tbz00_commcd
             WHERE lg_cd = 'B01106'
                   AND sm_cd = t.qlty_cd) sm_cfnm, t.reg_dtime, '备注' remark,
          '测试人员' create_opr
	     FROM tbb02_work_qlty t, tbb01_seq_qlty t1
	    WHERE t.qlty_cd = t1.qlty_cd
	          AND t.smp_no = ?
		 ]]>
	</query>
	
		<query id="UIJ030060_xg3qQLTY.select"  desc="查询三期材质实绩">
		<![CDATA[
		SELECT rownum xh,
       t.smp_no,
       t.qlty_cd,
       t1.qlty_cd_cfnm,
       decode(substr(t.qlty_val_wk,1,1),'.','0'||t.qlty_val_wk,t.qlty_val_wk) qlty_val_wk,
       t.qlty_unit_wk,
       '单位名称' pfmc_unit_name,
       t.qlty_type_wk,
       (SELECT sm_cfnm
          FROM tbz00_commcd
         WHERE lg_cd = 'B01106'
           AND sm_cd = t.qlty_cd) sm_cfnm,
       t.reg_dtime,
       '备注' remark,
       '测试人员' create_opr,
       t2.phy_code_l,
       t2.phy_name_l,
       t2.phy_code_m,
       t2.phy_name_m,
       t2.phy_code_s,
       t2.phy_name_s,
       t2.item_code_d,
       t2.item_name_d,
       t2.item_code_t,
       t2.item_name_t,
       t2.item_code_s,
       t2.item_name_s
  FROM tbb02_work_qlty t, tbb01_seq_qlty t1, COM_BASE_PHY_R_PHY t2
 WHERE t.qlty_cd = t1.qlty_cd
   and t.QLTY_CD = t2.pfmc_code
   AND t.smp_no = ?
		 ]]>
	</query>
	
	<query id="UIJ030060_QLTYLL.select"  desc="查询拉力等需要三个值的材质实绩">
		<![CDATA[
		
		SELECT decode(substr(t.qlty_val_wk,1,1),'.','0'||t.qlty_val_wk,t.qlty_val_wk) QLTY_VAL_WK,T.QLTY_SEQ
		 FROM TBB02_REWORK_QLTY T WHERE T.SMP_NO=? AND T.QLTY_CD=?
		
		 ]]>
	</query>
	<query id="UIJ030060_xg3qCHEM.select"  desc="查询三期成分实绩">
		<![CDATA[
		SELECT t.charge_no, t.chem_cd, t.chem_val, t.reg_dtime, '备注' remark,
		       '测试' create_opr 
		  FROM tbb02_work_ingr t, tbg02_charge_comm_d t1
		 WHERE t.chem_seq = substr(t1.lst_ingr_cd, 3, 4)
		       AND t.charge_no = t1.charge_no
		       AND t.proc_cd = 'JJ'
		       AND t1.charge_no = ?
		 ]]>
	</query>
	<query id="UIJ030060_xg3qtbl.SELECT"  desc="查询中间库待发送的数据">
		<![CDATA[
		select t.OLD_SAMPL_NO,
          '1' COIL_SOURCE,
          decode(t.LINE_TP,'S','1','2') COIL_SORT,
          t.ORDCUST_CD,
          (select CUST_NM from tbz00_customer where CUST_CD = t.ORDCUST_CD) CUST_NM ,
          SUBSTR(t.SLAB_NO, 1, 10) CHARGE,
          t.SMP_NO,
          t.ord_use_cd PRD_CODE,
          (SELECT sm_cfnm FROM tbz00_commcd WHERE sm_cd = t.ord_use_cd AND lg_cd = 'A01007') PRD_NAME,
          t.SPEC_ABBSYM STD_CODE,
          t.SPEC_ABBSYM STD_NAME,
          t.SPEC_STL_GRD STEEL_CODE,
          t.SPEC_STL_GRD STEEL_NAME,
          t.COIL_THK,
          t.COIL_WTH,
          t.COIL_LEN,
          t.COIL_INDIA,
          t.COIL_OUTDIA,
          t.CAL_WGT COIL_WGT,
          t.ACT_WGT,
          t.MILL_DTIME,
         t.CRK_CD1 FLAW_CODE1,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD1) FLAW_NAME1,
         t.CRK_CD2 FLAW_CODE2,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD2) FLAW_NAME2,
         t.CRK_CD3 FLAW_CODE3,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD3) FLAW_NAME3,
         t.CRK_CD4 FLAW_CODE4,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD4) FLAW_NAME4,
         t.CRK_CD5 FLAW_CODE5,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD5) FLAW_NAME5,
          t.INGR_DEC_GRD,
          decode(t.INGR_DEC_GRD, 1, '合格', '不合格') INGR_DEC_NAME ,
          t.EXTSHAPE_DEC_GRD,
          decode(t.EXTSHAPE_DEC_GRD, 1, '合格', '不合格') EXTSHAPE_DEC_NAME,
          t.SIZE_DEC_RST,
          decode(t.SIZE_DEC_RST, 1, '合格', '不合格') SIZE_DEC_NAME,
          t.MATLQLTY_DEC_GRD,
          decode(t.MATLQLTY_DEC_GRD, 1, '合格', '不合格') MATLQLTY_DEC_NAME,
          t.WGT_DEC_RST,
          decode(t.WGT_DEC_RST, 1, '合格', '不合格') WGT_DEC_NAME,
          t.TOT_DEC_GRD,
          decode(t.TOT_DEC_GRD, 1, '合格', '不合格') TOT_DEC_NAME,
          '0' STATUS,
          '备注' REMARK,
          'test' CREATE_OPR,
          t.ORD_NO,
          t.ORD_SEQ,
          T1.DDC_STL_GRD,
          T1.ORD_WGT,
          (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = T.ORDCUST_CD
           and REC_TP = '02') ORD_NM,
       	T.INSTR_COIL_THK,
       	T.INSTR_COIL_WTH,
       	T.DLIV_TP,
       	T.TRANS_CAR_NO,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01010'
           AND SM_CD = T.PACKAGE_LEVEL) packagel,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01009'
           AND SM_CD = A.DEST_PCD) DESTPCD
	     from c_tbl02_coil_comm t,TBJ01_DLIV_DIR A,TBA01_ORD_LINE T1
	    where t.OLD_SAMPL_NO = ? AND T.DLIV_DIRNO = A.DLIV_DIRNO(+) AND T.ORD_NO=T1.ORD_NO(+) AND T.ORD_SEQ=T1.ORD_SEQ(+)
		 ]]>
	</query>
	
	<query id="UIJ030060_chkloc.SELECT"  desc="查询中间表是否有数据">
		<![CDATA[
		
		select t.OLD_SAMPL_NO,
          '1' COIL_SOURCE,
          decode(t.LINE_TP,'S','1','2') COIL_SORT,
          t.ORDCUST_CD,
          (select CUST_NM from tbz00_customer where CUST_CD = t.ORDCUST_CD) CUST_NM ,
          SUBSTR(t.SLAB_NO, 1, 10) CHARGE,
          t.SMP_NO,
          t.ord_use_cd PRD_CODE,
          (SELECT sm_cfnm FROM tbz00_commcd WHERE sm_cd = t.ord_use_cd AND lg_cd = 'A01007') PRD_NAME,
          t.SPEC_ABBSYM STD_CODE,
          t.SPEC_ABBSYM STD_NAME,
          t.SPEC_STL_GRD STEEL_CODE,
          t.SPEC_STL_GRD STEEL_NAME,
          t.COIL_THK,
          t.COIL_WTH,
          t.COIL_LEN,
          t.COIL_INDIA,
          t.COIL_OUTDIA,
          t.CAL_WGT COIL_WGT,
          t.ACT_WGT,
          t.MILL_DTIME,
         t.CRK_CD1 FLAW_CODE1,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD1) FLAW_NAME1,
         t.CRK_CD2 FLAW_CODE2,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD2) FLAW_NAME2,
         t.CRK_CD3 FLAW_CODE3,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD3) FLAW_NAME3,
         t.CRK_CD4 FLAW_CODE4,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD4) FLAW_NAME4,
         t.CRK_CD5 FLAW_CODE5,
        (SELECT B.SM_CFNM
          FROM TBZ00_COMMCD B
         WHERE B.LG_CD = 'B02001'
           AND B.SM_CD = T.CRK_CD5) FLAW_NAME5,
          t.INGR_DEC_GRD,
          decode(t.INGR_DEC_GRD, 1, '合格', '不合格') INGR_DEC_NAME ,
          t.EXTSHAPE_DEC_GRD,
          decode(t.EXTSHAPE_DEC_GRD, 1, '合格', '不合格') EXTSHAPE_DEC_NAME,
          t.SIZE_DEC_RST,
          decode(t.SIZE_DEC_RST, 1, '合格', '不合格') SIZE_DEC_NAME,
          t.MATLQLTY_DEC_GRD,
          decode(t.MATLQLTY_DEC_GRD, 1, '合格', '不合格') MATLQLTY_DEC_NAME,
          t.WGT_DEC_RST,
          decode(t.WGT_DEC_RST, 1, '合格', '不合格') WGT_DEC_NAME,
          t.TOT_DEC_GRD,
          decode(t.TOT_DEC_GRD, 1, '合格', '不合格') TOT_DEC_NAME,
          '0' STATUS,
          '备注' REMARK,
          'test' CREATE_OPR,
          t.ORD_NO,
          t.ORD_SEQ,
          
          (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = T.ORDCUST_CD
           and REC_TP = '02') ORD_NM,
       	T.INSTR_COIL_THK,
       	T.INSTR_COIL_WTH,
       	T.DLIV_TP,
       	T.TRANS_CAR_NO,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01010'
           AND SM_CD = T.PACKAGE_LEVEL) packagel,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01009'
           AND SM_CD = A.DEST_PCD) DESTPCD
	     from c_tbl02_coil_comm t,TBJ01_DLIV_DIR A,if_raw_mtr_coil_m t1
	    where t.OLD_SAMPL_NO = ? AND T.DLIV_DIRNO = A.DLIV_DIRNO and t.OLD_SAMPL_NO=t1.coil_no



		 ]]>
	</query>
	<query id="UIJ030060_xg3qtbc.SELECT"  desc="查询成品库待发送的数据">
		<![CDATA[
		select t.OLD_SAMPL_NO,
          '1' COIL_SOURCE,
          decode(t.LINE_TP,'L','3','4') COIL_SORT,
          t.ORDCUST_CD,
          (select CUST_NM from tbz00_customer where CUST_CD = t.ORDCUST_CD) CUST_NM ,
          SUBSTR(t.SLAB_NO, 1, 10) CHARGE,
          t.SMP_NO,
          t.ord_use_cd PRD_CODE,
          (SELECT sm_cfnm FROM tbz00_commcd WHERE sm_cd = t.ord_use_cd AND lg_cd = 'A01007') PRD_NAME,
          t.SPEC_ABBSYM STD_CODE,
          t.SPEC_ABBSYM STD_NAME,
          t.SPEC_STL_GRD STEEL_CODE,
          t.SPEC_STL_GRD STEEL_NAME,
          t.COIL_THK,
          t.COIL_WTH,
          t.COIL_LEN,
          t.COIL_INDIA,
          t.COIL_OUTDIA,
          t.CAL_WGT COIL_WGT,
          t.ACT_WGT,
          t.MILL_DTIME,
          t.CRK_CD1 FLAW_CODE1,
          t.CRK_CD1 FLAW_NAME1,
          t.CRK_CD2 FLAW_CODE2,
          t.CRK_CD2 FLAW_NAME2,
          t.CRK_CD3 FLAW_CODE3,
          t.CRK_CD3 FLAW_NAME3,
          t.CRK_CD4 FLAW_CODE4,
          t.CRK_CD4 FLAW_NAME4,
          t.CRK_CD5 FLAW_CODE5,
          t.CRK_CD5 FLAW_NAME5,
          t.INGR_DEC_GRD,
          decode(t.INGR_DEC_GRD, 1, '合格', '不合格') INGR_DEC_NAME ,
          t.EXTSHAPE_DEC_GRD,
          decode(t.EXTSHAPE_DEC_GRD, 1, '合格', '不合格') EXTSHAPE_DEC_NAME,
          t.SIZE_DEC_RST,
          decode(t.SIZE_DEC_RST, 1, '合格', '不合格') SIZE_DEC_NAME,
          t.MATLQLTY_DEC_GRD,
          decode(t.MATLQLTY_DEC_GRD, 1, '合格', '不合格') MATLQLTY_DEC_NAME,
          t.WGT_DEC_RST,
          decode(t.WGT_DEC_RST, 1, '合格', '不合格') WGT_DEC_NAME,
          t.TOT_DEC_GRD,
          decode(t.TOT_DEC_GRD, 1, '合格', '不合格') TOT_DEC_NAME,
          '0' STATUS,
          '备注' REMARK,
          'test' CREATE_OPR,
          t.ORD_NO,
          t.ORD_SEQ,
          (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = T.ORDCUST_CD
           and REC_TP = '02') ORD_NM,
       	T.INSTR_COIL_THK,
       	T.INSTR_COIL_WTH,
       	T.DLIV_TP,
       	T.TRANS_CAR_NO,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01010'
           AND SM_CD = T.PACKAGE_LEVEL) packagel,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'A01009'
           AND SM_CD = A.DEST_PCD) DESTPCD
	     from c_tbC02_coil_comm t,TBJ01_DLIV_DIR A
	    where t.OLD_SAMPL_NO = ? AND T.DLIV_DIRNO = A.DLIV_DIRNO
		 ]]>
	</query>
	
	<query id="UIJ030060_youte.delete" desc="删除优特主数据表" fetchSize="10">
    	delete from if_raw_mtr_coil_m where coil_no=?
    </query>
    <query id="UIJ030060_youte.update" desc="更新优特主数据表" fetchSize="10">
    	update if_raw_mtr_coil_m set STATUS ='2' where coil_no=?
    </query>
	    <query id="UIJ030060_youte.insert" desc="插入待发送的数据到优特钢带" fetchSize="10">
        <![CDATA[
         INSERT INTO if_raw_mtr_coil_m
		  (coil_no, coil_source, coil_sort, supplier_code, supplier_name,
		   
		   heatno, smp_no, prd_code, prd_name, std_code,
		   
		   std_name, steel_code, steel_name, coil_thk, coil_wdt,
		   
		   coil_len, coil_inn_dia, coil_out_dia, coil_thr_wgt, coil_act_wgt,
		   
		   produce_time, flaw_code1, flaw_name1, flaw_code2, flaw_name2,
		   
		   flaw_code3, flaw_name3, flaw_code4, flaw_name4, flaw_code5,
		   
		   flaw_name5, chem_dec_rst_code, chem_dec_rst_name, surface_dec_rst_code,
		   surface_dec_rst_name,
		   
		   size_dec_rst_code, size_dec_rst_name, pfmc_dec_rst_code,
		   pfmc_dec_rst_name, wgt_dec_rst_code,
		   
		   wgt_dec_rst_name, total_dec_rst_code, total_dec_rst_name, status, remark,		   
		   create_opr, orderno, prodorderno, 
		   
		   ord_nm, instr_coil_thk, instr_coil_wth, dliv_tp, trans_car_no, packagel, destpcd,ddc_stl_grd,ord_wgt)
		   
		VALUES
		  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
		   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
		   ?, ?, ?, ?, ?, ?, ?,?,?)
        ]]>
    </query>
    
    <query id="UIJ030060_youtejilu_zhu.insert" desc="插入主数据记录" fetchSize="10">
    	insert into youtejilu (table_name, coil_no, data_source) values (?, ?, ?)
    </query>
    <query id="UIJ030060_youtejilu_caizhi.insert" desc="插入材质实绩记录" fetchSize="10">
    	insert into youtejilu (table_name, smp_no, pfmc_code, data_source) values (?, ?, ?, ?)
    </query>
    <query id="UIJ030060_youtejilu_chengfen.insert" desc="插入成分实绩记录" fetchSize="10">
    	insert into youtejilu (table_name, heatno, chem_code, data_source) values (?, ?, ?, ?)
    </query>
    <query id="UIJ030060_youteCHEM.insert" desc="插入待发送的成分数据到优特钢带" fetchSize="10">
        <![CDATA[
        INSERT INTO if_raw_mtr_coil_chem
		  (heatno, chem_code, chem_value, inspect_time, remark, create_opr)
		VALUES
		  (?, ?, ?, ?, ?, ?)
        ]]>
    </query> 
    
    <query id="UIJ030060_youteQLTY11111.insert" desc="插入待发送的材质数据到优特钢带" fetchSize="10">
        <![CDATA[
         INSERT INTO if_raw_mtr_coil_pfmc
		  (smp_no, pfmc_code, pfmc_name, pfmc_value, pfmc_unit_code, pfmc_unit_name,
		   pfmc_type_code, pfmc_type_name, inspect_time, remark, create_opr)
		VALUES
		  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ]]>
    </query>
    
      <query id="UIJ030060_youteQLTY.insert" desc="插入待发送的材质数据到优特钢带" fetchSize="10">
        <![CDATA[
       insert into QCM_JHY_PHY_RESULT T2
       (T2.SPECIMEN_NO,
   T2.SEQ,
   T2.TEST_QTY,
   T2.VAL1,
   T2.VAL2,
   T2.VAL3,
   T2.avg_val,
   t2.phy_code_l,
   t2.phy_name_l,
   t2.phy_code_m,
   t2.phy_name_m,
   t2.phy_code_s,
   t2.phy_name_s,
   t2.item_code_d,
   t2.item_name_d,
   t2.item_code_t,
   t2.item_name_t,
   t2.item_code_s,
   t2.item_name_s,
   t2.pfmc_id,
   t2.pfmc_name
  )
VALUES
  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ]]>
    </query>
</queryMap>