XinYu_LZMES/.svn/pristine/59/59150a7152a44fa49d8bace86ecabaf045c4e999.svn-base

<?xml version="1.0" encoding='UTF-8'?>

<queryMap desc="">
    <query id="UIK080012.OrdPrc.Select" desc="it's query from TBE02_ORDSTS Table" fetchSize="10">
        <![CDATA[
			    SELECT         A.ORD_NO, -- 合同号  
			           'false' CHK,                                                                
		               A.ORD_SEQ, -- 订单号     
		               XB_PACKAGE.GF_CUSTOMER_FIND(A.CUST_CD) CUST_CD, -- 客户代码                                                                    
		               XB_PACKAGE.GF_COMNNAME_FIND('E01001', A.ORD_PROG_CD) ORD_PROG_CD, -- 订单进度代码         
		               XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.PRD_TY) PRODNM_CD, -- 交货状态 
		               
		               A.H_SPEC_STL_GRD,                                                                         
		               A.C_SPEC_STL_GRD,                                                   --冷轧牌号            
		               A.C_SPEC_ABBSYM,                                              -- 冷轧标准号 
		               --A.ORD_PRC_LOCK,
		                                 
		               B.MK_CONTENT, --订单备注    
		               B.ORD_PLAN_DATA, --订单预排期  
		               A.MILL_DUE_DATE, --热轧交货期
		               A.DEL_TO_DATE, -- 交货日期（到达）   
		               
		               A.C_ORD_THK,
		               A.C_ORD_WTH,
		               A.C_ORD_LEN,
		               A.C_ORD_THK_MIN,
		               A.C_ORD_THK_MAX,
		               
		               A.ORD_THK, -- 订单厚度                                                                    
		               A.ORD_WTH, -- 订单宽度 
		               
		               A.ORD_UNIT_WGT_MIN, -- 订单单重量下限                                                     
		               A.ORD_UNIT_WGT_MAX, -- 订单单重量上限
		               
		               A.ORD_WGT, -- 订单重量                                                                    
		               A.WGT_ACVAL_MIN, -- 交付允许误差最小                                                      
		               A.WGT_ACVAL_MAX, -- 交付允许误差最大
		                           
		               --PKG_QUALITY_COMM.FID070010(A.ORD_NO, A.ORD_SEQ) SUM_WGT,  --生产量  
		               nvl(a.ORD_PRC_WGT,0) + nvl(a.PROD_END_WGT,0) + nvl(a.SHIP_END_WGT,0) SUM_WGT,--生产量
		               A.HOT_ROLL_END_WGT,                                       --热轧转运量                              
		               DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT) ORD_REM_WGT, -- 订单未生产量       
		               case when DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT)  >= 0 
                            then DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT) 
                            when DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT) < 0 then 0
                       end  ORD_REM_WGT2,    
		               
		               (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD IN ('JB', 'JR', 'JC', 'JL')) LG, -- 炼钢                                  
		               (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'JJ') LZ, -- 连铸                                                       
		               (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD IN ('RA', 'RB', 'RC')) ZG, -- 轧钢 
		               /*(SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'SC') JZ,*/ -- 精整   
		                   (SELECT SUM(WRK_WGT)
						          FROM TBE02_ORD_PRC_DET
						         WHERE ORD_NO = A.ORD_NO
						           AND ORD_SEQ = A.ORD_SEQ
						           AND PROC_CD = 'PA') JZ,
		                (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD IN ('SB', 'SD', 'SE')) RZZH, -- 综合判定 
		                (SELECT SUM(TOT_WGT)                                                                      
	                      FROM TBE02_ORD_PRC_DET                                                                 
	                     WHERE ORD_NO = A.ORD_NO                                                                 
	                       AND ORD_SEQ = A.ORD_SEQ                                                               
	                       AND PROC_CD IN ('DE')) LZZH, -- 冷轧综合判定      
		                (SELECT SUM(INS_WGT + WRK_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD IN ('SG','SF')) RZYS, -- 热轧移送   
		                --(SELECT SUM(INS_WGT + WRK_WGT)                                                                      
	                    --  FROM TBE02_ORD_PRC_DET                                                                 
	                    -- WHERE ORD_NO = A.ORD_NO                                                                 
	                    --   AND ORD_SEQ = A.ORD_SEQ                                                               
	                    --   AND PROC_CD IN ('SG','SF')) RZYS, -- 热轧移送      
		                (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'PC') SZ, -- 酸轧 
		                   (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'CC') LT, -- 连退
		                (SELECT SUM(TOT_WGT)                                                                      
	                      FROM TBE02_ORD_PRC_DET                                                                 
	                     WHERE ORD_NO = A.ORD_NO                                                                 
	                       AND ORD_SEQ = A.ORD_SEQ                                                               
	                       AND PROC_CD = 'DB') CJ, --  重卷   
		                (SELECT SUM(TOT_WGT)                                                                      
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'DC') BZ, -- 包装     
		                (SELECT SUM(INS_WGT + WRK_WGT)                                                            
		                  FROM TBE02_ORD_PRC_DET                                                                 
		                 WHERE ORD_NO = A.ORD_NO                                                                 
		                   AND ORD_SEQ = A.ORD_SEQ                                                               
		                   AND PROC_CD = 'DF') DD, -- 发货等待
		                 A.SHIP_END_WGT, -- 发货完重量                                                             
		                                                                          
		               A.SMS_DUE_DATE,   --炼钢完成期限                                                          
		               A.MILL_DUE_DATE,  --热轧完成期限                                                          
		               A.PLTCM_DUE_DATE, --酸轧完成期限                                                          
		               A.CAL_DUE_DATE,   --连退完成期限                                                          
		               A.PROD_END_DATE, --生产结束时间    
		               A.SHIP_END_DATE, --发货结束时间                                                 
		               XB_PACKAGE.GF_COMNNAME_FIND('A01012', B.DLIV_TP) DLIV_TP,--运输方式                                                      
		               B.XSQY, --销售区域    
		               CASE
                                        WHEN A.CUST_SPEC_NO_INGR IS NOT NULL OR A.CUST_SPEC_NO_QLTY IS NOT NULL OR A.CUST_SPEC_NO_DEL IS NOT NULL
                                        THEN 'Y'
                                        WHEN A.CUST_SPEC_NO_INGR IS  NULL AND A.CUST_SPEC_NO_QLTY IS  NULL AND A.CUST_SPEC_NO_DEL IS  NULL
                                        THEN 'N'
                       END INGR_YN, --特殊要求                                                                
		               XB_PACKAGE.GF_COMNNAME_FIND('B01004', A.PROD_LINE) PROD_LINE -- 产线              
		               ,(SELECT TO_CHAR(wmsys.wm_concat(XB_PACKAGE.GF_COMNNAME_FIND('E04002',COLUMN_VALUE))) 
                 			FROM TABLE(XB_PACKAGE.GF_TABSTR(A.ORD_PRC_LOCK,'|'))) ORD_PRC_LOCK                                                            
			    FROM TBE02_ORD_PRC A,                                                                          
			         TBA01_ORD_LINE B -- 订单进程表                                                            
			        ,                                                                                          
			         TBZ00_CUSTOMER C --客户公共表                                                             
			        ,                                                                                          
			                                                                        
			         (SELECT A.ORD_NO, A.ORD_SEQ, SUM(A.ACT_WGT) WGT_IN_DEL                                    
			            FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B                                                
			           WHERE A.ORD_NO = B.ORD_NO                                                               
			             AND A.ORD_SEQ = B.ORD_SEQ                                                             
			             AND A.ORD_FL = '1'                                                                    
			             AND A.TRNF_DTIME > B.DEL_TO_DATE                                                      
			           GROUP BY (A.ORD_NO, A.ORD_SEQ)) M                                                       
			   WHERE '1' = '1'                                                                                 
			     AND A.ORD_NO = B.ORD_NO(+)                                                                    
			     AND A.ORD_SEQ = B.ORD_SEQ(+)                                                                  
			     --AND A.ORD_NO = K.ORD_NO(+)                                                                    
			     --AND A.ORD_SEQ = K.ORD_SEQ(+)                                                                  
			     AND A.ORD_NO = M.ORD_NO(+)                                                                    
			     AND A.ORD_SEQ = M.ORD_SEQ(+)                                                                  
			     AND A.CUST_CD = C.CUST_CD(+)   
			     AND A.PROD_LINE = 'C'                                                                     
			     and A.ORD_NO LIKE :1||'%' -- 参数：合同号                                                     
			    AND A.ORD_SEQ LIKE :2 ||'%' -- 参数：订单号       
			    -- and A.ORD_NO='1' -- 参数：合同号                                                     
			   -- AND A.ORD_SEQ='2' -- 参数：订单号                                                                                     
			     AND A.DEL_TO_DATE BETWEEN :3 AND :4 -- 参数：交货日期（到达）            
			     AND NVL(:5,'NULL') IN (SELECT *
                 FROM TABLE(XB_PACKAGE.GF_TABSTR(A.ORD_PRC_LOCK,'|'))
                 union all
                 select 'NULL' from dual
                 )                   
			     ORDER BY A.ORD_NO, A.ORD_SEQ                                                                    
						
        ]]>
    </query>
    <query id="UIK080012_shift.Update"  desc="无时间查询"> 
		<![CDATA[	
	        UPDATE TBE04_OPT_HIS
			SET REG_ID = :1
			    ,REG_DATE = :2
			    ,REG_SHIFT = :3
			    ,REG_GROUP = :4
			WHERE REG_ID = :1 
		 ]]>
	</query>
	<query id="UIK080012_SHIFT.insert"  desc="无时间查询"> 
		<![CDATA[	
	        INSERT INTO TBE04_OPT_HIS T
					  (SEQ_ID,
					   OPT_TYP,
					   REG_ID,
					   REG_PGM_ID,
					   REG_DATE,
					   REG_SHIFT,
					   REG_GROUP,
					   RESERVE_FIELD1,
					   RESERVE_FIELD11,
					   RESERVE_FIELD2,
					   RESERVE_FIELD3)
					VALUES
					  (TBE04_OPT_HIS_SEQ.NEXTVAL,
					   '工序锁定',
					   :1,
					   'UIK080012',
					   TO_CHAR(SYSDATE, 'YYYYMMDD'),
					   :2,
					   :3,
					   :4,
					   TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
					   :5,
					   :6				   
					   )
		 ]]>
	</query>
</queryMap>