XinYu_LZMES/.svn/pristine/6f/6f587bacc55aba2260a2ea7014acaa5e84f35c47.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发运计划查询">
	<query id="UIJ030050_01.SELECT"  desc="根据发运指示号查询可回退的发运明细">
		<![CDATA[
		SELECT A.OLD_SAMPL_NO
		 ,A.DLIV_DIRNO
		 ,A.SHIP_DIRNO
		 ,A.SHIP_INVNO
		 ,A.CUR_PROG_CD
		 ,A.COIL_STAT
		 ,A.SHIP_PROG_CD
		 ,to_char(to_date(A.TRNF_DTIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') TRNF_DTIME
		 ,DECODE(C.DLIV_TP,'C','汽运','T','火运') DLIV_TP 
		 ,A.TRANS_CAR_NO
		 ,(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.TRNF_REG) TRNF_REG
		 ,A.ORD_NO
		 ,A.ORD_SEQ
		 ,DECODE(A.TRNF_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') TRNF_SHIFT
		 ,DECODE(A.TRNF_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') TRNF_GROUP
		 ,to_char(to_date(A.TRNF_USE_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') TRNF_USE_TIME
		 ,(select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO) and REC_TP='01') CUST_NM 
		 ,(select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_NM
		 ,(SELECT A.SM_CFNM FROM TBZ00_COMMCD A WHERE A.LG_CD = 'A01009' AND A.SM_CD = C.DEST_PCD) DEST_PCD_DESC
		 FROM C_TBL02_COIL_COMM A,TBJ01_DLIV_DIR C 
		 WHERE A.DLIV_DIRNO = C.DLIV_DIRNO 
		 AND A.DLIV_DIRNO = ? 
		 AND A.COIL_STAT = '3' 
		 AND A.CUR_PROG_CD = 'DFF'
		 AND A.SHIP_PROG_CD = ?
		 AND A.TRNF_DTIME IS NOT NULL
		 ]]>
	</query>
	
		<query id="UIJ030050_02.SELECT"  desc="根据发运指示号查询可回退的发运明细">
		<![CDATA[
		SELECT A.OLD_SAMPL_NO,
       A.DLIV_DIRNO,
       A.SHIP_DIRNO,
       A.SHIP_INVNO,
       A.CUR_PROG_CD,
       A.COIL_STAT,
       A.SHIP_PROG_CD,
       to_char(to_date(A.TRNF_DTIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') TRNF_DTIME,
       DECODE(C.DLIV_TP, 'C', '汽运', 'T', '火运') DLIV_TP,
       A.TRANS_CAR_NO,
       (SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.TRNF_REG) TRNF_REG,
       A.ORD_NO,
       A.ORD_SEQ,
       DECODE(A.TRNF_SHIFT,
              '0',
              '常白班',
              '1',
              '早班',
              '2',
              '中班',
              '3',
              '晚班') TRNF_SHIFT,
       DECODE(A.TRNF_GROUP,
              '0',
              '常白班',
              '1',
              '甲班',
              '2',
              '乙班',
              '3',
              '丙班',
              '4',
              '丁班') TRNF_GROUP,
       to_char(to_date(A.TRNF_USE_TIME, 'yyyy-mm-dd hh24:mi:ss'),
               'yyyy-mm-dd hh24:mi:ss') TRNF_USE_TIME,
       (select CUST_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD =
               (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO)
           and REC_TP = '01') CUST_NM,
       (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = C.ORD_CUST_CD
           and REC_TP = '02') ORD_NM,
       (SELECT A.SM_CFNM
          FROM TBZ00_COMMCD A
         WHERE A.LG_CD = 'A01009'
           AND A.SM_CD = C.DEST_PCD) DEST_PCD_DESC
  FROM C_TBL02_COIL_COMM A, TBJ01_DLIV_DIR C
 WHERE A.DLIV_DIRNO = C.DLIV_DIRNO
   AND A.DLIV_DIRNO = ?
   AND A.COIL_STAT = '3'
   AND A.CUR_PROG_CD = 'DFF'
   AND A.SHIP_PROG_CD = '08'
   AND A.TRNF_DTIME IS NOT NULL
   
   union all
   
   SELECT A.OLD_SAMPL_NO,
       A.DLIV_DIRNO,
       A.SHIP_DIRNO,
       A.SHIP_INVNO,
       A.CUR_PROG_CD,
       A.COIL_STAT,
       A.SHIP_PROG_CD,
       to_char(to_date(A.TRNF_DTIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') TRNF_DTIME,
       DECODE(C.DLIV_TP, 'C', '汽运', 'T', '火运') DLIV_TP,
       A.TRANS_CAR_NO,
       (SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.TRNF_REG) TRNF_REG,
       A.ORD_NO,
       A.ORD_SEQ,
       DECODE(A.TRNF_SHIFT,
              '0',
              '常白班',
              '1',
              '早班',
              '2',
              '中班',
              '3',
              '晚班') TRNF_SHIFT,
       DECODE(A.TRNF_GROUP,
              '0',
              '常白班',
              '1',
              '甲班',
              '2',
              '乙班',
              '3',
              '丙班',
              '4',
              '丁班') TRNF_GROUP,
       to_char(to_date(A.TRNF_USE_TIME, 'yyyy-mm-dd hh24:mi:ss'),
               'yyyy-mm-dd hh24:mi:ss') TRNF_USE_TIME,
       (select CUST_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD =
               (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO)
           and REC_TP = '01') CUST_NM,
       (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = C.ORD_CUST_CD
           and REC_TP = '02') ORD_NM,
       (SELECT A.SM_CFNM
          FROM TBZ00_COMMCD A
         WHERE A.LG_CD = 'A01009'
           AND A.SM_CD = C.DEST_PCD) DEST_PCD_DESC
  FROM C_TBc02_COIL_COMM A, TBJ01_DLIV_DIR C
 WHERE A.DLIV_DIRNO = C.DLIV_DIRNO
   AND A.DLIV_DIRNO = ?
   AND A.COIL_STAT = '3'
   AND A.CUR_PROG_CD = 'DFF'
   AND A.SHIP_PROG_CD = '08'
   AND A.TRNF_DTIME IS NOT NULL
   
   union all 
   
   
   SELECT A.OLD_SAMPL_NO,
       A.DLIV_DIRNO,
       A.SHIP_DIRNO,
       A.SHIP_INVNO,
       A.CUR_PROG_CD,
       A.COIL_STAT,
       A.SHIP_PROG_CD,
       to_char(to_date(A.TRNF_DTIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') TRNF_DTIME,
       DECODE(C.DLIV_TP, 'C', '汽运', 'T', '火运') DLIV_TP,
       A.TRANS_CAR_NO,
       (SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.TRNF_REG) TRNF_REG,
       A.ORD_NO,
       A.ORD_SEQ,
       DECODE(A.TRNF_SHIFT,
              '0',
              '常白班',
              '1',
              '早班',
              '2',
              '中班',
              '3',
              '晚班') TRNF_SHIFT,
       DECODE(A.TRNF_GROUP,
              '0',
              '常白班',
              '1',
              '甲班',
              '2',
              '乙班',
              '3',
              '丙班',
              '4',
              '丁班') TRNF_GROUP,
       to_char(to_date(A.TRNF_USE_TIME, 'yyyy-mm-dd hh24:mi:ss'),
               'yyyy-mm-dd hh24:mi:ss') TRNF_USE_TIME,
       (select CUST_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD =
               (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO)
           and REC_TP = '01') CUST_NM,
       (select CUST_NM ORD_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD = C.ORD_CUST_CD
           and REC_TP = '02') ORD_NM,
       (SELECT A.SM_CFNM
          FROM TBZ00_COMMCD A
         WHERE A.LG_CD = 'A01009'
           AND A.SM_CD = C.DEST_PCD) DEST_PCD_DESC
  FROM tbh02_coil_comm A, TBJ01_DLIV_DIR C
 WHERE A.DLIV_DIRNO = C.DLIV_DIRNO
   AND A.DLIV_DIRNO = ?
   AND A.COIL_STAT = '3'
   AND A.CUR_PROG_CD = 'SFF'
   AND A.SHIP_PROG_CD = '08'
   AND A.TRNF_DTIME IS NOT NULL
		 ]]>
	</query>
	
	
	<query id="UIJ030050_01.UPDATE"  desc="更新钢卷公共表钢卷状态及发运状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM A SET 
		 A.COIL_STAT = '2'
		 ,A.CUR_PROG_CD = 'DFB'
		 ,A.SHIP_PROG_CD = '03'
		 ,A.TRNF_DTIME = ''
		 ,A.TRANS_CAR_NO = ''
		 ,A.TRNF_REG = ''
		 ,A.TRNF_SHIFT = ''
		 ,A.TRNF_GROUP = ''
		 ,A.TRNF_USE_TIME = ''
		 ,A.BEF_SHIP_INVNO = A.SHIP_INVNO
		 ,A.BEF_CRET_NO = A.CRET_NO
		 ,A.CRET_NO = ''
		 ,A.CUR_LOAD_LOC=A.BEF_LOAD_LOC
		 ,A.RETURN_SHIP_REG = ?
		 ,A.RETURN_SHIP_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
		 WHERE A.DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ030050_02.UPDATE"  desc="清除发运表发运信息">
		<![CDATA[
		UPDATE TBJ01_DLIV_DIR A SET 
		 A.LANE_TP = ''
		 ,A.ENTERANCE_SEQ = ''
		 ,A.ALLOC_SEQ = ''
		 ,a.TRANS_CAR_NO = ''
		 WHERE A.DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ030050_03.UPDATE"  desc="更新运送表运送状态">
		<![CDATA[
		UPDATE TBJ01_SHIP_DIR A SET 
		A.SHIP_PROG_CD = '03'
		 WHERE A.SHIP_DIRNO = (SELECT T.SHIP_DIRNO FROM TBJ01_DLIV_DIR T WHERE T.DLIV_DIRNO = ?)
		 ]]>
	</query>
	<query id="UIJ030050_04.UPDATE"  desc="将质保书状态改为失效">
		<![CDATA[
		UPDATE TBB02_MS_COM T
		SET    T.USED_TP    =           'N'
		WHERE  T.SHIP_DIRNO in
		(
		select distinct(l.SHIP_DIRNO) from c_tbl02_coil_comm l where l.DLIV_DIRNO = ?
		)
		 ]]>
	</query>
	<query id="UIJ030050_01.DELETE"  desc=" 删除结算接口表数据">
		<![CDATA[
		DELETE TBJ02_COIL_COMM A WHERE A.DLIV_DIRNO = ?
		 ]]>
	</query>
</queryMap>