XinYu_RZMES/.svn/pristine/38/38e891345d4b2b17491dd8360bb6863eca8afed9.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="MAKE SPM PLAN QUERY">
	<query id="UIH060030.select.OrdSEQ" desc=" 查询对应订单的订单序号" fetchSize="10">
		<![CDATA[
         SELECT A.ORD_SEQ  					   --订单序号
           FROM TBE02_ORD_PRC A  
          WHERE A.ORD_NO LIKE :1||'%'
		]]>
	</query>
	<query id="UIH060030.select.Coil" desc=" 查询平整分卷原料卷 " fetchSize="10">
		<![CDATA[
         SELECT 'N' CHK,						--选择
         		C.SPM_PLAN_SEQ,          		--平整顺序号
         		B.MILL_DTIME,                	--轧制时间
         		B.OLD_SAMPL_NO,					--钢卷号
        		B.CUR_LOAD_LOC,					--堆放位置
        		B.SPEC_STL_GRD,					--钢卷牌号
        		B.COIL_THK,						--钢卷厚度
        		B.COIL_WTH,						--钢卷宽度
        		B.COIL_LEN,						--钢卷长度
        		B.ACT_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 = C.SPM_PLAN) SPM_PLAN,  --平整顺序号
				C.SPM_REMARK,					--钢卷平整分卷说明
				c.REG_ID,c.REG_DTIME,
				B.SPM_YN,            			--SMP是否作业
				Z.MK_CONTENT,
				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,			--订单收货单位
				E.SM_CFNM DEST_CD 				--订单到站
           FROM TBH02_COIL_COMM B,
           	    TBE02_ORD_PRC A,
           	    TBB02_SPM_PLAN C,
           	    TBZ00_CUSTOMER D,
           	    TBZ00_COMMCD E  ,
           	    TBA01_ORD_LINE Z
          WHERE B.OLD_SAMPL_NO=C.COIL_NO 
            AND B.ORD_NO=A.ORD_NO 
            AND B.ORD_SEQ=A.ORD_SEQ 
            AND B.ORD_NO=Z.ORD_NO 
            AND B.ORD_SEQ=Z.ORD_SEQ 
            AND C.SPM_STATUS='A' 
            AND A.ORDCUST_CD=D.CUST_CD 
            AND A.DEST_CD=E.SM_CD(+)
            AND B.COIL_STAT = '2'
            AND NVL (B.FB_YN, 'N') = 'N' --封闭卷无法制定平整计划 20200720
            --AND A.ORD_NO||'&' LIKE :1||'%'
            --AND A.ORD_SEQ||'&' LIKE :2||'%'
            --AND nvl(A.ORD_THK,0) BETWEEN :3 AND :4
            --AND nvl(A.ORD_WTH,0) BETWEEN :5 AND :6
            --AND B.CUR_LOAD_LOC||'&' LIKE :7||'%'
            AND A.ORD_NO LIKE :1||'%'
            AND A.ORD_SEQ LIKE :2||'%'
            AND A.ORD_THK BETWEEN :3 AND :4
            AND A.ORD_WTH BETWEEN :5 AND :6
            AND NVL(B.CUR_LOAD_LOC,'1') LIKE :7||'%'
            ORDER BY B.OLD_SAMPL_NO
		]]>
	</query>
	<query id="UIH060030.select.Load" desc=" 查询热轧成品的堆放位置基本信息" fetchSize="10">
		<![CDATA[
         SELECT DISTINCT YARD_CLF FROM TBH05_COIL_YARD ORDER BY YARD_CLF
		]]>
	</query>
	<query id="UIH060030.select.PlanInfo" desc=" 查询平整分卷计划信息汇总" fetchSize="10">
		<![CDATA[
         SELECT 'SPM计划名称' TYPE_,
         		MAX(SPM_MANA_NO) SPMINFO 
           FROM TBF05_SPEC_SPM 
          WHERE FL='0'  
          UNION 
         SELECT '个数(个)' TYPE_,
         		TO_CHAR(SUM(SPM_COIL_CNT)) SPMINFO 
           FROM TBF05_SPEC_SPM
          WHERE FL='0'
          UNION 
         SELECT '重量(公斤)' TYPE_,
         		TO_CHAR(SUM(SPM_COIL_WGT)) SPMINFO 
           FROM TBF05_SPEC_SPM
          WHERE FL='0'
		]]>
	</query>
	<query id="UIH060030.select.Planlist" desc=" 查询平整分卷计划信息明细" fetchSize="10">
		<![CDATA[
          SELECT B.OLD_SAMPL_NO,
          		 '' CHK ,
          		 F.COIL_EDT_SEQ,
		         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,  --平整顺序号
				 B.SPM_YN ,           --SMP是否作业
		         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.FL='0'   
		]]>
	</query>	
	<query id="UIH060030.insert.SPMPlan" desc=" 新增平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call pkg_spm_uih.c_prd_spm_plan_add(?,?,?)}
		]]>
	</query>
	<query id="UIH060030.DFLTBH.SPMPlan" desc=" 删除平整分卷指示" fetchSize="10">
		<![CDATA[
         	
         	{call pkg_spm_uih.C_PRD_SPM_PLAN_TBH(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060030.delete.SPMPlan" desc="删除平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_DEL(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060030.cancle.SPMPlan" desc="删除平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_CAN(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060030.affirm.SPMPlan" desc="确认平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_AFM(?,?)}
		]]>
	</query>
	<query id="UIH060030.send.SPMPlan" desc="下发平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_SND(?,?,?,?)}
		]]>
	</query>
	<query id="UIH060030.unsend.SPMPlan" desc="取消下发平整分卷计划" fetchSize="10">
		<![CDATA[
         	
         	{call PKG_SPM_UIH.C_PRD_SPM_PLAN_USD(?,?,?,?)}
		]]>
	</query>
</queryMap>