XinYu_LZMES/.svn/pristine/19/19064cdf5b708223e2a58f4be2001586adc5c3a7.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发行装车明细">
	<query id="UIJ060020_01.SELECT"  desc="查询发货实绩信息">
		<![CDATA[
		SELECT 'N' AS CHK
		 , A.SHIP_PROG_CD
		 , Z.SM_CFNM AS SHIP_PROG_NM
		 , A.SHIP_DIRNO
		,(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 SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.ORD_USE_CD and LG_CD = 'A01007') SM_CFNM
		 , A.ORD_USE_CD
		 , A.SPEC_ABBSYM
		 , A.SPEC_STL_GRD
		 , A.DLIV_DIRNO
		 , C.DLIV_TP
		 , C.DLIV_TP_DESC
		 , C.SHIP_COMP_CD
		 , NVL(C.SHIP_COMP_NM,C.SHIP_COMP_CD) SHIP_COMP_NM
		 , C.TRANS_CAR_NO
		 , C.GET_ON_PCD
		 , C.DEST_PCD
		 , C.DEST_PCD_DESC
		 , C.EXLV_LINE_CD
		 , C.EXLV_LINE_CD_DESC
		 , C.GET_OFF_PCD
		 , C.BY_SEA_YN
		 , C.ARRIVAL_CD 
		 , C.ARRIVAL_CD_DESC
		 , A.OLD_SAMPL_NO OLD_SAMPL_NO
		 , A.COIL_NO  
		 , A.TOT_DEC_GRD 
		, DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格',null) as  TOT_DEC_GRD_DESC
		 , NVL(A.ACT_WGT,0) COIL_WGT
		 , NVL(A.CAL_WGT,0) CAL_WGT
		 , A.COIL_OUTDIA
		 , A.SHIP_INVNO
		 , A.CRET_NO
		, NVL(TO_CHAR(TO_DATE(A.TRNF_DTIME,'YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS'),'-') AS TRNF_DTIME
		 ,'3'                   AS COIL_STAT
		 ,'DFF'                 AS CUR_PROG_CD
		 ,'UIJ060020'           AS CUR_PROG_CD_PGM
		 , A.CUR_PROG_CD        AS BEF_PROG_CD
		 , A.CUR_PROG_CD_DTIME  AS BEF_PROG_CD_DTIME
		 , A.CUR_PROG_CD_PGM    AS BEF_PROG_CD_PGM
		 , A.ORD_NO, A.ORD_SEQ
		 ,A.MATLQLTY_DEC_GRD 
		, DECODE(A.MATLQLTY_DEC_GRD,'1','合格','2','不合格',null) as MATLQLTY_DEC_GRD_DESC
		 , to_char(A.INSTR_COIL_THK,'FM990.099')||'*'||A.INSTR_COIL_WTH T_W
		 , TO_CHAR(SYSDATE,'YYYY-MM-DD') SYS_TIME
		 , A.CZD_WGT
		, 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
		 ,A.HCOLOUR_REM
		 ,A.SLAB_NO       --板加添加，以下相同
		,to_char(to_date((SELECT T.DEL_TO_DATE FROM TBE02_ORD_PRC T WHERE T.ORD_NO = A.ORD_NO AND T.ORD_SEQ = A.ORD_SEQ),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') DEVLMT_DTIME
		 ,A.INSTR_COIL_THK
		 ,A.INSTR_COIL_WTH
		 ,A.INSTR_COIL_LEN
		 ,A.INSTR_COIL_OUTDIA
		 ,A.INSTR_COIL_INDIA
		 ,A.INSTR_COIL_WGT
		 ,C.SM_CD
		,(select CUST_CD ORD_CD FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_CD 
		 ,A.COIL_LEN
		 ,A.COIL_INDIA
		 ,A.COIL_THK
		 ,A.COIL_WTH
		 ,A.PRODNM_CD
		 ,A.INGR_DEC_DTIME       --成份判定
		 ,A.MATLQLTY_DEC_DTIME   --材质判定
		 ,A.EXTSHAPE_DEC_DTIME   --外观判定
		 ,A.TOT_DEC_DTIME        --综合判定时间
		 ,A.INGR_DEC_GRD
		 ,A.SIZE_DEC_RST
		 ,A.EXTSHAPE_DEC_GRD
		 ,A.WGT_DEC_RST
		 ,A.MILL_DTIME
		 ,A.CRK_CD1
		 ,A.CRK_CD2
		 ,A.CRK_CD3
		 ,A.CRK_CD4
		 ,A.CRK_CD5
		  ,DECODE(NVL(A.PACKAGE_LEVEL, 1),
              '2',
              '20',
              '3',
              case when a.ACT_WGT<10000 then '20' 
              else '40' END,
              '4',
              case when a.ACT_WGT<10000 then '40' 
              else '70'
              end,
              '0') PKG_WGT
		-- ,DECODE(NVL(A.PACKAGE_LEVEL,1),'2','22','3','110','4','100','0') PKG_WGT  --包装重量
		 FROM C_TBL02_COIL_COMM A
		 , TBJ01_SHIP_DIR  B
		,(SELECT X.*, Y.SHIP_COMP_NM, Z.SM_CFNM AS DLIV_TP_DESC
		 , Z1.SM_CFNM AS DEST_PCD_DESC
		 , Z2.SM_CFNM AS EXLV_LINE_CD_DESC
		 , Z3.SM_CFNM AS ARRIVAL_CD_DESC
		 , Z1.SM_CD  --板加 目的地代码
		 FROM TBJ01_DLIV_DIR X
		 , TBJ00_SHIP_COMP Y
		,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01012') Z
		 ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01009') Z1
		 ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01015') Z2 
		 ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01009') Z3
		 WHERE 1 = 1
		 AND X.SHIP_COMP_CD = Y.SHIP_COMP_CD(+)
		 AND X.DLIV_TP = Z.SM_CD(+)
		 AND X.DEST_PCD = Z1.SM_CD(+)
		 AND X.EXLV_LINE_CD = Z2.SM_CD(+)
		 AND X.ARRIVAL_CD = Z3.SM_CD(+)
		 AND X.DLIV_TP LIKE ?||'%') C
		,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'J01005' AND SM_CD IN ('05','06','07','08','09')) Z
		 WHERE 1 = 1
		 AND A.SHIP_DIRNO = B.SHIP_DIRNO
		 AND A.DLIV_DIRNO = C.DLIV_DIRNO
		 AND A.SHIP_PROG_CD = Z.SM_CD(+)
		 AND A.SHIP_PROG_CD IN ('06','07','08')
		 AND A.CUR_PROG_CD LIKE ?||'%'
		 AND A.TRANS_CAR_NO||'&' LIKE ?||'%'
		 AND A.DLIV_DIRNO LIKE ?||'%'
		 AND A.SHIP_PROG_CD LIKE ?||'%'
		 AND A.TRNF_DTIME||'&' LIKE ?||'%'
		 ORDER BY A.SHIP_DIRNO, A.DLIV_DIRNO
		 ]]>
	</query>
	<query id="UIJ060020_01.UPDATE"  desc="更改钢卷公共表发货状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
			 SET COIL_STAT         = ?
			 , CUR_PROG_CD       = ?
			 , CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			 , CUR_PROG_CD_PGM   = ?
			 , BEF_PROG_CD       = ?
			 , BEF_PROG_CD_DTIME = ?
			 , BEF_PROG_CD_PGM   = ?
			 , TRANS_CAR_NO      = ?
			 , TRNF_SHIFT        = ?
			 , TRNF_GROUP        = ?
			 , TRNF_REG          = ?
			 , TRNF_DTIME        = ?
			 --记录修改前重量
			 --, BEF_ACT_WGT = ACT_WGT
			 --, ACT_WGT = ?
			 , TRNF_USE_TIME     = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			 ,OUT_YARD_KIND      = '2' --发运出库
			 WHERE COIL_NO         = ?
		 ]]>
	</query>
	<query id="UIJ060020_cargocn.select" desc="从货运中国中间表查询某钢卷是否存在" fetchSize="10">
        <![CDATA[
       SELECT COIL_NO,
		       DLIV_DIRNO,
		       SPEC_STL_GRD,
		       TRANS_CAR_NO,
		       COIL_WGT,
		       TRNF_DTIME,
		       ORD_NM,
		       DEST_PCD_DESC,
		       CUST_NM,
		       SHIP_COMP_CD,
		       SHIP_COMP_NM,
		       SM_CFNM,
		       SHIPPERCITY,
		       SHIPPERADDR,
		       ORDERTYPE,
		       SHIPPERCONTACTNAME,
		       SHIPPERCONTACTPHONE,
		       RECEIVECONTACTNAME,
		       RECEIVECONTACTPHONE,
		       FREIGHTPAYSIDE,
		       DRIVERNAME,
		       DRIVERPHONE,
		       SHIP_PROG_CD,
		       REG_PGM_ID,
		       REG_ID,
		       REG_DTIME,
		       MOD_PGM_ID,
		       MOD_ID,
		       MOD_DTIME,
		       RECEIVECITY,
		       TRANSMODE,
		       EDITFLAG,
		       PACTNO,
		       KZZL,
		       PKG_WGT
		  FROM TBB01_CARGOCN_DATA
		 WHERE COIL_NO = ?
        ]]>
    </query>
    <query id="UIJ060020_cargocn.dlivselect" desc="从货运中国中间表查询某发运指示号是否存在" fetchSize="10">
        <![CDATA[
       SELECT COIL_NO,
		       DLIV_DIRNO,
		       SPEC_STL_GRD,
		       TRANS_CAR_NO,
		       COIL_WGT,
		       TRNF_DTIME,
		       ORD_NM,
		       DEST_PCD_DESC,
		       CUST_NM,
		       SHIP_COMP_CD,
		       SHIP_COMP_NM,
		       SM_CFNM,
		       SHIPPERCITY,
		       SHIPPERADDR,
		       ORDERTYPE,
		       SHIPPERCONTACTNAME,
		       SHIPPERCONTACTPHONE,
		       RECEIVECONTACTNAME,
		       RECEIVECONTACTPHONE,
		       FREIGHTPAYSIDE,
		       DRIVERNAME,
		       DRIVERPHONE,
		       SHIP_PROG_CD,
		       REG_PGM_ID,
		       REG_ID,
		       REG_DTIME,
		       MOD_PGM_ID,
		       MOD_ID,
		       MOD_DTIME,
		       RECEIVECITY,
		       TRANSMODE,
		       EDITFLAG,
		       PACTNO,
		       KZZL,
		       PKG_WGT
		  FROM TBB01_CARGOCN_DATA
		 WHERE DLIV_DIRNO = ?
        ]]>
    </query>
    <query id="UIJ060020_cx.select" desc="从产销数据库查收货城市、收货人、电话" fetchSize="10">
        <![CDATA[
          SELECT CX.UNLOADDOCK,CX.SHR,CX.SHRDH FROM SEL_STATIONINFO@XGCX CX WHERE CX.STATIONNAME  LIKE ? || '%'
        ]]>
    </query>
    <query id="UIJ060020_cargocn.update" desc="更新已发送到货运中国平台数据的状态" fetchSize="10">
        <![CDATA[
          UPDATE TBB01_CARGOCN_DATA T
			 SET T.SHIP_PROG_CD  = '7',
				 T.MOD_DTIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
			 WHERE T.DLIV_DIRNO = ?
			   AND T.SHIP_PROG_CD != '7'
        ]]>
    </query> 
    <query id="UIJ060020_cargocn_01.update" desc="更新已发送到货运中国平台数据的状态" fetchSize="10">
        <![CDATA[
          UPDATE TBB01_CARGOCN_DATA T
			 SET T.SHIP_PROG_CD  = '6',
			     T.EDITFLAG = 'C',
				 T.MOD_DTIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
			 WHERE T.DLIV_DIRNO = ?
        ]]>
    </query>   
    <query id="UIJ060020_cargocn_02.update" desc="根据钢卷号更新已发送到货运中国平台数据的状态" fetchSize="10">
        <![CDATA[
          UPDATE TBB01_CARGOCN_DATA T
			 SET T.SHIP_PROG_CD  = '6',
			     T.EDITFLAG = 'C',
				 T.MOD_DTIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
			 WHERE T.COIL_NO = ?
        ]]>
    </query> 
     <query id="UIJ060020_cargocn_03.update" desc="根据钢卷号更新已发送到货运中国平台数据的状态" fetchSize="10">
        <![CDATA[
          UPDATE TBB01_CARGOCN_DATA T
			 SET T.SHIP_PROG_CD  = '6',
			     T.EDITFLAG = 'D',
				 T.MOD_DTIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
			 WHERE T.DLIV_DIRNO = ?
        ]]>
    </query>    
    <query id="UIJ060020_cargocn.delete" desc="根据钢卷号删除货运中国中间表数据" fetchSize="10">
        <![CDATA[
          delete from TBB01_CARGOCN_DATA  where COIL_NO = ?  
        ]]>
    </query>
    <query id="UIJ060020_cargocn_01.delete" desc="根据指示号删除货运中国中间表数据" fetchSize="10">
        <![CDATA[
          delete from TBB01_CARGOCN_DATA  where DLIV_DIRNO = ?  
        ]]>
    </query>
    <query id="UIJ060020_cargocn.insert" desc="插入待发送的数据到货运中国中间表" fetchSize="10">
        <![CDATA[
          INSERT INTO TBB01_CARGOCN_DATA
			  (COIL_NO,
			   DLIV_DIRNO,
			   SPEC_STL_GRD,
			   TRANS_CAR_NO,
			   COIL_WGT,
			   TRNF_DTIME,
			   ORD_NM,
			   DEST_PCD_DESC,
			   CUST_NM,
			   SHIP_COMP_NM,
			   SM_CFNM,
			   SHIPPERCITY,
			   SHIPPERADDR,
			   ORDERTYPE,
			   RECEIVECITY,
			   RECEIVECONTACTNAME,
			   RECEIVECONTACTPHONE,
			   SHIP_PROG_CD,
			   REG_PGM_ID,
			   REG_ID,
			   REG_DTIME,
			   TRANSMODE,
			   EDITFLAG,
			   PACTNO,
			   KZZL,
			   PKG_WGT) 
			 values 
			  (?,?,?,?,?,TO_CHAR(TO_DATE(?,'YYYYMMDD'),'yyyy-MM-dd HH24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS'),decode(?,'T','R','C','T','0'),?,?,?,?)
        ]]>
    </query>    
	<query id="UIJ060020_02.UPDATE"  desc="更改发运表车辆号">
		<![CDATA[
		UPDATE tbj01_dliv_dir
			 SET TRANS_CAR_NO      = ?
			 WHERE DLIV_DIRNO      = ?
		 ]]>
	</query>
	<query id="UIJ060020_03.UPDATE"  desc="更新钢卷公共表发运状态及发运相关信息">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
		 SET SHIP_PROG_CD = ?
		 ,TRANS_CAR_NO = ?
		 --,ACT_WGT     = ?
		 ,CZD_WGT     = ?	
		 WHERE COIL_NO    = ?
		 ]]>
	</query>
	<query id="UIJ060020_04.UPDATE"  desc="改变发货表发货指示状态">
		<![CDATA[
		UPDATE TBJ01_SHIP_DIR
		 SET SHIP_PROG_CD = ?
		 WHERE SHIP_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ060020_05.UPDATE"  desc="">
		<![CDATA[
		UPDATE TBJ01_DLIV_DIR
		 SET TRANS_CAR_NO = ?
		 , INV_PRNCNT = NVL(INV_PRNCNT,0) + 1
		 WHERE DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ060020_06.UPDATE"  desc="更新钢卷公共表发运状态及发运相关信息">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
		 SET SHIP_PROG_CD = ?
		 ,TRANS_CAR_NO = ?
		 --,ACT_WGT     = ?
		 ,CZD_WGT     = ?	
		 WHERE COIL_NO    = ?
		 ]]>
	</query>
	<query id="UIJ060020_07.UPDATE"  desc="修改车辆号">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
		 SET TRANS_CAR_NO = ?,
		 CHG_CARNO = (case when DLIV_TP = 'T' THEN '1' ELSE '0' END)
		 WHERE DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ060020_08.UPDATE"  desc="修改车辆号">
		<![CDATA[
		UPDATE TBJ01_DLIV_DIR
		 SET TRANS_CAR_NO = ?
		 WHERE DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ060020_09.UPDATE"  desc="修改车辆号">
		<![CDATA[
		UPDATE tbj02_coil_comm
		 SET TRANS_CAR_NO = ?
		 WHERE DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ050030_01.UPDATE"  desc="修改车辆号">
		<![CDATA[
		UPDATE C_TBC02_COIL_COMM
		 SET TRANS_CAR_NO = ?,
		 CHG_CARNO = (case when DLIV_TP = 'T' THEN '1' ELSE '0' END)
		 WHERE DLIV_DIRNO = ?
		 ]]>
	</query>
	<query id="UIJ060020_09.CALL"  desc="销售明细">
		<![CDATA[
		{call TEST.ADDSHIPDETAIL(?,?,?,?,?,?,?,?,?,?)}
		 ]]>
	</query>
	<query id="UIJ060020_10.CALL"  desc="酸轧中冶新材明细">
		<![CDATA[
		{call TEST.ADDZYCOILSZ(?)}
		 ]]>
	</query>
	<query id="UIJ060020_11.CALL"  desc="连退中冶新材明细">
		<![CDATA[
		{call TEST.ADDZYCOILLT(?)}
		 ]]>
	</query>
</queryMap>