XinYu_RZMES/.svn/pristine/73/73df4e25c612b0ee542ccd7574a6f38fbca0065b.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="钢卷余材充当结果录入">

 	<!--
 		充当订单及其余量查询：同 NIE031041.RepOrd.Select
 	-->

	<query id="NIE031042.RepCoil.Select" desc="充当物料及其重量查询" fetchSize="10">
		<![CDATA[
			SELECT
				COIL_NO MAT_NO		-- 钢卷号
			FROM TBE04_REP_COIL
			WHERE ORD_NO = ?		-- 合同号
			  AND ORD_SEQ = ?		-- 订单号
			GROUP BY COIL_NO
		]]>
	</query> 
	<query id="NIE031042.OrdSts.Select" desc="NIE012070 接口参数查询" fetchSize="10">
		<![CDATA[
			SELECT
				'' BEF_ORD_NO,			-- 前合同号
				'' BEF_ORD_SEQ,			-- 前订单号
				-- 合同号
				-- 订单号
				PROG_CD BEF_PROG_CD,	-- 前进程代码
				'SFA' PROG_CD,			-- 进程代码
				'AR' MAT_TYPE,			-- 物料类型
				COIL_NO MAT_NO,			-- 物料号
				COIL_WGT WGT,			-- 重量
				'NIE031042' PGMID
			FROM TBE04_REP_COIL
			WHERE COIL_NO = ?
		]]>
	</query>

	<query id="NIE031042.RepHis.Insert" desc="插入充当历史查询表" fetchSize="10">
		<![CDATA[
			INSERT INTO TBE04_REP_HIS
			(
				REP_TYP, REP_KND, REG_ID, MOD_PGM_ID, REG_DTIME,
				UPD_AFT_ORD_NO, UPD_AFT_ORD_SEQ, UPD_AFT_CUST_CD, MAT_TYPE,
				MAT_NO, UPD_BEF_ORD_NO, UPD_BEF_ORD_SEQ, UPD_BEF_CUST_CD
			)
			-- (1) 充当代码, 充当方法, 录入者, 变更程序ID, 录入时间,
			-- (2) 变更前合同号, 变更前订单号, 变更前客户代码, 物料类型,
			-- (3) 物料号, 变更后合同号, 变更后订单号, 变更后客户代码
			SELECT
				'A', 'S', ?, 'NIE031042', TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
				ORD_NO, ORD_SEQ, CUST_CD, 'AR', ?, '', '', ''
			FROM TBE04_REP_ORD
			WHERE ORD_NO = ?
			  AND ORD_SEQ = ?
		]]>
	</query>

	<query id="NIE031042.CoilComm.Update" desc="">
		<![CDATA[
			UPDATE TBH02_COIL_COMM A SET
				BEF_PROG_CD = CUR_PROG_CD,
				BEF_PROG_CD_PGM = CUR_PROG_CD_PGM,
				BEF_PROG_CD_DTIME = CUR_PROG_CD_DTIME,
				CUR_PROG_CD = 'SFA',
				CUR_PROG_CD_PGM = 'NIE031042',
				CUR_PROG_CD_DTIME = TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
				ORD_FL = '1',
				NONORD_CAUSE_CD = '',
				NONORD_OCR_DTIME = '',
			    TOT_DEC_GRD = '1',
				(ORD_NO, ORD_SEQ, CUST_CD, ORD_USE_CD, SPEC_ABBSYM, DEVLMT_DTIME, PRODNM_CD,
				 INGR_DEC_GRD, MATLQLTY_DEC_GRD,
				 INSTR_COIL_THK, INSTR_COIL_WTH, INSTR_COIL_LEN, INSTR_COIL_INDIA, INSTR_COIL_OUTDIA, INSTR_COIL_WGT) =
				(SELECT B.ORD_NO, B.ORD_SEQ, C.CUST_CD, C.ORD_USEAGE_CD, C.SPEC_ABBSYM, C.DEL_TO_DATE, C.PRODNM_CD,
				 DECODE(GF_NIB028720(A.SLAB_NO, B.ORD_NO, B.ORD_SEQ), 'YY', '1', '2'),
				 DECODE(GF_NIB028750(B.COIL_NO, B.ORD_NO, B.ORD_SEQ), 'YY', '1', '2'),
				 	ORD_THK, ORD_WTH, ORD_LEN, ORD_INDIA, ORD_OUTDIA, ORD_UNIT_WGT
				 FROM TBE04_REP_COIL B, TBE04_REP_ORD C
				 WHERE B.COIL_NO = A.COIL_NO
				   AND B.ORD_NO = C.ORD_NO
				   AND B.ORD_SEQ = C.ORD_SEQ)
			WHERE A.COIL_NO = ?
		]]>
	</query>

	<query id="NIE031042.CoilCommD.Delete" desc="">
		<![CDATA[
			DELETE TBH02_COIL_COMM_D
			WHERE COIL_NO = ?
		]]>
	</query>
	<query id="NIE031042.CoilCommD.Insert" desc="" fetchSize="10">
		<![CDATA[
			INSERT INTO TBH02_COIL_COMM_D
			( 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
			)
			SELECT A.COIL_NO, A.COIL_CUT_SEQ, B.ORD_THK, B.ORD_WTH, B.ORD_LEN,
				B.ORD_WGT, B.ORD_UNIT_WGT_MIN, B.ORD_UNIT_WGT_MAX, B.ORD_INDIA, B.ORD_OUTDIA,
				B.ORD_NO, B.ORD_SEQ, '1'
			FROM TBE04_REP_COIL_D A, TBE02_ORD_PRC B
			WHERE A.ORD_NO = B.ORD_NO
			  AND A.ORD_SEQ = B.ORD_SEQ
			  AND A.COIL_NO = ?
		]]>
	</query>

</queryMap>