XinYu_LZMES/.svn/pristine/94/94ea51b3a0476fdc88696581a3747d0e4109ad29.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发运计划查询">
	<query id="UIJ030040_01.SELECT"  desc="发运计划查询">
		<![CDATA[
		SELECT '' CHK,
		 B.SHIP_DIRNO,
		  B.GET_ON_PCD,
		 B.DEST_PCD,
		(SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND b.DEST_PCD = SM_CD) DEST_PCD_DESC,
		 B.EXLV_LINE_CD,
		 (SELECT x.SM_CFNM   FROM TBZ00_COMMCD   x   WHERE LG_CD = 'A01015'     AND b.EXLV_LINE_CD = SM_CD) EXLV_LINE_CD_DESC,
		 (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.CUST_CD and REC_TP='01') CUST_NM, 
		 (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.ORD_CUST_CD and REC_TP='02') ORD_NM,
		 B.GET_OFF_PCD,
		 D.SM_CFNM AS DLIV_TP_NM,
		 C.SHIP_COMP_NM SM_CFNM,
		 B.TRAIN_DLIVNO,
		 B.DLIV_DIRNO,
		 NVL(B.CNT,0) AS CNT,
		 NVL(B.ACT_WGT,0) AS ACT_WGT,
		 B.ALLOC_SEQ,
		 B.TRANS_CAR_NO,
		 B.LANE_TP,
		 B.ENTERANCE_SEQ,
		 B.ORD_NO,
		 B.ORD_SEQ,
		 B.SPEC_STL_GRD,
		 to_char(B.INSTR_COIL_THK,'FM990.099')||'*'||B.INSTR_COIL_WTH T_W,
		 ISCANCEL   
		 FROM(SELECT *
		 FROM TBJ01_SHIP_DIR
		 WHERE 1 = 1
		 AND SHIP_PROG_CD = '03'           -- SHIP_PROG_CD = '03'
		 AND SHIP_DIRNO BETWEEN NVL(?,'00000101')||'0000' AND NVL(?,'99991231')||'9999'
		 AND SHIP_DIRNO LIKE ''||'%') A       
		 ,(SELECT X.*, Y.CNT CNT, Y.ACT_WGT ACT_WGT,Y.ORD_NO,Y.ORD_SEQ,Y.SPEC_STL_GRD,Y.INSTR_COIL_THK,Y.INSTR_COIL_WTH 
		 FROM TBJ01_DLIV_DIR X
		 ,(SELECT DLIV_DIRNO
		 , COUNT(*)            AS CNT
		 , NVL(SUM(ACT_WGT),0) AS ACT_WGT
		 , MAX(ORD_NO)         AS ORD_NO
		 , MAX(ORD_SEQ)        AS ORD_SEQ
		 , MAX(SPEC_STL_GRD)   AS SPEC_STL_GRD
		 , MAX(INSTR_COIL_THK) AS INSTR_COIL_THK
		 , MAX(INSTR_COIL_WTH) AS INSTR_COIL_WTH
		 FROM C_TBL02_COIL_COMM
		 WHERE 1 = 1
		 AND CUR_PROG_CD like 'SFB'||'%'            -- CUR_PROG_CD = 'SFB'
		 AND SHIP_DIRNO BETWEEN NVL(?,'00000101')||'0000' AND NVL(?,'99991231')||'9999'
		 GROUP BY DLIV_DIRNO) Y
		 WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
		 AND X.DLIV_TP LIKE ?||'%'
		 AND NVL(X.LANE_TP,'*') LIKE ''||'%'
		 AND NVL(X.ENTERANCE_SEQ,0) = NVL('',0)) B
		 ,(SELECT *
		 FROM TBJ00_SHIP_COMP
		 WHERE SHIP_COMP_CD LIKE ''||'%') C
		 ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012' AND SM_CD LIKE ?||'%') D
		 WHERE A.SHIP_DIRNO = B.SHIP_DIRNO(+)
		 AND B.SHIP_COMP_CD = C.SHIP_COMP_CD(+)
		 AND D.SM_CD = B.DLIV_TP 
		 AND B.ISDELETED = 0
		 AND B.ISCANCEL = ?
		 AND B.DLIV_DIRNO LIKE ?||'%'
		 ORDER BY B.ALLOC_SEQ,B.DLIV_DIRNO 
		 ]]>
	</query>
	<query id="UIJ030040_01.UPDATE"  desc="发运计划回退">
		<![CDATA[
		update TBJ01_DLIV_DIR a
		 set   A.RETURN_GED   = ?,
		A.RETURN_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
		 A.ISCANCEL     = '1',
		 A.DLIV_PROG_CD = '02' 
		 WHERE A.DLIV_DIRNO   = ? 
		]]>
	</query>
</queryMap>