XinYu_LZMES/.svn/pristine/c6/c67f462f59efc28383ebdb440ba4ec3adf1c0b19.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="连退上料">
	<query id="UIM020030_01.SELECT"  desc="查询连退计划">
		<![CDATA[
			SELECT M.CAL_NO || '-' || M.CAL_COIL_SEQ CAL_SEQ, --编制号 
               L.COIL_NO C_COIL_NO, --热卷号 
               M.L_COIL_NO, --冷卷号 
               L.CUR_LOAD_LOC YARD_ADDR, --垛位
               DECODE(L.TRNF_SHIFT,  '0', '常白班', '1','早班', '2', '中班', '3', '晚班') TRNF_SHIFT, --出库班次
               to_char(L.COIL_THK, 'FM990.099') || ' * ' || L.COIL_WTH T_W,
               to_char(E.C_ORD_THK, 'FM990.099') || ' * ' || E.C_ORD_WTH IT_IW,
               L.COIL_LEN,
               L.ACT_WGT COIL_WGT,
               M.SPEC_STL_GRD,
               DECODE(M.STATUS_CD, 'A', '计划中',  'B', '上鞍座', 'C','焊接', 'D','生产完成','F','预备吊销钢卷','E','真正吊销钢卷') STATUS_CD,
               L.ORD_NO,
               L.ORD_SEQ,
               L.ORD_FL
          FROM C_TBL02_COIL_COMM L, L_TBF03_SPEC_MILL M, TBE02_ORD_PRC E
         WHERE L.COIL_NO = M.C_COIL_NO
           AND L.ORD_NO = E.ORD_NO
           AND L.ORD_SEQ = E.ORD_SEQ
           AND (M.STATUS_CD IN ('A','B','F') OR (M.STATUS_CD IN ('C','D') AND L.TRNF_SHIFT IS NULL AND L.CUR_PROG_CD = 'CCC'))
           AND M.CAL_NO = (SELECT MAX(CAL_NO) FROM L_TBF03_SPEC_MILL WHERE C_COIL_NO = L.COIL_NO AND CAL_NO LIKE 'C%')
         ORDER BY M.CAL_NO, M.CAL_COIL_SEQ
		]]>
	</query>
	<query id="UIM020030_05.SELECT"  desc="查询连退计划">
		<![CDATA[
			SELECT M.CAL_NO || '-' || M.CAL_COIL_SEQ CAL_SEQ, --编制号 
               L.COIL_NO C_COIL_NO, --热卷号 
               M.L_COIL_NO, --冷卷号 
               L.CUR_LOAD_LOC YARD_ADDR, --垛位
               DECODE(L.TRNF_SHIFT,  '0', '常白班', '1','早班', '2', '中班', '3', '晚班') TRNF_SHIFT, --出库班次
               to_char(L.COIL_THK, 'FM990.099') || ' * ' || L.COIL_WTH T_W,
               to_char(E.C_ORD_THK, 'FM990.099') || ' * ' || E.C_ORD_WTH IT_IW,
               L.COIL_LEN,
               L.ACT_WGT COIL_WGT,
               M.SPEC_STL_GRD,
               DECODE(M.STATUS_CD, 'A', '计划中',  'B', '上鞍座', 'C','焊接', 'D','生产完成','F','预备吊销钢卷','E','真正吊销钢卷') STATUS_CD,
               L.ORD_NO,
               L.ORD_SEQ,
               L.ORD_FL
          FROM C_TBL02_COIL_COMM L, L_TBF03_SPEC_MILL M, TBE02_ORD_PRC E
         WHERE L.COIL_NO = M.C_COIL_NO
           AND L.ORD_NO = E.ORD_NO
           AND L.ORD_SEQ = E.ORD_SEQ
           AND (M.STATUS_CD IN ('A','B','F') OR (M.STATUS_CD IN ('C','D') AND L.TRNF_SHIFT IS NULL AND L.CUR_PROG_CD = 'CCC'))
           AND M.CAL_NO = (SELECT MAX(CAL_NO) FROM L_TBF03_SPEC_MILL WHERE C_COIL_NO = L.COIL_NO AND CAL_NO LIKE 'A%')
           and m.CAL_NO like 'A%'
         ORDER BY M.CAL_NO, M.CAL_COIL_SEQ
		]]>
	</query>
	<query id="UIM020030_02.SELECT"  desc="查询未加班次班组的钢卷">
		<![CDATA[		
			select M.CAL_NO,
	             T.COIL_NO C_COIL_NO,
	             M.L_COIL_NO,
	             T.TRNF_SHIFT,
	             T.TRNF_GROUP,
	             T.TRNF_REG
	        from c_tbl02_coil_comm t, l_tbf03_spec_mill m
	       where t.COIL_NO = m.C_COIL_NO
	         and m.STATUS_CD in ('B', 'C', 'D')
	         AND T.TRNF_SHIFT IS NULL
	         AND T.CUR_PROG_CD = 'CCC'
	         AND M.CAL_NO = (SELECT MAX(CAL_NO) FROM L_TBF03_SPEC_MILL WHERE C_COIL_NO = t.COIL_NO)
	         ORDER BY CAL_NO			
		]]>
	</query>
	<query id="UIM020030_06.SELECT"  desc="查询未加班次班组的钢卷">
		<![CDATA[		
			select M.CAL_NO,
	             T.COIL_NO C_COIL_NO,
	             M.L_COIL_NO,
	             T.TRNF_SHIFT,
	             T.TRNF_GROUP,
	             T.TRNF_REG
	        from c_tbl02_coil_comm t, l_tbf03_spec_mill m
	       where t.COIL_NO = m.C_COIL_NO
	         and m.STATUS_CD in ('B', 'C', 'D')
	         AND T.TRNF_SHIFT IS NULL
	         AND T.CUR_PROG_CD = 'CCC'
	         AND M.CAL_NO = (SELECT MAX(CAL_NO) FROM L_TBF03_SPEC_MILL WHERE C_COIL_NO = t.COIL_NO
	         				AND CAL_NO LIKE 'A%'
	         )
	         ORDER BY CAL_NO			
		]]>
	</query>
	<query id="UIM020030_03.SELECT"  desc="查询成品表中钢卷记录是否存在">
		<![CDATA[		
			SELECT 1 FROM C_TBC02_COIL_COMM T WHERE T.OLD_SAMPL_NO = ?		
		]]>
	</query>
	<query id="UIM020030_04.SELECT"  desc="根据计划号和原料卷号查询连退卷号">
		<![CDATA[		
			SELECT F.L_COIL_NO FROM L_TBF03_SPEC_MILL F 
	         WHERE F.CAL_NO = ? 
	           AND F.C_COIL_NO = ?		
		]]>
	</query>
	<query id="UIM020030_01.INSERT"  desc="上料后插入钢卷成品数据">
		<![CDATA[		
			INSERT INTO C_TBC02_COIL_COMM C
			  (COIL_NO,
			   SLAB_NO,
			   OLD_SAMPL_NO,
			   COIL_STAT,
			   INSTR_COIL_THK,
			   INSTR_COIL_WTH,
			   INSTR_COIL_LEN,
			   INSTR_COIL_INDIA,
			   INSTR_COIL_OUTDIA,
			   INSTR_COIL_WGT,
			   DEVLMT_DTIME,
			   DEST_CD,
			   ORDCUST_CD,
			   CUST_CD,
			   ORD_FL,
			   ORD_NO,
			   ORD_SEQ,
			   PRODNM_CD,
			   SPEC_ABBSYM,
			   SPEC_STL_GRD,
			   ORD_USE_CD,
			   STL_GRD,
			   SMP_NO,
			   SMP_CUT_LOC,
			   SMP_DIR_PIC_YN,
			   SMP_PICK_YN,
			   MATLQLTY_DEC_GRD,
			   CAL_TYPE,
			   RCAL_CNT,
			   LINE_TP,
			   ORI_OLD_SAMPL_NO,
			   BEF_STL_GRD)
			  SELECT P.L_COIL_ID COIL_NO,
			         L.SLAB_NO,
			         C_PKG_UIN.FUN_GET_CAL_NO(P.L_COIL_NO) OLD_SAMPL_NO,
			         '1' COIL_STAT,
			         O.C_ORD_THK INSTR_COIL_THK,
			         O.C_ORD_WTH INSTR_COIL_WTH,
			         O.C_ORD_LEN INSTR_COIL_LEN,
			         O.C_ORD_INDIA INSTR_COIL_INDIA,
			         O.C_ORD_OUTDIA INSTR_COIL_OUTDIA,
			         O.ORD_WGT INSTR_COIL_WGT,
			         O.DEL_FR_DATE DEVLMT_DTIME,
			         O.DEST_CD,
			         O.ORDCUST_CD,
			         O.CUST_CD,
			         '1' ORD_FL,
			         L.ORD_NO,
			         L.ORD_SEQ,
			         O.C_PRODNM_CD PRODNM_CD,
			         O.C_SPEC_ABBSYM SPEC_ABBSYM,
			         L.SPEC_STL_GRD,
			         O.ORD_USEAGE_CD ORD_USE_CD,
			         L.STL_GRD,
			         P.SMP_NO,
			         P.SMP_CUT_LOC,
			         P.SMP_DIR_PIC_YN,
			         P.SMP_PICK_YN,
			         P.MATLQLTY_DEC_GRD,
			         '1',
			         P.CAL_ID,
			         'L',
			         L.OLD_SAMPL_NO,
			         L.SPEC_STL_GRD
			    FROM C_TBL02_COIL_COMM L, L_TBF03_SPEC_MILL P, TBE02_ORD_PRC O
			   WHERE L.COIL_NO = P.C_COIL_NO
			     AND P.ORD_NO = O.ORD_NO
			     AND P.ORD_SEQ = O.ORD_SEQ
			     AND P.STATUS_CD = 'B'
			     AND L.COIL_NO = ?	
			     --P.CAL_TYPE	
		]]>
	</query>
	<query id="UIM020030_02.INSERT"  desc="上料后插入钢卷成品_D数据">
		<![CDATA[		
			INSERT INTO C_TBC02_COIL_COMM_D
			  (L_COIL_NO,
			   CUT_SEQ,
			   ORD_THK,
			   ORD_WTH,
			   ORD_LEN,
			   ORD_WGT,
			   ORD_WGT_MIN,
			   ORD_WGT_MAX,
			   ORD_INDIA,
			   ORD_OUTDIA,
			   ORD_NO,
			   ORD_SEQ,
			   ORD_FL,
			   MERGE_FL)
			  SELECT C_PKG_UIN.FUN_GET_CAL_NO(F.L_COIL_NO) OLD_SAMPL_NO,
			         D.COIL_CUT_SEQ,
			         D.ORD_THK,
			         D.ORD_WTH,
			         D.ORD_LEN,
			         D.ORD_WGT,
			         D.ORD_WGT_MIN,
			         D.ORD_WGT_MAX,
			         D.ORD_INDIA,
			         D.ORD_OUTDIA,
			         D.ORD_NO,
			         D.ORD_SEQ,
			         D.ORD_FL,
			         D.MERGE_FL
			    FROM L_TBF03_SPEC_MILL_D D, L_TBF03_SPEC_MILL F
			   WHERE D.CAL_NO = F.CAL_NO
			     AND D.C_COIL_NO = F.C_COIL_NO
			     AND F.STATUS_CD = 'B'
			     AND D.C_COIL_NO = ?	
		]]>
	</query>
	<query id="UIM020030_01.DELETE"  desc="删除钢卷成品_D数据">
		<![CDATA[
			DELETE FROM C_TBC02_COIL_COMM_D T WHERE T.L_COIL_NO =  (SELECT t1.ORI_OLD_SAMPL_NO FROM c_tbl02_coil_comm t1
           WHERE t1.COIL_NO = ?)
		]]>
	</query>
	<query id="UIM020030_02.DELETE"  desc="删除成品表数据">
		<![CDATA[
			DELETE FROM C_TBC02_COIL_COMM T 
			 WHERE T.OLD_SAMPL_NO = ?
			 AND T.COIL_STAT = '1'
		]]>
	</query>
	<query id="UIM020030_03.DELETE"  desc="删除成品表数据">
		<![CDATA[
			DELETE FROM C_TBC02_COIL_COMM T 
			 WHERE T.OLD_SAMPL_NO = (SELECT t1.ORI_OLD_SAMPL_NO FROM c_tbl02_coil_comm t1
           WHERE t1.COIL_NO like ?)
		]]>
	</query>
	<query id="UIM020030_01.CALL"  desc="删除成品表数据">
		<![CDATA[
			{CALL ZL_LIANT_DCS01.COILNO_LOT_DELETE(?,?)}
		]]>
	</query>
</queryMap>