XinYu_LZMES/.svn/pristine/db/dbe55987b199994da8e3cb0ccec23e208ac649c6.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发行装车明细">
	<query id="UIJ030070_01.SELECT"  desc="移送优特钢信息查询">
		<![CDATA[
		SELECT 'N' AS CHK,
       A.ROUTE SHIP_PROG_CD,
    
       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,
       A.DLIV_DIRNO,
       
       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,
       'UIJ030030' 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,
       
       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
  FROM C_TBL02_COIL_COMM A
 WHERE 1 = 1
   AND A.old_sampl_no|| '&' LIKE ? || '%'
   AND A.TRNF_DTIME || '&' LIKE ? || '%'
   AND NVL(A.ROUTE,'0')  LIKE ? || '%'     --移送优特钢标识0待移送 1已移送未接收 2已接收 3已退回
   AND A.ORD_NO LIKE 'WZ%'
 ORDER BY A.SHIP_DIRNO
		 ]]>
	</query>
		<query id="UIJ030070_01.UPDATE"  desc="更改钢卷公共表发货状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
			 SET COIL_STAT         = '3'
			 , CUR_PROG_CD       = 'YFF'
			 , CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			 , CUR_PROG_CD_PGM   = 'UIJ030070'
			 , BEF_PROG_CD       = CUR_PROG_CD
			 , BEF_PROG_CD_DTIME = CUR_PROG_CD_DTIME
			 , BEF_PROG_CD_PGM   = CUR_PROG_CD_PGM
			 , TRNF_SHIFT        = ?
			 , TRNF_GROUP        = ?
			 , TRNF_REG          = ?
			 , TRNF_DTIME        = ?
			 ,ROUTE      = '1' --发运出库
			 WHERE COIL_NO         = ?
			 AND ROUTE='0'
		 ]]>
	</query>
		<query id="UIJ030070_02.UPDATE"  desc="更改钢卷公共表发货状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM
			 SET COIL_STAT         = '2'
			 , CUR_PROG_CD       = 'DBA'
			 , CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			 , CUR_PROG_CD_PGM   = 'UIJ030070'
			 , BEF_PROG_CD       = CUR_PROG_CD
			 , BEF_PROG_CD_DTIME = CUR_PROG_CD_DTIME
			 , BEF_PROG_CD_PGM   = CUR_PROG_CD_PGM
			
			 ,ROUTE      = '4' --卷板接收回库
			 WHERE COIL_NO         = ? 
			 AND ROUTE='3'
		 ]]>
	</query>
</queryMap>