XinYu_RZMES/.svn/pristine/24/246baec4629bff2ccf1ceb752d94f8e3d53ea209.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="MAKE SPM PLAN QUERY">
	<query id="UIH060050.select.SPMMANANO" desc=" 查询平整分卷计划信息明细" fetchSize="10">
		<![CDATA[
            SELECT '' CHK,
		           T1.REG_DTIME,
		           T1.SPM_MANA_NO,
		           T1.SPM_COIL_CNT,
		           T1.SPM_COIL_WGT,
		           T3.NON_EXE_WGT,
		           T3.NON_EXE_CNT,
		           T3.EXECUTED_WGT,
		           T3.EXECUTED_CNT,
		           T1.L2_SEND
		    FROM   TBF05_SPEC_SPM T1  
		           ,(SELECT T2.SPM_MANA_NO,
		           SUM(DECODE(T2.STATUS_CD,'A',T2.COIL_WGT,0)) NON_EXE_WGT,
		           SUM(DECODE(T2.STATUS_CD,'A',1,0)) NON_EXE_CNT,
		           SUM(DECODE(T2.STATUS_CD,'B',T2.COIL_WGT,'C',T2.COIL_WGT,0)) EXECUTED_WGT,
		           SUM(DECODE(T2.STATUS_CD,'B',1,'C',1,0)) EXECUTED_CNT
		    FROM   TBF05_SPEC_MILL T2
		    GROUP  BY T2.SPM_MANA_NO )T3
		    WHERE  T1.SPM_MANA_NO = T3.SPM_MANA_NO
		    AND    T1.STATUS_CD IN ('A','B')   
		    ORDER BY T1.SPM_MANA_NO
		]]>
	</query>
	<query id="UIH060050.select.Planlist" desc=" 查询平整分卷计划信息明细" fetchSize="10">
		<![CDATA[
          SELECT 'Y' CHK,
           		 F.COIL_EDT_SEQ,
          		 B.OLD_SAMPL_NO,
          		 F.STATUS_CD,
                 F.L2_SEND,
		         B.CUR_PROG_CD,
		         F.SPEC_STL_GRD,
		       	 F.COIL_THK,
		         F.COIL_WTH,
		         F.COIL_LEN,
		         F.COIL_WGT,
		         (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = C.SPM_CRK_CD1) SPM_CRK_CD1 , --钢卷外观缺陷
		       (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = C.SPM_CRK_CD2) SPM_CRK_CD2 , --钢卷外观缺陷
		       (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = C.SPM_CRK_CD3) SPM_CRK_CD3 , --钢卷外观缺陷
		        (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = C.SPM_CRK_CD4) SPM_CRK_CD4 , --钢卷外观缺陷
		       (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = C.SPM_CRK_CD5) SPM_CRK_CD5 , --钢卷外观缺陷 
		        C.SPM_REMARK, --缺陷备注   
		         (SELECT T.SM_CFNM FROM TBZ00_COMMCD T 
				 WHERE T.LG_CD = 'B02002' 
				 AND T.SM_CD = F.SPM_ORDER) SPM_PLAN,  --平整顺序号
		         C.SPM_REMARK,
		         A.ORD_NO,
			     A.ORD_SEQ,
			     A.STL_GRD,
			     A.ORD_THK,
			     A.ORD_WTH,
			     A.ORD_LEN,
			     A.ORD_UNIT_WGT,
			     A.ORD_UNIT_WGT_MIN,
			     A.ORD_UNIT_WGT_MAX,
			     A.DEL_TO_DATE,
			     D.CUST_NM ORDCUST_CD,
			     B.CUR_LOAD_LOC,
       			 B.ACT_WGT,
			     E.SM_CFNM DEST_CD 
            FROM TBE02_ORD_PRC A,
       			 TBF05_SPEC_MILL F,
       			 TBB02_SPM_PLAN C,
       			 TBZ00_CUSTOMER D,
       			 TBZ00_COMMCD E,
       			 TBH02_COIL_COMM B  
           WHERE B.OLD_SAMPL_NO=F.COIL_NO 
   			 AND F.SPM_PLAN_SEQ=C.SPM_PLAN_SEQ 
   			 AND B.ORD_NO=A.ORD_NO
   			 AND B.ORD_SEQ=A.ORD_SEQ
   			 AND A.ORDCUST_CD=D.CUST_CD 
   			 AND A.DEST_CD=E.SM_CD(+) 
   			 AND F.SPM_MANA_NO LIKE :1||'%'   
   			 AND F.STATUS_CD IN ('A','B','C')
   			 AND B.COIL_STAT = '2'
   			 ORDER BY B.OLD_SAMPL_NO
		]]>
	</query>	
	<query id="UIH060050.delete.SPMPlan" desc="删除平整分卷计划" fetchSize="10">
		<![CDATA[
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_DEL(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060050.send.SPMPlan" desc="下发平整分卷计划" fetchSize="10">
		<![CDATA[
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_SND(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060050.unsend.SPMPlan" desc="取消下发平整分卷计划" fetchSize="10">
		<![CDATA[
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_USD(?,?,?,?)}
		]]>
	</query>
</queryMap>