XinYu_RZMES/.svn/pristine/0a/0a669303718268c382b75194734b0d28dec20e8e.svn-base

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


    <query id="UIJ010061.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 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,
       nvl(A.DLIV_DIRNO,A.APPOINT_NOTE) DLIV_DIRNO,
       a.TRANS_CAR_NO,
      
       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,
       'SGF' 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,

       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,
       (SELECT *
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'J01005'
           AND SM_CD IN ('05', '06', '07', '08', '09')) Z
 WHERE 1 = 1
  AND A.SHIP_PROG_CD = Z.SM_CD(+)
   AND A.APPOINT_YN IN ('06', '07', '08', '09')
   AND A.APPOINT_YN=:1
   AND A.TRANS_CAR_NO || '&' LIKE :2 || '%'
   AND nvl(A.DLIV_DIRNO,A.APPOINT_NOTE) LIKE :3 || '%'
   AND A.TRNF_DTIME || '&' LIKE :4|| '%'
 ORDER BY A.SHIP_DIRNO, A.DLIV_DIRNO
		]]>
	</query>

    <query id="UIJ010062.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 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,
       nvl(A.DLIV_DIRNO,A.APPOINT_NOTE) DLIV_DIRNO,
       a.TRANS_CAR_NO,
      
       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,

       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,
       (SELECT *
          FROM TBZ00_COMMCD
         WHERE LG_CD = 'J01005'
           AND SM_CD IN ('05', '06', '07', '08', '09')) Z
 WHERE 1 = 1
  AND A.SHIP_PROG_CD = Z.SM_CD(+)
   AND A.APPOINT_YN IN ('06', '07', '08', '09')
   AND A.APPOINT_YN=:1
   AND A.SHIP_PROG_CD=:2
   AND A.TRANS_CAR_NO || '&' LIKE :3 || '%'
   AND nvl(A.DLIV_DIRNO,A.APPOINT_NOTE) LIKE :4 || '%'
   AND A.TRNF_DTIME || '&' LIKE :5|| '%'
 ORDER BY A.SHIP_DIRNO, A.DLIV_DIRNO
		]]>
	</query>


    <query id="UIJ010061.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')
 -- , TRNF_USE_TIME     = :13
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="UIJ010061.UpdateTime.Save" desc=" 更新时间 " fetchSize="10">
        <![CDATA[
			UPDATE TBH02_COIL_COMM
				SET TRNF_USE_TIME      = :1
				WHERE DLIV_DIRNO      = :2
        ]]>
    	</query>
<query id="UIJ010061.Checkbj.Select" desc=" 验证板加是否存在 " fetchSize="10">
        <![CDATA[
			
   			SELECT T.* FROM TBH07_BJ_TRANFINFO T WHERE T.OLD_SAMPL_NO =:1
        ]]>
    	</query>
    	
    	<query id="UIJ010061.Checkstatus.Select" desc=" 验证板加是否存在 " fetchSize="10">
        <![CDATA[
			
   			SELECT T.* FROM TBH02_COIL_COMM T WHERE T.OLD_SAMPL_NO =:1 and t.APPOINT_YN ='08'
        ]]>
    	</query>
    <query id="UIJ010061.ModifyShipProgStatusOfCoils.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBH02_COIL_COMM
SET APPOINT_YN = :1
   ,TRANS_CAR_NO = :2
   ,ACT_WGT     = :3
   ,CZD_WGT     = :4	
WHERE COIL_NO    = :5
        ]]>
    </query>

    <query id="UIJ010061.CLEARDLIVNO.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBH02_COIL_COMM
SET APPOINT_YN = :1
   ,TRANS_CAR_NO = :2
   ,ACT_WGT     = :3
   ,CZD_WGT     = :4	
  -- ,APPOINT_NOTE= DLIV_DIRNO   --因为为内部转运，清除计划到此字段记录
   ,DLIV_DIRNO= ''	
WHERE COIL_NO    = :5
        ]]>
    </query>
<query id="UIJ010062.CLEARDLIVNO.Save" desc="  " fetchSize="10">
        <![CDATA[
UPDATE TBH02_COIL_COMM
SET APPOINT_YN = :1
   ,TRANS_CAR_NO = :2
   ,ACT_WGT     = :3
   ,CZD_WGT     = :4	
  -- ,APPOINT_NOTE= DLIV_DIRNO   --因为为内部转运，清除计划到此字段记录 
   ,SHIP_PROG_CD= '08'	
WHERE COIL_NO    = :5
        ]]>
    </query>
</queryMap>