XinYu_RZMES/.svn/pristine/73/73819205b1ca3c6964662e9c3dd209602ae46e60.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH051100_01.select" desc=" 库存数据查询 " fetchSize="10">
		<![CDATA[
SELECT     '' SEQ
           ,'' CHK
           ,A.OLD_SAMPL_NO
           ,XB_PACKAGE.GF_COMNNAME_FIND('A01004' , P.PRD_TY)  PRD_TY
           ,A.SPEC_STL_GRD
           ,A.COIL_THK
           ,A.COIL_WTH
           ,A.INSTR_COIL_THK
           ,A.INSTR_COIL_WTH  
           ,A.ACT_WGT
           ,A.COIL_SERIES  --系列
           ,A.COIL_PROD_GRADE ---产品等级
           ,A.COIL_UN_REMARK --异常卷备注
           ,A.COIL_DESC      --钢卷说明
           ,A.COIL_REMARK    --备注
           ,DECODE(A.ORD_FL,'1','订单材','2','余材','','未判') ORD_FL
           ,DECODE(A.QK_YN,'Y','清库卷') QK_YN  
           ,A.ORD_NO
           ,A.ORD_SEQ
           ,A.CUR_LOAD_LOC   
           ,DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','3','次品','4','废品') TOT_DEC_GRD
           ,DECODE(A.INGR_DEC_GRD,'0','综合判定取消','1','合格','2','不合格') INGR_DEC_GRD
           ,DECODE(A.MATLQLTY_DEC_GRD,'0','无性能要求','1','合格','2','不合格') MATRL_END_CAUSE_CD
           ,DECODE(A.SIZE_DEC_RST,'1','合格','2','不合格') SIZE_DEC_RST
           ,DECODE(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品','待判定') EXTSHAPE_DEC_GRD
           ,DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST
           ,A.EXTSHAPE_DEC_DTIME
           ,A.TOT_DEC_DTIME
           ,A.YARD_ENTRY_DTIME
           ,A.YARD_ENTRY_USE_TIME
           ,A.YARD_ENTRY_REG
           ,A.YARD_ENTRY_SHIFT
           ,A.YARD_ENTRY_GROUP
           ,SUBSTR(B.SLAB_NO,0,10) CHARGE_NO
           ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01012' AND T.SM_CD = C.DLIV_TP) DLIV_TP
           ,(SELECT T.CUST_NM FROM TBZ00_CUSTOMER T WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD
           ,DECODE(A.CUR_PROG_CD,'SFA','发货指示待机','SED','判定待机','SRC','充当待机') CUR_PROG_CD
           ,DECODE(A.REM_YARD,'1','禁发火车','2','卷径比超宽','3','卷规格异常','4','卷外型异常','5','禁发') REM_YARD
           ,A.HCOLOUR_REM  
           ,C.ORD_DEVLMT_DATE 
           ,DECODE(A.COIL_DEFECTIVE,'1A','订单外合格','2A','成分不合格','2E','工序不合格','3B','尺寸不合格','3C','重量不合格','3D','外观不合格','4A','性能不合格') COIL_DEFECTIVE 
           ,A.COIL_DEFECTIVE COIL_DEFECTIVE_DESC
           ,A.BEF_SPEC_STL_GRD
           ,E.SMP_SEND_DTIME --送样
           ,E.SMP_WORK_DTIME --检验
           ,E.QLTY_DCS_DTIME --综合判定
           ,E.SMP_INTO_DTIME --入库时间   
           ,to_char(ROUND(((to_date(E.SMP_SEND_DTIME,'yyyy-mm-dd hh24:mi:ss')-to_date(A.MILL_DTIME,'yyyy-mm-dd hh24:mi:ss'))*86400)/3600,1)) GET_SEND_DTIME --取送样时间（送样-轧制）
           ,to_char(ROUND(((to_date(E.QLTY_DCS_DTIME,'yyyy-mm-dd hh24:mi:ss')-to_date(E.SMP_SEND_DTIME,'yyyy-mm-dd hh24:mi:ss'))*86400)/3600,1)) TO_YANG_DTIME --出样时间（综合判定-送样）
           ,DECODE(A.BEF_MATLQLTY_DEC_GRD,'0','无性能要求','1','合格','2','不合格') BEF_MATRL_END_CAUSE_CD  --清库前性能
           ,DECODE(A.BEF_EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') BEF_EXTSHAPE_DEC_GRD --清库前外观
           ,A.MILL_DTIME 
           ,A.DCS_THK --判定厚度
           ,A.DCS_WTH --判定宽度
           ,C.XSQY --销售区域，生产处要求添加 TL091225
		   ,XB_PACKAGE.GF_COMNNAME_FIND('A01001', P.RCVORD_CLF) RCVORD_CLF --订单类型 add by jungan 20110120
		   ,XB_PACKAGE.GF_COMNNAME_FIND('B01004', P.PROD_LINE) PROD_LINE  --产线  add by jungan 20110120
		   
           ,F.AFFIRMTIME||'/'||F.DLIV_TP AFFIRMTIME  --下发计划确认时间
            ,C.ORD_PLAN_DATA  --新增订单预排期
      FROM TBH02_COIL_COMM A,TBG02_SLAB_COMM B,TBA01_ORD_LINE C,TBA01_ORD_COMM D,TBB02_LOT_COM E,TBJ01_DLIV_DIR F,TBE02_ORD_PRC P
      WHERE '1' = '1'
      AND   A.SLAB_NO = B.SLAB_NO(+)
      AND   A.ORD_NO = C.ORD_NO(+)
      AND   A.ORD_SEQ = C.ORD_SEQ(+)
      AND   A.ORD_NO = D.ORD_NO(+)
      AND   A.SAMPL_NO = E.SMP_NO(+)
      AND   A.DLIV_DIRNO = F.DLIV_DIRNO(+)
      AND   A.ORD_NO = P.ORD_NO(+)
	  AND   A.ORD_SEQ = P.ORD_SEQ(+)
      AND   A.COIL_STAT = '2'
      AND   A.OLD_SAMPL_NO LIKE :1||'%'
      AND   A.SPEC_STL_GRD||'&' LIKE :2||'%'
      AND   A.CUR_LOAD_LOC||'&' LIKE :3||'%'
      AND   A.COIL_THK >= NVL(:4,'000')
      AND   A.COIL_THK <= NVL(:5,'999')
      AND   A.COIL_WTH >= NVL(:6,'00000')
      AND   A.COIL_WTH <= NVL(:7,'99999')
      AND   SUBSTR(NVL(A.YARD_ENTRY_DTIME,99999999999999),1,8) >= NVL(:8,0)
      AND   SUBSTR(NVL(A.YARD_ENTRY_DTIME,0),1,8) <= NVL(:9,99999999999999)
      AND   A.ORD_NO||'&' LIKE :10||'%'
      AND   A.CUR_PROG_CD||'&' LIKE :11||'%'
      AND   C.DLIV_TP||'&' LIKE :12||'%' 
      AND   C.ORD_DEVLMT_DATE||'&' LIKE :13||'%'
      AND   A.COIL_DEFECTIVE||'&' LIKE  :14||'%'
      AND   E.SMP_SEND_DTIME||'&' LIKE  :15||'%'
      AND   C.XSQY||'&' LIKE :16||'%'
      --AND   A.PRODNM_CD LIKE :17||'%'
      AND   INSTR(NVL(:17 , NVL(P.PRD_TY,'NULL')) , NVL(P.PRD_TY,'NULL')) > 0 
      AND   F.DLIV_TP||'&' LIKE :18||'%'
	  AND   P.RCVORD_CLF||'&' LIKE :19||'%'
      AND   P.PROD_LINE||'&'  LIKE :20||'%'
      and   a.SPM_YN||'&' like :21||'%'
    --AND   (A.ORD_FL = '1' AND A.QK_YN <> 'Y')    CASE：订单材
    --AND   (A.ORD_FL = '2' AND A.QK_YN <> 'Y')    CASE：余材
      --AND   (A.QK_YN <> 'Y')                        CASE：清库卷      
      --order by a.OLD_SAMPL_NO desc
		]]>
	</query>
	<query id="UIH051100_02.select" desc=" 统计计算卷数和重量 " fetchSize="10">
		<![CDATA[
            SELECT count(*) COIL_CNT,
            nvl(sum(a.act_wgt)/1000,0) COIL_WGT 
      			FROM TBH02_COIL_COMM A
				  WHERE '1' = '1'
				  AND   A.COIL_STAT = '2'
				  AND   A.OLD_SAMPL_NO LIKE :1||'%'
				  AND   A.SPEC_STL_GRD||'&' LIKE :2||'%'
				  AND   A.CUR_LOAD_LOC||'&' LIKE :3||'%'
				  AND   A.ORD_FL||'&' LIKE :4||'%' 
				  AND   A.COIL_THK >= NVL(:5,'000')
				  AND   A.COIL_THK <= NVL(:6,'999')
				  AND   A.COIL_WTH >= NVL(:7,'00000')
				  AND   A.COIL_WTH <= NVL(:8,'99999')
				  AND   SUBSTR(NVL(A.YARD_ENTRY_DTIME,99999999999999),1,8) >= NVL(:9,0)
				  AND   SUBSTR(NVL(A.YARD_ENTRY_DTIME,0),1,8) <= NVL(:10,99999999999999)
				  AND   A.ORD_NO||'&' LIKE :11||'%'
				  AND   A.CUR_PROG_CD||'&' LIKE :12||'%'
			      AND   C.DLIV_TP||'&' LIKE :13||'%' 
                  AND   C.ORD_DEVLMT_DATE||'&' LIKE :14||'%'
			      AND   A.COIL_DEFECTIVE||'&'  LIKE :15||'%'
				  order by a.OLD_SAMPL_NO desc 
		]]>
	</query>
	<query id="UIH051200_01.update" desc=" 清库修改 " fetchSize="10">
		<![CDATA[
              UPDATE TBH02_COIL_COMM 
              SET    QK_YN = 'Y'
                     ,QK_REG_ID = :1
                     ,COIL_SERIES = :2		--系列
                     ,COIL_PROD_GRADE = :3 	--产品等级
                     ,COIL_UN_REMARK = :4	--异常卷备注
                     ,COIL_DESC = :5    	--钢卷说明 
                     ,COIL_REMARK = :6   	--备注 
                     ,QK_REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                     ,QK_REG_SHIFT = ''
                     ,QK_REG_GROUP = ''
                     ,BEF_MATLQLTY_DEC_GRD = MATLQLTY_DEC_GRD
                     ,BEF_EXTSHAPE_DEC_GRD = EXTSHAPE_DEC_GRD
              WHERE OLD_SAMPL_NO = :7
		]]>
	</query>
	<query id="UIH051200_02.update" desc=" 撤销 " fetchSize="10">
		<![CDATA[
              UPDATE TBH02_COIL_COMM 
              SET    QK_YN = 'N'
                     ,QK_REG_ID = :1
                     ,QK_REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                     ,QK_REG_SHIFT = ''
                     ,QK_REG_GROUP = ''
                     --,BEF_MATLQLTY_DEC_GRD = ''--清库前性能等级
                     --,BEF_EXTSHAPE_DEC_GRD = ''--清库前外观等级
              WHERE OLD_SAMPL_NO = :2
		]]>
	</query>
	
	
	<query id="UIH051200_03.update" desc="编辑内容 " fetchSize="10">
		<![CDATA[
              UPDATE TBH02_COIL_COMM 
              SET     COIL_SERIES = :1		--系列
                     ,COIL_PROD_GRADE = :2 	--产品等级
                     ,COIL_UN_REMARK = :3	--异常卷备注
                     ,COIL_DESC = :4   		--钢卷说明 
                     ,COIL_REMARK = :5   	--备注 
              WHERE OLD_SAMPL_NO = :6
		]]>
	</query>
</queryMap>