XinYu_RZMES/.svn/pristine/0e/0e101bc8da45684661aa75df537a6928110a19ee.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="订单预排期确认">
	<query id="UID060010.findPlanDate" desc="查询等待预排期的订单">
        <![CDATA[
	       SELECT 'N' AS CHK,
	           C.CUST_NM,
	           A.ORD_NO,
	           A.ORD_SEQ,
	           D.SM_CFNM,
	           A.PROD_LINE,
               A.TRTMTH_TP,  --交货状态
	           A.ORD_PLAN_DATA,
	           A.ORD_DEVLMT_DATE,
	           A.H_ORD_DEVLMT_DATE,
	           A.SPEC_STL_GRD,
	           A.ORD_WGT,
	           A.PROD_WGT_MIN,
	           A.PROD_WGT_MAX,
	           H_THK_AIM,
	           H_WTH_AIM,
	           A.ORD_THK,
	           A.ORD_WTH,
	           A.ORD_REGBY_CD,
	           A.ORD_REG_DTIME,
	           A.DLIV_TP,
	           A.MK_CONTENT,
	           DECODE(E.DSN_KIND, '1', 'Y', 'N') AS INGR_YN
	           ,A.XSQY
	           ,XB_PACKAGE.GF_COMNNAME_FIND('A01004', B.PRDNM_CD) PRDNM_CD        
	      FROM TBA01_ORD_LINE A,
	         TBA01_ORD_COMM B,
	         TBZ00_CUSTOMER C,
	         (SELECT SM_CD, SM_CFNM, SM_CSNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01001') D,
	         (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
	          FROM TBB01_ORD_INGR
	         WHERE DSN_KIND = '1') E
	         ,(SELECT  MAX(T2.H_THK_AIM) H_THK_AIM , MAX(T2.H_WTH_AIM) H_WTH_AIM,T3.ORD_NO,T3.ORD_SEQ
	          FROM    TBB01_KEY_PROD T1 , TBB01_HCMAP_SIZE T2   , TBA01_ORD_LINE T3
	          WHERE   T1.PRDNM_CD           =   T3.TRTMTH_TP
	          AND     T1.SPEC_ABBSYM        =   T3.SPEC_ABBSYM
	          AND     T1.SPEC_STL_GRD       =   T3.SPEC_STL_GRD
	          AND     T1.ORD_USE_TP         =   T3.ORD_USE_TP
	          AND     T1.THK_MIN            <=  T3.ORD_THK
	          AND     T1.THK_MAX            >=  T3.ORD_THK
	          AND     T1.WTH_MIN            <=  T3.ORD_WTH
	          AND     T1.WTH_MAX            >=  T3.ORD_WTH
	          AND     T1.SIZE_SPEC_CD       =   T2.SIZE_SPEC_CD
	          AND     DECODE(T2.SIZE_CD ,'THK' , T2.C_THK_MIN  , T3.ORD_THK)        <=  T3.ORD_THK
	          AND     DECODE(T2.SIZE_CD ,'THK' , T2.C_THK_MAX  , T3.ORD_THK)        >=  T3.ORD_THK
	          AND     DECODE(T2.SIZE_CD ,'WTH' , T2.C_WTH_MIN  , T3.ORD_WTH)        <=  T3.ORD_WTH
	          AND     DECODE(T2.SIZE_CD ,'WTH' , T2.C_WTH_MAX  , T3.ORD_WTH)        >=  T3.ORD_WTH
	          GROUP BY T3.ORD_NO,T3.ORD_SEQ
	          )F
	       WHERE A.ORD_NO = B.ORD_NO
	       AND A.ORD_NO = E.ORD_NO(+)
	       AND A.ORD_SEQ = E.ORD_SEQ(+)
	       AND B.RCVORD_TP = D.SM_CD(+)
	       AND A.ORD_NO = F.ORD_NO(+)
	       AND A.ORD_SEQ = F.ORD_SEQ(+)
		   AND B.CUST_CD = C.CUST_CD
		   AND B.RCVORD_TP <> 'M'
		   AND A.ORD_SEQ_PROG_CD = '1'   
		   AND B.ORD_NO NOT LIKE '42%'
       	   AND B.ORD_NO NOT LIKE '47%'
		   AND A.ORD_NO LIKE :1 || '%'
		   AND A.ORD_SEQ LIKE :2 || '%'
		   AND NVL(A.ORD_WTH,0) BETWEEN NVL(:3, 0) AND NVL(:4, 99999999)
		   AND (:5 IS NULL or D.SM_CSNM IN(SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:6) AS TABSTR_T))))
		   AND A.SPEC_STL_GRD LIKE '%' || :7 || '%'
		   AND C.CUST_NM LIKE '%' || :8 || '%'
		   AND A.ORD_REG_DTIME BETWEEN NVL(:9, '00000000') AND NVL(:10, '99999999') 
		   AND A.DLIV_TP LIKE NVL(:11,'%')
			AND INSTR(NVL(:12 , B.PRDNM_CD) , B.PRDNM_CD) > 0 
			AND NVL(a.ORD_PLAN_DATA,'00000000') BETWEEN :13 AND :14
			AND  DECODE(A.PROD_LINE , 'L' , 'C' , A.PROD_LINE)LIKE :15||'%'
			AND   A.ORD_THK  LIKE :16||'%'
		ORDER BY  A.ORD_NO,A.ORD_SEQ	
        ]]>
    </query>
	
	<query id="UID060010.savePlanDate.update" desc="保存订单预排期">
        <![CDATA[
              UPDATE TBA01_ORD_LINE 
			  SET ORD_PLAN_DATA = ?
			    , ORD_DEVLMT_DATE = ?
			    , CMT_DLIV_DEVLMT_DATE = ?
			    -------------------------------------add by wxy 091218----------------
			    -------应刘毅斌要求 增加 订单预排期 操作时间
			    , PLAN_ORDT_REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS')
			    -----------------------------------------------------------------------
			    , H_ORD_DEVLMT_DATE = ?
			  WHERE ORD_NO = ?
			  AND ORD_SEQ = ?
        ]]>
    </query>
	
	<query id="UID060010.select.orderwgt" desc="统计进程订单量">
        <![CDATA[
             SELECT (SELECT SUM(A.NEED_WGT)
  FROM TBD01_SLABDN_OBJ A, TBA01_ORD_COMM F
 WHERE A.ORD_NO = F.ORD_NO
   AND A.ORD_NO NOT LIKE '42%'
   AND A.ORD_NO NOT LIKE '47%'
   AND A.ORD_NO NOT LIKE 'TSH%'
   AND F.RCVORD_TP <> 'M') AS NEED_WGT,
       (SELECT SUM(B.PREV_CHARGE_WGT)
          FROM TBF01_SPEC_CHARGE B
         WHERE B.STATUS_CD IN ('A', 'B')) AS CHARGE_WGT,
       (SELECT SUM(C.SLAB_WGT) --库存量
          FROM TBG02_SLAB_COMM C, TBG02_SLAB_COMM_D D
         WHERE C.ORD_FL = '1'
           AND C.SUB_SLAB_NO = D.SLAB_NO
           AND C.ORD_FL = D.ORD_FL
           AND C.SLAB_STAT = '2') AS KC_WGT,
       ((SELECT SUM(A.NEED_WGT)
  FROM TBD01_SLABDN_OBJ A, TBA01_ORD_COMM F
 WHERE A.ORD_NO = F.ORD_NO
   AND A.ORD_NO NOT LIKE '42%'
   AND A.ORD_NO NOT LIKE '47%'
   AND A.ORD_NO NOT LIKE 'TSH%'
   AND F.RCVORD_TP <> 'M') +
       (SELECT SUM(B.PREV_CHARGE_WGT)
           FROM TBF01_SPEC_CHARGE B
          WHERE B.STATUS_CD IN ('A', 'B')) +
       (SELECT SUM(C.SLAB_WGT) --库存量
           FROM TBG02_SLAB_COMM C, TBG02_SLAB_COMM_D D
          WHERE C.ORD_FL = '1'
            AND C.SUB_SLAB_NO = D.SLAB_NO
            AND C.ORD_FL = D.ORD_FL
            AND C.SLAB_STAT = '2')) AS ALLWGT
	  FROM DUAL
        ]]>
    </query>
	
	<query id="UID060010.select.max_ord_plan_data" desc="统计进程订单量">
        <![CDATA[
	    SELECT MAX(ORD_PLAN_DATA) AS MAX_ORD_PLAN_DATA FROM TBA01_ORD_LINE
        ]]>
    </query>
</queryMap>