XinYu_RZMES/.svn/pristine/bb/bb52b894e8bb32db5c0df0beceade14f80fd3fd5.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIJ010100_01.select" desc=" 查询运送指示信息 " fetchSize="10">
		<![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,
       B.DLIV_TP,
       D.SM_CFNM AS DLIV_TP_NM,
       B.SHIP_COMP_CD,
       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,
       B.INSTR_COIL_THK||'*'||B.INSTR_COIL_WTH T_W,
	   ISCANCEL   
FROM(SELECT *
     FROM TBJ01_SHIP_DIR
     WHERE 1 = 1
     AND SHIP_PROG_CD = :1           -- SHIP_PROG_CD = '03':?樊硅?措扁
     AND SHIP_DIRNO BETWEEN NVL(:2,'00000101')||'0000' AND NVL(:3,'99991231')||'9999'
     AND SHIP_DIRNO LIKE :4||'%') 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 TBH02_COIL_COMM
          WHERE 1 = 1
          AND CUR_PROG_CD like :5||'%'            -- CUR_PROG_CD = 'SFB'(款价措扁)
          AND SHIP_DIRNO BETWEEN NVL(:6,'00000101')||'0000' AND NVL(:7,'99991231')||'9999'
          GROUP BY DLIV_DIRNO) Y
     WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
     AND X.DLIV_TP LIKE :8||'%'
     AND NVL(X.LANE_TP,'*') LIKE :9||'%'
     AND NVL(X.ENTERANCE_SEQ,0) = NVL(:10,0)) B
   ,(SELECT *
     FROM TBJ00_SHIP_COMP
     WHERE SHIP_COMP_CD LIKE :11||'%') C
   ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012' AND SM_CD LIKE :12||'%') 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 = :13
AND B.DLIV_DIRNO LIKE :14||'%'
ORDER BY B.ALLOC_SEQ,B.DLIV_DIRNO 
		]]>
	</query>
	
	
	<query id="UIJ010100_01.update" desc=" 查询运送指示信息 " fetchSize="10">
		<![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>