XinYu_RZMES/.svn/pristine/5c/5c0dc5d36d16e28adb3ae05e454aafac4ffb6be2.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="SHIPPING">


	<query id="UIJ010060.LOV.ShipDirNo.find" desc=" LOVResult_1 " fetchSize="10">
        <![CDATA[
SELECT A.SHIP_DIRNO, A.SHIP_PROG_CD
FROM TBJ01_SHIP_DIR A
WHERE A.SHIP_PROG_CD IN ('05','07','08')   -- (... 05:装车待机 06:行车作业结束(Only CoilComm) 07:发货结束 08:明细发行)
AND A.SHIP_PROG_CD LIKE :1||'%'
        ]]>
    </query>


    <query id="UIJ010060.LOV.DlivDirNo.find" desc=" LOVResult_2 " fetchSize="10">
        <![CDATA[
SELECT B.DLIV_DIRNO, A.SHIP_PROG_CD, A.SHIP_DIRNO
FROM TBJ01_SHIP_DIR A, TBJ01_DLIV_DIR B
WHERE B.SHIP_DIRNO = A.SHIP_DIRNO
AND A.SHIP_PROG_CD IN ('05','07','08')     -- (... 05:装车待机 06:行车作业结束(Only CoilComm) 07:发货结束 08:明细发行)
AND A.SHIP_PROG_CD LIKE :1||'%'
        ]]>
    </query>


    <query id="UIJ010060.LOV.DlivType.find" desc=" LOVResult_3 " fetchSize="10">
        <![CDATA[
SELECT SM_CD, SM_CFNM, LG_CD, LG_NM
FROM TBZ00_COMMCD
WHERE LG_CD = 'A01012'
AND USED_YN = 'Y'
ORDER BY CD_SEQ
        ]]>
    </query>


<!--
Begin Commented by Mr.GONG 2009.03.24
    <query id="UIJ010060.LOV.InvNo.find" desc=" LOVResult_4 " fetchSize="10">
        <![CDATA[
SELECT B.INV_NO, B.DLIV_DIRNO, A.SHIP_PROG_CD, A.SHIP_DIRNO, B.INV_PRNCNT
FROM TBJ01_SHIP_DIR A, TBJ01_DLIV_DIR B
WHERE A.SHIP_DIRNO = B.SHIP_DIRNO
AND A.SHIP_PROG_CD IN ('07','08')
ORDER BY B.INV_NO
        ]]>
    </query>
End Commented by Mr.GONG 2009.03.24
-->


    <query id="UIJ010060.ShippingResults.Select" desc="  " fetchSize="10">
		<![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
     ,'SFF'                 AS CUR_PROG_CD
     ,'UIJ010060'           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
     , A.INSTR_COIL_THK||'*'||A.INSTR_COIL_WTH T_W
   , TO_CHAR(SYSDATE,'YYYY-MM-DD') SYS_TIME
   , A.CZD_WGT
   , DECODE(A.TRNF_SHIFT,'1','早','2','中','3','晚') TRNF_SHIFT
   , DECODE(A.TRNF_GROUP,'A','甲','B','乙','C','丙','D','丁') TRNF_GROUP
   , A.TRNF_USE_TIME
   ,A.HCOLOUR_REM
   ,A.SLAB_NO       --板加添加，以下相同
   ,(SELECT T.DEL_TO_DATE FROM TBE02_ORD_PRC T WHERE T.ORD_NO = A.ORD_NO AND T.ORD_SEQ = A.ORD_SEQ) 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
FROM TBH02_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 :1||'%') 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','09')
AND A.CUR_PROG_CD LIKE :2||'%'
AND A.TRANS_CAR_NO||'&' LIKE :3||'%'
AND A.DLIV_DIRNO LIKE :4||'%'
AND A.SHIP_PROG_CD LIKE :5||'%'
AND A.TRNF_DTIME||'&' LIKE :6||'%'
ORDER BY A.SHIP_DIRNO, A.DLIV_DIRNO
/***********************************************************************************
:1 - 'C':Car 'T':Train
:2 - 'SFB' Before invoice print.
   - 'SFC' After invoice printed
:3 - YYYYMMDD9999
:4 - YYYYMMDDS999
:5 - '06':行车作业结束 '07':发货结束 '08':明细发行)
************************************************************************************/
		]]>
	</query>


    <query id="UIJ010060.ModifyCoilProgStatusOfCoils.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBH02_COIL_COMM
SET COIL_STAT         = :1
  , CUR_PROG_CD       = :2
  , CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
  , CUR_PROG_CD_PGM   = :3
  , BEF_PROG_CD       = :4
  , BEF_PROG_CD_DTIME = :5
  , BEF_PROG_CD_PGM   = :6
  , TRANS_CAR_NO      = :7
  , TRNF_SHIFT        = :8
  , TRNF_GROUP        = :9
  , TRNF_REG          = :10
  , TRNF_DTIME        = :11
  --记录修改前重量
  , BEF_ACT_WGT       = ACT_WGT
  , ACT_WGT           = :12
  , TRNF_USE_TIME     = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
WHERE COIL_NO         = :13
        ]]>
    </query>
    <query id="UIJ010060.ModifyCoilProgStatusOfCoilsTrans.Save" desc=" 这里需要回填清单号 " fetchSize="10">
        <![CDATA[
UPDATE tbj01_dliv_dir
SET TRANS_CAR_NO      = :1
WHERE DLIV_DIRNO      = :2
        ]]>
    </query>


    <query id="UIJ010060.ModifyShipProgStatusOfCoils.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBH02_COIL_COMM
SET SHIP_PROG_CD = :1
   ,TRANS_CAR_NO = :2
   ,ACT_WGT     = :3
   ,CZD_WGT     = :4		
WHERE COIL_NO    = :5
        ]]>
    </query>


	<query id="UIJ010060.ModifyShipProgStatusOfShip.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBJ01_SHIP_DIR
SET SHIP_PROG_CD = :1
WHERE SHIP_DIRNO = :2
		]]>
    </query>


    <query id="UIJ010060.InvoiceInfo.DeliverDir.Save" desc=" 这里不在更新发货清单号，运输表里的清单号必须唯一 " fetchSize="10">
        <![CDATA[
UPDATE TBJ01_DLIV_DIR
SET TRANS_CAR_NO = :1
  , INV_PRNCNT = NVL(INV_PRNCNT,0) + 1
WHERE DLIV_DIRNO = :2
        ]]>
    </query>
    <query id="UIJ010060_INV_NO.UPDATE" desc=" 生成发货清单号码 " fetchSize="10">
        <![CDATA[
UPDATE   TBJ01_DLIV_DIR
SET      INV_NO = (select 'JB'||to_char(sysdate,'y')||'-'||
	    nvl((select to_char(to_number(nvl(substr(max(inv_no),5,6),0))+1,'FM000000') from tbj01_dliv_dir
	    where inv_no like 'JB'||to_char(sysdate,'y')||'%'),'000001')
	    from dual) 
      , INV_DTIME  = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
WHERE   DLIV_DIRNO = :1
        ]]>
    </query>
    <query id="UIJ010060_BJ.INSERT" desc=" 为板加提供相应的数据 " fetchSize="10">
        <![CDATA[
		INSERT INTO TBH07_BJ_TRANFINFO A   
            (   
               A.SHIP_INVNO
              ,A.OLD_SAMPL_NO
              ,A.COIL_NO
              ,A.SLAB_NO
              ,A.ORD_NO
              ,A.ORD_SEQ
              ,A.DEVLMT_DTIME
              ,A.INSTR_COIL_THK
              ,A.INSTR_COIL_WTH
              ,A.INSTR_COIL_LEN
              ,A.INSTR_COIL_INDIA
              ,A.INSTR_COIL_OUTDIA
              ,A.INSTR_COIL_WGT
              ,A.DEST_CD
              ,A.ORDCUST_CD
              ,A.COIL_LEN
              ,A.COIL_INDIA
              ,A.COIL_OUTDIA
              ,A.COIL_THK
              ,A.COIL_WTH
              ,A.ACT_WGT
              ,A.PRODNM_CD
              ,A.SPEC_ABBSYM
              ,A.SX_BIAOZHUN
              ,A.ORD_USE_CD
              ,A.STL_GRD
              ,A.INGR_DEC_GRD
              ,A.EXTSHAPE_DEC_GRD
              ,A.SIZE_DEC_RST
              ,A.WGT_DEC_RST
              ,A.MATLQLTY_DEC_GRD
              ,A.TOT_DEC_GRD
              ,A.MILL_DTIME
              ,A.INGR_DEC_DTIME
              ,A.EXTSHAPE_DEC_DTIME
              ,A.MATLQLTY_DEC_DTIME
              ,A.TOT_DEC_DTIME
              ,A.CRK_CD1
              ,A.CRK_CD2
              ,A.CRK_CD3
              ,A.CRK_CD4
              ,A.CRK_CD5
              ,A.TRANS_CAR_NO
              ,A.CRET_NO
              ,A.MOVE_SHIFT
              ,A.MOVE_GROUP
              ,A.MOVE_MAN
              ,A.MOVE_TIME
              ,A.ROUTE
              ,A.MOVE_ST
              ,A.CHARGE_NO
              ,A.BAHCT_NUMBER
              ,A.MOVE_LINE
              ,A.ARRIVE_LINE
              ,A.MOVE_TYPE
              ,A.ISNET
            )
            values
            ( 
              :1 
              ,:2
              ,:3
              ,:4
              ,:5
              ,:6
              ,:7
              ,:8
              ,:9
              ,:10
              ,:11
              ,:12
              ,:13
              ,:14
              ,:15
              ,:16
              ,:17
              ,:18
              ,:19
              ,:20
              ,:21 
              ,:22
              ,:23
              ,:24
              ,:25
              ,:26
              ,:27
              ,:28
              ,:29
              ,:30
              ,:31
              ,:32
              ,:33
              ,:34
              ,:35
              ,:36
              ,:37
              ,:38
              ,:39
              ,:40
              ,:41
              ,:42
              ,:43
              ,:44
              ,:45
              ,:46
              ,:47
              ,:48
              ,:49
              ,:50
			  ,:51
			  ,:52
              ,'三期'
              ,'板加'
              ,'1'
              ,'1'
            )
        ]]>
    </query>
    <query id="UIJ010060_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="UIJ010060_cargocn.select" desc="从货运中国中间表查询某钢卷是否存在" fetchSize="10">
        <![CDATA[
          SELECT * FROM TBB01_CARGOCN_DATA T WHERE T.COIL_NO = ?
        ]]>
    </query>
    <query id="UIJ010060_cargocn.delete" desc="根据钢卷号删除" fetchSize="10">
        <![CDATA[
          delete from TBB01_CARGOCN_DATA  where COIL_NO = ?  
        ]]>
    </query>
    <query id="UIJ010060_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="UIJ010060_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) 
			 values 
			  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS'))
        ]]>
    </query>      
    <query id="UIJ010060_BJ.UPDATE" desc=" 把板加移送状态更新到钢卷公共表 " fetchSize="10">
        <![CDATA[
			update TBH02_COIL_COMM A set
              A.SHIP_PROG_CD = ?
			  where    A.OLD_SAMPL_NO = ? 
        ]]>
    </query>
    <query id="UIJ010060_01.CALL" desc="把明细发送结束数据保存到另一个数据库xgcx中">
		   {call TEST.ADDSHIPDETAIL(?,?,?,?,?,?,?,?,?)}
	</query>
	<query id="UIJ010060_02.CALL" desc="把明细发送结束数据保存到中冶新材接口表">
		   {call TEST.ADDZYCOILRZ(?)}
	</query>
</queryMap>