XinYu_RZMES/.svn/pristine/4a/4a738f9325f1d9342ce588306657abb3b9ad361d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIK010010_01.select" desc=" FJH热卷数据查询 " fetchSize="10">
		<![CDATA[
SELECT     '' CHK
           ,A.OLD_SAMPL_NO
           ,XB_PACKAGE.GF_COMNNAME_FIND('A01004' , P.PRD_TY)  PRODNM_CD
           ,A.SPEC_STL_GRD
           ,A.COIL_THK,A.COIL_WTH,A.COIL_LEN,A.COIL_INDIA,A.COIL_OUTDIA
           ,A.CRK_CD1,A.CRK_CD2,A.CRK_CD3,A.CRK_CD4,A.CRK_CD5
           ,A.INSTR_COIL_THK
           ,A.INSTR_COIL_WTH  
           ,A.ACT_WGT
           ,DECODE(A.APPOINT_YN,'N','在库未移送','Q','移送出库板加未接收','Y','移送完成板加已入库','P','板加退回入库','在库未移送')  APPOINT_YN---指定
           ,DECODE(BJ.OLD_SAMPL_NO,'','N','Y')   EXIST_YN
           ,A.APPOINT_NOTE---指定备注
           ,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.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 ,TBH02_COIL_COMM_BJ BJ ,TBE04_REP_HIS H
      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.OLD_SAMPL_NO 	= BJ.OLD_SAMPL_NO(+)
	  AND   A.OLD_SAMPL_NO 	= H.MAT_NO(+)
      AND   A.ORD_NO  		= H.UPD_AFT_ORD_NO(+)
      AND   A.ORD_SEQ 		= H.UPD_AFT_ORD_SEQ(+)
	  --AND	A.ORD_FL = '2'
	  --AND   A.ORD_NO LIKE 'XG%'-----绑定指定转运合同
	
      
      AND   A.OLD_SAMPL_NO LIKE :1||'%'
      AND   A.SPEC_STL_GRD||'&' LIKE :2||'%'      
      AND   A.COIL_THK >= NVL(:3,'000')
      AND   A.COIL_THK <= NVL(:4,'999')
      AND   A.COIL_WTH >= NVL(:5,'00000')
      AND   A.COIL_WTH <= NVL(:6,'99999')      
      AND   A.CUR_PROG_CD||'&' LIKE :7||'%'
      AND   INSTR(NVL(:8 , NVL(A.APPOINT_YN,'N')) , NVL(A.APPOINT_YN,'N')) > 0 ---zhiding
      AND	A.CUR_LOAD_LOC||'&' LIKE :9||'%'     
	  AND   A.COIL_STAT IN (:10)
	  AND   A.ORD_NO LIKE :11||'%'
	  AND   NVL(H.REG_DTIME,'NULL') LIKE :12||'%'----待定
		]]>
	</query>
	
	

	
	
	<query id="UIK010010_01.insert" desc="指定移送" fetchSize="10">
		<![CDATA[
            INSERT INTO TBH02_COIL_COMM_BJ T (T.OLD_SAMPL_NO,T.COIL_NO,T.SLAB_NO,T.COIL_STAT
           ,T.PRODNM_CD
           ,T.SPEC_STL_GRD
           ,T.BEF_SPEC_STL_GRD
           ,T.COIL_THK,T.COIL_WTH,T.COIL_LEN,T.COIL_INDIA,T.COIL_OUTDIA
           ,T.CRK_CD1,T.CRK_CD2,T.CRK_CD3,T.CRK_CD4,T.CRK_CD5
           ,T.INSTR_COIL_THK
           ,T.INSTR_COIL_WTH  
           ,T.ACT_WGT
           ,T.ORD_FL
           ,T.QK_YN
           ,T.ORD_NO
           ,T.ORD_SEQ
           ,T.CUR_LOAD_LOC  
           ,T.TOT_DEC_GRD
           ,T.INGR_DEC_GRD
           ,T.MATLQLTY_DEC_GRD
           ,T.SIZE_DEC_RST
           ,T.EXTSHAPE_DEC_GRD
           ,T.WGT_DEC_RST
           ,T.CUR_PROG_CD
           ,T.REM_YARD
           ,T.MILL_DTIME 
           ,T.DCS_THK 
           ,T.DCS_WTH 
           ,T.APPOINT_YN
           ,T.APPOINT_ID
           ,T.APPOINT_DTIME
           ,T.APPOINT_NOTE)
SELECT      A.OLD_SAMPL_NO,A.COIL_NO,A.SLAB_NO,'1'
           ,A.PRODNM_CD
           ,A.SPEC_STL_GRD
           ,A.BEF_SPEC_STL_GRD
           ,A.COIL_THK,A.COIL_WTH,A.COIL_LEN,A.COIL_INDIA,A.COIL_OUTDIA
           ,A.CRK_CD1,A.CRK_CD2,A.CRK_CD3,A.CRK_CD4,A.CRK_CD5
           ,A.INSTR_COIL_THK
           ,A.INSTR_COIL_WTH  
           ,A.ACT_WGT
           ,A.ORD_FL
           ,A.QK_YN
           ,A.ORD_NO
           ,A.ORD_SEQ
           ,''   
           ,A.TOT_DEC_GRD
           ,A.INGR_DEC_GRD
           ,A.MATLQLTY_DEC_GRD
           ,A.SIZE_DEC_RST
           ,A.EXTSHAPE_DEC_GRD
           ,A.WGT_DEC_RST
           ,'SDB'----------钢卷入库待机
           ,A.REM_YARD
           ,A.MILL_DTIME 
           ,A.DCS_THK 
           ,A.DCS_WTH 
           ,'Q'---出热轧库实物到板加但未入库
           ,:1
           ,TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
           ,:2   ---指定备注
FROM TBH02_COIL_COMM A WHERE   A.OLD_SAMPL_NO LIKE :3||'%'
						AND    NVL(A.APPOINT_YN,'N') LIKE 'N'
		]]>
	</query>
	
	
	
	
	<query id="UIK010010_01.update" desc="指定后修改状态" fetchSize="10">
		<![CDATA[
            UPDATE TBH02_COIL_COMM T
			SET T.COIL_STAT = '3'
				,T.BEF_LOAD_LOC	=	T.CUR_LOAD_LOC
				,T.CUR_LOAD_LOC = ''
    			,T.APPOINT_YN = 'Q'
    			,T.CUR_PROG_CD = 'SGF'	-------进程状态
   				,T.CUR_PROG_CD_DTIME 	=	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.CUR_PROG_CD_PGM 		= 	'UIK010010'
          		,T.BEF_PROG_CD 			= 	T.CUR_PROG_CD
          		,T.BEF_PROG_CD_DTIME 	= 	T.CUR_PROG_CD_DTIME
          		,T.BEF_PROG_CD_PGM 		= 	T.CUR_PROG_CD_PGM
    			,T.APPOINT_ID = :1
    			,T.TRNF_REG   = :2
    			,T.TRNF_DTIME			= 	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.TRNF_USE_TIME 		= 	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.APPOINT_DTIME 		= 	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.APPOINT_NOTE = :3
    			
			WHERE T.OLD_SAMPL_NO LIKE :4||'%'
			  AND NVL(T.APPOINT_YN,'N') LIKE 'N'---未指定的变指定及移送
			  AND T.COIL_STAT  = '2'
		]]>
	</query>
	
	
	
	
	<query id="UIK010010_02.update" desc="撤销修改状态" fetchSize="10">
		<![CDATA[
            UPDATE TBH02_COIL_COMM T
			SET T.COIL_STAT = '2'
				,T.CUR_LOAD_LOC	=	T.BEF_LOAD_LOC
				,T.BEF_LOAD_LOC = ''
				,T.CUR_PROG_CD = 'SFA'	-------进程状态
   				,T.CUR_PROG_CD_DTIME 	=	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.CUR_PROG_CD_PGM 		= 	'UIK010011'
          		,T.BEF_PROG_CD 			= 	T.CUR_PROG_CD
          		,T.BEF_PROG_CD_DTIME 	= 	T.CUR_PROG_CD_DTIME
          		,T.BEF_PROG_CD_PGM 		= 	T.CUR_PROG_CD_PGM
    			,T.APPOINT_YN = ''
    			,T.APPOINT_ID = :1
    			,T.APPOINT_DTIME = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.APPOINT_NOTE = ''
			WHERE T.OLD_SAMPL_NO LIKE :2||'%'
			  AND T.APPOINT_YN = 'Q'---撤销板加未入库的卷
		]]>
	</query>
	
	<query id="UIK010010_03.update" desc="板加退回入库状态修改" fetchSize="10">
		<![CDATA[
            UPDATE TBH02_COIL_COMM T
			SET T.COIL_STAT = '2'
				,T.CUR_LOAD_LOC	= :1
				,T.YARD_ENTRY_REG = :2
				,T.YARD_ENTRY_USE_TIME =TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.APPOINT_YN = 'N'
    			,T.APPOINT_NOTE = ''
    			,T.CUR_PROG_CD = 'SRC'--充当待机
    			,T.CUR_PROG_CD_DTIME 	=	TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
    			,T.CUR_PROG_CD_PGM 		= 	'UIK010012'
          		,T.BEF_PROG_CD 			= 	T.CUR_PROG_CD
          		,T.BEF_PROG_CD_DTIME 	= 	T.CUR_PROG_CD_DTIME
          		,T.BEF_PROG_CD_PGM 		= 	T.CUR_PROG_CD_PGM
          		
				,T.ORD_FL = '2'
				--NONORD_CAUSE_CD = ?
				,T.NONORD_OCR_DTIME = TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')
				,T.ORD_NO = ''
				,T.ORD_SEQ = ''
				,T.CUST_CD = ''
				,T.ORDCUST_CD = ''
				,T.ORD_USE_CD = ''
				,T.SPEC_ABBSYM = ''
				,T.DEVLMT_DTIME = ''
				,T.DEST_CD = ''
			WHERE T.OLD_SAMPL_NO LIKE :3||'%'
			  AND T.COIL_STAT = '3'
			  AND T.APPOINT_YN = 'P'---入库板加退回的卷
		]]>
	</query>
	
	<query id="UIK010010_01.delete" desc="删除" fetchSize="10">
		<![CDATA[
            DELETE TBH02_COIL_COMM_BJ T 
            WHERE T.OLD_SAMPL_NO LIKE :1||'%'
              AND T.COIL_STAT = '1'
              
		]]>
	</query>
	
	<query id="UIK010010_02.insert" desc="移送" fetchSize="10">
		<![CDATA[
            UPDATE  TBH02_COIL_COMM_BJ T 
SET     T.APPOINT_YN = 'Q'
       ,T.APPOINT_ID = :1
       ,T.APPOINT_DTIME = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
       ,T.APPOINT_NOTE = :2
       ,T.COIL_STAT = '1'
       ,T.CUR_PROG_CD = 'SDB'
       ,T.CUR_LOAD_LOC = '',
    
       (    T.PRODNM_CD
           ,T.SPEC_STL_GRD
           ,T.BEF_SPEC_STL_GRD
           ,T.COIL_THK,T.COIL_WTH,T.COIL_LEN,T.COIL_INDIA,T.COIL_OUTDIA
           ,T.CRK_CD1,T.CRK_CD2,T.CRK_CD3,T.CRK_CD4,T.CRK_CD5
           ,T.INSTR_COIL_THK
           ,T.INSTR_COIL_WTH  
           ,T.ACT_WGT
           ,T.ORD_FL
     
           ,T.ORD_NO
           ,T.ORD_SEQ
 
           ,T.TOT_DEC_GRD
           ,T.INGR_DEC_GRD
           ,T.MATLQLTY_DEC_GRD
           ,T.SIZE_DEC_RST
           ,T.EXTSHAPE_DEC_GRD
           ,T.WGT_DEC_RST
           
           ,T.REM_YARD
           ,T.MILL_DTIME 
           ,T.DCS_THK 
           ,T.DCS_WTH)
=   (SELECT A.PRODNM_CD
           ,A.SPEC_STL_GRD
           ,A.BEF_SPEC_STL_GRD
           ,A.COIL_THK,A.COIL_WTH,A.COIL_LEN,A.COIL_INDIA,A.COIL_OUTDIA
           ,A.CRK_CD1,A.CRK_CD2,A.CRK_CD3,A.CRK_CD4,A.CRK_CD5
           ,A.INSTR_COIL_THK
           ,A.INSTR_COIL_WTH  
           ,A.ACT_WGT
           ,A.ORD_FL
      
           ,A.ORD_NO
           ,A.ORD_SEQ

           ,A.TOT_DEC_GRD
           ,A.INGR_DEC_GRD
           ,A.MATLQLTY_DEC_GRD
           ,A.SIZE_DEC_RST
           ,A.EXTSHAPE_DEC_GRD
           ,A.WGT_DEC_RST

           ,A.REM_YARD
           ,A.MILL_DTIME 
           ,A.DCS_THK 
           ,A.DCS_WTH 

FROM TBH02_COIL_COMM A WHERE   A.OLD_SAMPL_NO LIKE :3||'%'
            AND    NVL(A.APPOINT_YN,'N') LIKE 'N')
            
WHERE T.OLD_SAMPL_NO LIKE :4||'%'
		]]>
	</query>
	
	
	<query id="UIK010010_02.INITSELECT" desc="查询所有钢种组中的牌号" fetchSize="10">
        <![CDATA[
		SELECT     DISTINCT
                   FAC_STL_GRD     LABEL
                  ,FAC_STL_GRD     VALUE
         FROM      TBD00_STLGRD
         UNION     ALL
         SELECT    'ALL'   LABEL
                   ,''     VALUE
         FROM      DUAL
         ORDER BY  VALUE DESC
        ]]>
    </query>
	
</queryMap>