| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586 |
- <?xml version="1.0" encoding="UTF-8"?>
- <queryMap desc="发运计划查询">
- <query id="UIJ060030_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="UIJ060030_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.CUR_LOAD_LOC=A.BEF_LOAD_LOC
- ,A.CRET_NO = ''
- ,A.RETURN_SHIP_REG = ?
- ,A.RETURN_SHIP_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
- WHERE A.DLIV_DIRNO = ?
- ]]>
- </query>
- <query id="UIJ060030_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="UIJ060030_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="UIJ060030_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="UIJ060030_01.DELETE" desc=" 删除结算接口表数据">
- <![CDATA[
- DELETE TBJ02_COIL_COMM A WHERE A.DLIV_DIRNO = ? AND A.ISCHANGEWGT = '1'
- ]]>
- </query>
- </queryMap>
|
