XinYu_RZMES/.svn/pristine/bd/bd8a17a563ddb6603929ba635cc24ab61a71ffb6.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="合同完成情况汇总">

	<query id="UID070010.OrdPrc.Select" desc="合同完成情况汇总" fetchSize="10">
		<![CDATA[
	     
          SELECT A.ORD_NO                                         -- 合同号
               , A.ORD_SEQ                                        -- 订单号
			   , B.XSQY                                           -- 销售区域 
			   , C.REG_DTIME                                      -- 品质设计确认时间
			   , O.E_SLAB_REP_DTIME                               -- 板坯充当最小时间
			   , P.E_COIL_REP_DTIME                               -- 钢卷充当最小时间
			   , Q.SLAB_STOCK_ENT_DTIME                           -- 热轧库入库时间
			   , R.EXTRACT_DTIME                                  -- 轧制日期(加热炉出炉时间)
			 
               , XB_PACKAGE.GF_COMNNAME_FIND('E01001', A.ORD_PROG_CD) ORD_PROG_CD        -- 订单进度代码
               , XB_PACKAGE.GF_CUSTOMER_FIND(A.CUST_CD) CUST_CD                          -- 客户代码
               , XB_PACKAGE.GF_COMNNAME_FIND('A01001', A.RCVORD_CLF)  RCVORD_CLF         -- 订货区分
               , XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.PRD_TY)   PRODNM_CD          -- 产品代码
		   	   , XB_PACKAGE.GF_COMNNAME_FIND('B01001', C.SPEC_ABBSYM)   PROD_NM          --产品分类
			   , PKG_QUALITY_COMM.FID070010(A.ORD_NO , A.ORD_SEQ)     SUM_WGT
               , A.STL_GRD                                         -- 厂内牌号
               , B.SPEC_STL_GRD                                   -- 成品牌号
               , A.SPEC_ABBSYM                                    -- 标准号
			   , N.SLAB_CUT_DTIME                                -- 炼钢时间
               , NVL(A.KEEPWARM_YN, 'N') KEEPWARM_YN              -- 是否需要进保温坑
               , A.ORD_THK                                        -- 订单厚度
               , A.ORD_WTH                                        -- 订单宽度
               , A.ORD_LEN                                        -- 订单长度
               , A.ORD_UNIT_WGT                                   -- 订单单重量
               , A.ORD_UNIT_WGT_MIN                               -- 订单单重量下限
               , A.ORD_UNIT_WGT_MAX                               -- 订单单重量上限
               , B.ORD_PLAN_DATA                                  -- 订单预排期
               , A.DEL_TO_DATE                                    -- 交货日期（到达）
               , A.ORD_WGT                                        -- 订单重量
               , A.WGT_ACVAL_MIN                                  -- 交付允许误差最小
               , A.WGT_ACVAL_MAX                                  -- 交付允许误差最大
               , DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT) ORD_REM_WGT                -- 订单未生产量
			   
               , (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 = 'SF') DD                          -- 发货等待
               , (A.SHIP_END_WGT - DECODE(M.WGT_IN_DEL,'',0,M.WGT_IN_DEL)) SHIP_END_WGT        -- 发货完重量(交货期内)
			   , M.WGT_IN_DEL  OVER_DATE_WGT                       -- 发货完重量(交货期外)
               , DECODE(A.ORD_PROG_CD, 'G', 0,
                    (A.ORD_WGT - A.SHIP_END_WGT)) SHIP_REM_WGT    -- 剩余发货量
               , XB_PACKAGE.GF_COMNNAME_FIND('A01012', B.DLIV_TP) DLIV_TP    --运输方式
               
               , DECODE(A.PROD_END_GP,'4','',A.SHIP_END_DATE) SHIP_END_DATE --TL 091229 取出新添加的发货结束时间
			   , A.PROD_END_DATE --生产完成时间
			   , DECODE(M.DEL_TO_DATE_YN, '是' ,'是','否') DEL_TO_DATE_YN   --是否超交货期   
			
            FROM TBE02_ORD_PRC A             -- 订单进程表
			   , TBA01_ORD_LINE B    
			   , TBB01_ORD_HEAD C            
               , (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
                    FROM TBB01_ORD_INGR
                   WHERE DSN_KIND = '1') K
                   
               , (
               	----------------------------------------------------------------------------------------------------------
               	 /*SELECT A.ORD_NO, A.ORD_SEQ, SUM(A.ACT_WGT)   WGT_IN_DEL, '是' AS DEL_TO_DATE_YN
  					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 (SUBSTR(A.TRNF_DTIME,1,8) > B.DEL_TO_DATE OR (SYSDATE > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') AND B.ORD_PROG_CD IN ('D', 'E', 'F')))
			    GROUP BY (A.ORD_NO, A.ORD_SEQ, B.ORD_PROG_CD)*/
			    ---desc:钢卷公共表没数据时 超交货期不能正确显示-----------------------------------------------
			    ---MOD: WXY 2010-07-14----------------------------------
			    SELECT b.ORD_NO, b.ORD_SEQ, SUM(A.ACT_WGT)   WGT_IN_DEL, 
			    --'是' AS DEL_TO_DATE_YN
			    CASE  WHEN B.SHIP_END_DATE IS NULL AND SYSDATE  >  TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') THEN   '是'
         		      WHEN TO_DATE(B.SHIP_END_DATE, 'YYYYMMDD') > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD')  THEN   '是'
                      ELSE     '否'     END DEL_TO_DATE_YN  
		            FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B
		           WHERE A.ORD_NO(+) = B.ORD_NO
		           AND A.ORD_SEQ(+) = B.ORD_SEQ
		           AND nvl(A.ORD_FL,'1') = '1'
		           AND (SUBSTR(A.TRNF_DTIME,1,8) > B.DEL_TO_DATE OR (SYSDATE > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') AND B.ORD_PROG_CD IN ('D', 'E', 'F' , 'G')))
		           --and          a.SHIP_PROG_CD = '08'
               	   --and          a.CUR_PROG_CD =  'SFF'
                   --AND          A.COIL_STAT = '3'
		           GROUP BY (b.ORD_NO, b.ORD_SEQ , B.ORD_PROG_CD , B.SHIP_END_DATE ,B.DEL_TO_DATE )
			    -------------------------------------------------------------------------------------------------------------------
			    ) M       -- 超交货期量表
			  , (SELECT A.ORD_NO,A.ORD_SEQ,MIN(A.SLAB_CUT_DTIME) SLAB_CUT_DTIME
			             FROM TBG02_SLAB_COMM A 
			             GROUP BY (A.ORD_NO,A.ORD_SEQ)) N        --炼钢时间(准确的说是该订单第一块板坯的切割时间)
			,(SELECT T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ,MIN(T.REG_DTIME) E_SLAB_REP_DTIME 
			         FROM TBE04_REP_HIS T 
					 WHERE T.REP_TYP = 'A' --(余才到订单才)
			           AND T.MAT_TYPE = 'TM'  --板坯
				     GROUP BY (T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ)) O   --板坯充当最小时间
			,(SELECT T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ,MIN(T.REG_DTIME) E_COIL_REP_DTIME 
			         FROM TBE04_REP_HIS T 
					 WHERE T.REP_TYP = 'A' --(余才到订单才)
			           AND T.MAT_TYPE = 'AR'  --钢卷
				     GROUP BY (T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ)) P   --钢卷充当最小时间
			,(SELECT T.ORD_NO,T.ORD_SEQ, MIN(T.SLAB_STOCK_ENT_DTIME) SLAB_STOCK_ENT_DTIME 
					 FROM TBG02_SLAB_COMM T 
					 GROUP BY (T.ORD_NO,T.ORD_SEQ)) Q                --  热轧库入库时间     
			,(SELECT A.ORD_NO,A.ORD_SEQ, MIN(B.EXTRACT_DTIME) EXTRACT_DTIME
					 FROM TBG02_SLAB_COMM A, TBH02_REHEATFUR B 
					 WHERE A.SLAB_NO = B.SLAB_NO
					 GROUP BY (A.ORD_NO,A.ORD_SEQ)) R              --轧制日期(加热炉出炉时间)
			
			WHERE A.ORD_NO LIKE :1||'%'                            -- 参数：合同号
              AND A.ORD_SEQ LIKE :2||'%'                            -- 参数：订单号
              AND A.ORD_NO = B.ORD_NO(+)
              AND A.ORD_SEQ = B.ORD_SEQ(+)
			  AND A.ORD_NO = C.ORD_NO(+)
              AND A.ORD_SEQ = C.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.ORD_NO = N.ORD_NO(+)
              AND A.ORD_SEQ = N.ORD_SEQ(+)
			  AND A.ORD_NO = O.UPD_AFT_ORD_NO(+)
              AND A.ORD_SEQ = O.UPD_AFT_ORD_SEQ(+)
			  AND A.ORD_NO = P.UPD_AFT_ORD_NO(+)
              AND A.ORD_SEQ = P.UPD_AFT_ORD_SEQ(+)
			  AND A.ORD_NO = Q.ORD_NO(+)
              AND A.ORD_SEQ = Q.ORD_SEQ(+)
			  AND A.ORD_NO = R.ORD_NO(+)
              AND A.ORD_SEQ = R.ORD_SEQ(+)
			
              AND A.PRODNM_CD LIKE :3||'%'                        -- 参数：产品代码
              AND (:4 IS NULL OR A.ORD_PROG_CD IN (                -- 参数：订单进度代码
                    SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:5) AS TABSTR_T))))
              AND A.ABNOR_ORD_CD||'&' LIKE :6||'%'                -- 参数：异常订单类型代码
              AND A.CUST_CD||'&' LIKE :7||'%'                    -- 参数：客户代码
              AND A.STL_GRD||'&' LIKE :8||'%'                    -- 参数：厂内牌号
              AND A.DEL_TO_DATE BETWEEN :9 AND :10                    -- 参数：交货日期（到达）
              AND (:11 IS NULL OR A.RCVORD_CLF IN (                -- 参数：交货区分
                      SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:12) AS TABSTR_T))))
              AND NVL(B.ORD_PLAN_DATA,A.DEL_TO_DATE) BETWEEN NVL(:13,'0000000') AND NVL(:14,'99999999')
              AND B.DLIV_TP||'&' LIKE :15||'%'                     --运输方式，查询条件TL 091209
			  AND B.SPEC_STL_GRD||'&' LIKE :16||'%'
			  AND A.KEEPWARM_YN||'&' LIKE :17||'%'
			  AND A.ORD_THK BETWEEN NVL(:18,0) AND NVL(:19,99999999)
			  AND A.ORD_WTH BETWEEN NVL(:20,0) AND NVL(:21,99999999)
			  AND C.REG_DTIME BETWEEN :22||'000000' AND :23||'235959'
			  AND DECODE(M.DEL_TO_DATE_YN, '是' ,'是','否')||'&' LIKE :24||'%'
			  AND INSTR ( DECODE(:25 , NULL , A.ORD_PROG_CD , :26) ,  A.ORD_PROG_CD) >0
			  AND C.SPEC_ABBSYM LIKE         :27||'%'
            ORDER BY A.ORD_NO, A.ORD_SEQ
		]]>
	</query> 
</queryMap>