XinYu_RZMES/.svn/pristine/f6/f6a48c0d35dd98fd35aef5cfeefd54e8f0724fa4.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="充当余材处理">
              
	<query id="NIE031045.OrdPrc.Select" desc="订单余重" fetchSize="10">
        <![CDATA[
			SELECT
				100 REM_WGT		-- 订单伪余重
			FROM DUAL

/*
			SELECT
				ORD_NO,				-- 用于 NIE012070 接口参数
				ORD_SEQ
			FROM TBE02_ORD_PRC
			WHERE ORD_NO = ?
			  AND ORD_SEQ = ?
*/
		]]>
    </query> 
	<query id="NIE031045.SlabComm.Select" desc="充当物料及其重量查询" fetchSize="10">
        <![CDATA[
			SELECT
				SLAB_NO MAT_NO,		-- 板坯号
				(SLAB_WGT * PROC_RATE / 100) WGT
			FROM TBG02_SLAB_COMM A, (
				SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') B
			WHERE SLAB_NO IN (
				SELECT COLUMN_VALUE FROM TABLE(XB_PACKAGE.GF_TABSTR(?)))
		]]>
    </query> 
    <query id="NIE031045.CoilComm.Select" desc="充当物料查询" fetchSize="10">
        <![CDATA[
			SELECT
				COIL_NO MAT_NO,		-- 钢卷号
				ACT_WGT WGT
			FROM TBH02_COIL_COMM
			WHERE COIL_NO IN (
				SELECT COLUMN_VALUE FROM TABLE(XB_PACKAGE.GF_TABSTR(?)))
        ]]>
    </query> 
	<query id="NIE031045.OrdSts.Slab" desc="NIE012070 接口参数查询" fetchSize="10">
		<![CDATA[
			SELECT
				'' BEF_ORD_NO,				-- 前合同号
				'' BEF_ORD_SEQ,				-- 前订单号
				-- 合同号
				-- 订单号
				CUR_PROG_CD BEF_PROG_CD,	-- 前进程代码
				'RBA' PROG_CD,				-- 进程代码
				'TM' MAT_TYPE,				-- 物料类型
				SLAB_NO MAT_NO,				-- 物料号
				SLAB_WGT WGT,				-- 重量
				'UIE031045' PGMID
			FROM TBG02_SLAB_COMM
			WHERE SLAB_NO = ?
		]]>
	</query>
	<query id="NIE031045.OrdSts.Coil" desc="NIE012070 接口参数查询" fetchSize="10">
		<![CDATA[
			SELECT
				'' BEF_ORD_NO,				-- 前合同号
				'' BEF_ORD_SEQ,				-- 前订单号
				-- 合同号
				-- 订单号
				CUR_PROG_CD BEF_PROG_CD,	-- 前进程代码
				'SFA' PROG_CD,				-- 进程代码
				'AR' MAT_TYPE,				-- 物料类型
				COIL_NO MAT_NO,				-- 物料号
				ACT_WGT WGT,				-- 重量
				'UIE031045' PGMID
			FROM TBH02_COIL_COMM
			WHERE COIL_NO = ?
		]]>
	</query>

	<query id="NIE031045.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_NO,
				MAT_TYPE, UPD_BEF_ORD_NO, UPD_BEF_ORD_SEQ, UPD_BEF_CUST_CD,
				REP_TP
			)
			-- (1) 充当代码, 充当方法, 录入者, 变更程序ID, 录入时间,
			-- (2) 变更前合同号, 变更前订单号, 变更前客户代码, 物料类型,
			-- (3) 物料号, 变更后合同号, 变更后订单号, 变更后客户代码
			SELECT
				'A', 'M', ?, 'UIE031045', TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
				ORD_NO, ORD_SEQ, CUST_CD, ?, ?, '', '', '',?
			FROM TBE02_ORD_PRC
			WHERE ORD_NO = ?
			  AND ORD_SEQ = ?
		]]>
	</query>

	<query id="NIE031045.SlabComm.Update" desc="">
		<![CDATA[
	        UPDATE TBG02_SLAB_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,
-----------------------------------------------------------------------------------------
                BEF_STL_GRD       = STL_GRD,--充当的时候记录牌号历史 MODIFY BY SJW 090813
-----------------------------------------------------------------------------------------
                CUR_PROG_CD = 'RBA',
                CUR_PROG_CD_PGM = 'UIE031045',
                CUR_PROG_CD_DTIME = TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
                ORD_FL = '1',
                NONORD_CAUSE_CD = '',
                NONORD_OCR_DTIME = '',
                SLAB_INGR_DEC_GRD = '1',
                SLAB_SIZE_DEC_GRD = '1',
                EXTSHAPE_GRD = '1',
                MOD_ID = ?,
                MOD_DTIME = TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
----------------------------------------------------------------------------------------------
			    --充当将当前订单写入原始订单  add  by sjw /090812
                ORG_ORD_NO       = DECODE(ORD_FL,'1',ORD_NO,''),
                ORG_ORD_SEQ      = DECODE(ORD_FL,'1',ORD_SEQ,''),
----------------------------------------------------------------------------------------------			
                (ORD_NO, ORD_SEQ, CUST_CD, ORDCUST_CD, ORD_USE_CD, PRODNM_CD, SF_METHOD,
                    SPEC_ABBSYM, DEVLMT_DTIME, INSTR_COIL_THK, INSTR_COIL_WTH, INSTR_COIL_INDIA,
                    INSTR_COIL_WGT, INSTR_COIL_LEN, INSTR_COIL_OUTDIA,STL_GRD) =
                (SELECT ORD_NO, ORD_SEQ, CUST_CD, ORDCUST_CD, ORD_USEAGE_CD, PRODNM_CD, SCARPING_PROC_YN,
                    SPEC_ABBSYM, DEL_TO_DATE, ORD_THK, ORD_WTH, ORD_INDIA,
                    (A.SLAB_WGT * C.PROC_RATE / 100),
                    (A.SLAB_WGT * C.PROC_RATE / 100) / (B.ORD_THK * B.ORD_WTH * 7.8) * 1000000,
                    SQRT(4 * (A.SLAB_WGT * C.PROC_RATE / 100) / (B.ORD_WTH / 1000000 * 7.8 * 3.14 ) + (B.ORD_INDIA * B.ORD_INDIA))
                    , STL_GRD
                    --DECODE(GF_NIB028720(A.SLAB_NO, ORD_NO, ORD_SEQ), 'YY', '1', '2')
                 FROM TBE02_ORD_PRC B, (
                     SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
                 WHERE ORD_NO = ?
                   AND ORD_SEQ = ?)
            WHERE SLAB_NO = ?
		]]>
	</query>
	<query id="NIE031045.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,
---------------------------------------------------------------------------------------------
                BEF_SPEC_STL_GRD= SPEC_STL_GRD, --充当的时候记录前钢种 MODIFY BY SJW 090813
---------------------------------------------------------------------------------------------
				--CUR_PROG_CD = 'SFA',--充当时分产线修改物料进程状态
				CUR_PROG_CD = (CASE WHEN (SELECT D.PROD_LINE FROM TBE02_ORD_PRC D WHERE D.ORD_NO = ?
                                                             AND   D.ORD_SEQ = ?) = 'C'
                            THEN 'SGB'                                 
                                    WHEN (SELECT D.PROD_LINE FROM TBE02_ORD_PRC D WHERE D.ORD_NO = ?
                                                             AND   D.ORD_SEQ = ?) = 'H'
                            THEN  'SFA' 
                       END),
---------------------------------------------------------------------------------------------				
				CUR_PROG_CD_PGM = 'UIE031045',
				CUR_PROG_CD_DTIME = TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'),
				ORD_FL = '1',
			    INGR_DEC_GRD = '1',
			    MATLQLTY_DEC_GRD = '1',
				SIZE_DEC_RST = '1',
				EXTSHAPE_DEC_GRD = '1',
				WGT_DEC_RST = '1',
				TOT_DEC_GRD = '1',
				NONORD_CAUSE_CD = '',
				NONORD_OCR_DTIME = '',
				(ORD_NO, ORD_SEQ, CUST_CD, ORDCUST_CD, ORD_USE_CD, SPEC_ABBSYM, PRODNM_CD, SPM_YN,
				 	DEVLMT_DTIME, DEST_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,SPEC_STL_GRD
-------------------------------------------------------------------------------------------------------------------------------------			
			 --更改厂内牌号为订单厂内牌号 MODIFY BY SJW 090813
			   ,STL_GRD
----------------------------------------------------------------------------------------------------------------------         
		       --增加冷轧标准牌号，标准号 MODIFY BY WXY 101018
		         ,C_SPEC_ABBSYM 
		         ,C_SPEC_STL_GRD
		         ) =
------------------------------------------------------------------------------------------------------------------------------------			
				(SELECT A.ORD_NO, A.ORD_SEQ, A.CUST_CD, A.ORDCUST_CD, A.ORD_USEAGE_CD, A.SPEC_ABBSYM, A.PRODNM_CD, A.SPM_PROC_YN,
				 	A.DEL_TO_DATE, A.DEST_CD,
				 --	DECODE(GF_NIB028720(A.SLAB_NO, ORD_NO, ORD_SEQ), 'YY', '1', '2'),
				 --	DECODE(GF_NIB028750(A.COIL_NO, ORD_NO, ORD_SEQ), 'YY', '1', '2'),
				 	A.ORD_THK, A.ORD_WTH, A.ORD_LEN, A.ORD_INDIA, A.ORD_OUTDIA, A.ORD_UNIT_WGT,A.H_SPEC_STL_GRD,A.STL_GRD
----------------------------------------------------------------------------------------------------				 	
				 	,A.C_SPEC_ABBSYM,A.C_SPEC_STL_GRD
---------------------------------------------------------------------------------------------				 	
				 FROM  TBE02_ORD_PRC  A
					  ,TBA01_ORD_LINE B
				 WHERE A.ORD_NO = ?
				   AND A.ORD_SEQ = ?
			       AND A.ORD_NO  =  B.ORD_NO(+)
				   AND A.ORD_SEQ =  B.ORD_SEQ(+))
			WHERE COIL_NO = ?
		]]>
	</query>

	<query id="NIE031045.SlabCommD.Delete" desc="">
		<![CDATA[
			DELETE TBG02_SLAB_COMM_D
			WHERE SLAB_NO = (SELECT SUB_SLAB_NO FROM TBG02_SLAB_COMM WHERE SLAB_NO = ?)
		]]>
	</query>
	<query id="NIE031045.CoilCommD.Delete" desc="">
		<![CDATA[
			DELETE TBH02_COIL_COMM_D
			WHERE COIL_NO = ?
		]]>
	</query>

	<query id="NIE031045.SlabCommD.Insert" desc="" fetchSize="10">
		<![CDATA[
			INSERT INTO TBG02_SLAB_COMM_D
			( SLAB_NO, SLAB_CUT_SEQ, SLAB_THK, SLAB_WTH, SLAB_LEN,
			  ORD_THK, ORD_WTH, ORD_LEN, ORD_WGT, COIL_INDIA, 
			  COIL_OUTDIA, ORD_NO, ORD_SEQ, ORD_FL
			)
			SELECT SUB_SLAB_NO, '01', SLAB_THK, SLAB_WTH, SLAB_LEN,
				INSTR_COIL_THK, INSTR_COIL_WTH, INSTR_COIL_LEN, INSTR_COIL_WGT, INSTR_COIL_INDIA,
				INSTR_COIL_OUTDIA, ORD_NO, ORD_SEQ, '1'
			FROM TBG02_SLAB_COMM
			WHERE SLAB_NO = ?
		]]>
	</query>
	<query id="NIE031045.SlabCommD_detail.delete" desc="" fetchSize="10">
		<![CDATA[
			delete
			FROM TBG02_SLAB_COMM_d
			WHERE SLAB_NO = :1
		]]>
	</query>
	<query id="NIE031045.CoilCommD_detail.delete" desc="" fetchSize="10">
		<![CDATA[
			delete
			FROM TBH02_COIL_COMM_D
			WHERE COIL_NO = :1
		]]>
	</query>
	<query id="NIE031045.SlabCommD_detail.Insert" desc="" fetchSize="10">
		<![CDATA[
			INSERT INTO TBG02_SLAB_COMM_D
			( SLAB_NO, SLAB_CUT_SEQ, SLAB_THK, SLAB_WTH, SLAB_LEN,
			  ORD_THK, ORD_WTH, ORD_LEN, ORD_WGT, COIL_INDIA, 
			  COIL_OUTDIA, ORD_NO, ORD_SEQ, ORD_FL
			)
			SELECT SUB_SLAB_NO, :1, SLAB_THK, SLAB_WTH, SLAB_LEN,
				INSTR_COIL_THK, INSTR_COIL_WTH, INSTR_COIL_LEN, INSTR_COIL_WGT/:2, INSTR_COIL_INDIA,
				INSTR_COIL_OUTDIA, ORD_NO, ORD_SEQ, '1'
			FROM TBG02_SLAB_COMM
			WHERE SLAB_NO = :3
		]]>
	</query>
	<query id="NIE031045.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
-----------------------------------------------------------------------------------------------------
        --MODIFY BY WXY 增加冷轧订单信息
        ,C_ORD_THK
        ,C_ORD_WTH
        ,C_ORD_LEN
        ,C_ORD_WGT
        ,C_ORD_WGT_MIN
        ,C_ORD_WGT_MAX
        ,C_ORD_INDIA
        ,C_ORD_OUTDIA
-----------------------------------------------------------------------------------------------------        
      )
      SELECT A.COIL_NO, '01', B.ORD_THK, B.ORD_WTH, B.ORD_LEN,
        --A.ACT_WGT
          ------------------------------------------------------------------------------------------
             --MODIFY BY SJW:090820
                   --DESC:TBG02_SLAB_COMM_D 规格是订单的规格信息而不是物料的实际信息
            A.INSTR_COIL_WGT
          ------------------------------------------------------------------------------------------
          , DECODE(B.PROD_LINE,'C','',B.ORD_UNIT_WGT_MIN)
          ,DECODE(B.PROD_LINE,'C','',B.ORD_UNIT_WGT_MAX)
          , B.ORD_INDIA
          , B.ORD_OUTDIA,
        B.ORD_NO, B.ORD_SEQ, '1'
-----------------------------------------------------------------------------------------------------
        --MODIFY BY WXY 增加冷轧订单信息
        ,B.C_ORD_THK
        ,B.C_ORD_WTH
        ,B.C_ORD_LEN
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT,'')
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT_MIN,'')
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT_MAX,'')
        ,B.C_ORD_INDIA
        ,B.C_ORD_OUTDIA
-----------------------------------------------------------------------------------------------------  
      FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B
      WHERE A.ORD_NO = B.ORD_NO
        AND A.ORD_SEQ = B.ORD_SEQ
        AND A.COIL_NO = ?
		]]>
	</query>
	
	<query id="NIE031045.CoilCommD_Detail.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
-----------------------------------------------------------------------------------------------------
        --MODIFY BY WXY 增加冷轧订单信息
        ,C_ORD_THK
        ,C_ORD_WTH
        ,C_ORD_LEN
        ,C_ORD_WGT
        ,C_ORD_WGT_MIN
        ,C_ORD_WGT_MAX
        ,C_ORD_INDIA
        ,C_ORD_OUTDIA
-----------------------------------------------------------------------------------------------------        
      )
      SELECT A.COIL_NO, :1, B.ORD_THK, B.ORD_WTH, B.ORD_LEN,
        --A.ACT_WGT
          ------------------------------------------------------------------------------------------
             --MODIFY BY SJW:090820
                   --DESC:TBG02_SLAB_COMM_D 规格是订单的规格信息而不是物料的实际信息
            A.ACT_WGT/:2
          ------------------------------------------------------------------------------------------
          , DECODE(B.PROD_LINE,'C','',B.ORD_UNIT_WGT_MIN)
          ,DECODE(B.PROD_LINE,'C','',B.ORD_UNIT_WGT_MAX)
          , B.ORD_INDIA
          , B.ORD_OUTDIA,
        B.ORD_NO, B.ORD_SEQ, '1'
-----------------------------------------------------------------------------------------------------
        --MODIFY BY WXY 增加冷轧订单信息
        ,B.C_ORD_THK
        ,B.C_ORD_WTH
        ,B.C_ORD_LEN
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT,'')
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT_MIN,'')
        ,DECODE(B.PROD_LINE,'C',B.ORD_UNIT_WGT_MAX,'')
        ,B.C_ORD_INDIA
        ,B.C_ORD_OUTDIA
-----------------------------------------------------------------------------------------------------  
      FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B
      WHERE A.ORD_NO = B.ORD_NO
        AND A.ORD_SEQ = B.ORD_SEQ
        AND A.COIL_NO = :3
		]]>
	</query>

</queryMap>