TO_DATE(A.DEL_TO_DATE, 'yyyymmdd') THEN DECODE(A.PROD_END_WGT + A.ORD_PRC_WGT, 0, '', A.PROD_END_WGT + A.ORD_PRC_WGT) END OVER_DATE_WGT, */ /* CASE WHEN A.SHIP_END_WGT > M.WGT_IN_DEL THEN TO_CHAR(A.SHIP_END_WGT-M.WGT_IN_DEL) ELSE '' END OVER_DATE_WGT, 王黎注释(M.WGT_IN_DEL即超交货期的量 无需相减)*/ M.WGT_IN_DEL OVER_DATE_WGT, --超交货期量 CASE WHEN B.DLIV_TP = 'T' THEN DECODE(A.SHIP_END_WGT, 0, '', A.SHIP_END_WGT) END CALZADA_WGT, --火车交货 CASE WHEN B.DLIV_TP = 'C' THEN DECODE(A.SHIP_END_WGT, 0, '', A.SHIP_END_WGT) END TRAIN_WGT, --汽车交货 DECODE(A.ORD_PROG_CD,'G',A.SHIP_END_DATE,A.PROD_END_DATE) PROD_END_DATE --TL 091229 取出新添加的发货结束时间 --屏蔽原来取的生产结束时间 DECODE(A.PROD_END_GP, '1' ,A.PROD_END_DATE,'') PROD_END_DATE --发货结束时间 ------------------------------------------------------------------------------------------------- ,B.MK_COIL ,B.XSQY --销售区域 ,C_PKG_ORD_REP.FUN_PING_ZHENG(B.ORD_NO , B.ORD_SEQ) YN_PING_ZHENG --是否平整 FROM TBE02_ORD_PRC A, TBA01_ORD_LINE B -- 订单进程表 ,TBZ00_CUSTOMER 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 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 A.ORD_NO LIKE ?||'%' -- 参数：合同号 AND A.ORD_SEQ LIKE ?||'%' -- 参数：订单号 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 INSTR(NVL(? , A.PRD_TY) , A.PRD_TY) > 0 -- 参数：产品代码 AND (? IS NULL OR A.ORD_PROG_CD IN ( -- 参数：订单进度代码 SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(?) AS TABSTR_T)))) AND A.ABNOR_ORD_CD||'&' LIKE ?||'%' -- 参数：异常订单类型代码 -- AND A.CUST_CD||'&' LIKE ?||'%' -- 参数：客户代码 AND C.CUST_NM LIKE '%'||?||'%' --参数：客户名称 AND A.STL_GRD||'&' LIKE '%'||?||'%' -- 参数：钢号 AND A.DEL_TO_DATE BETWEEN ? AND ? -- 参数：交货日期（到达） AND (? IS NULL OR A.RCVORD_CLF IN ( -- 参数：交货区分 SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(?) AS TABSTR_T)))) AND NVL(B.ORD_PLAN_DATA,A.DEL_TO_DATE) BETWEEN NVL(?,'0000000') AND NVL(?,'99999999') AND B.DLIV_TP||'&' LIKE ?||'%' --运输方式，查询条件TL 091209 AND A.ORD_WTH BETWEEN NVL(?,0) AND NVL(?,99999999) AND A.ORD_THK BETWEEN NVL(?,0) AND NVL(?,99999999) AND B.XSQY||'&' LIKE ?||'%' ORDER BY A.ORD_NO, A.ORD_SEQ ]]> B.DEL_TO_DATE GROUP BY (A.ORD_NO, A.ORD_SEQ)) M 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 = T.ORD_NO(+) AND A.ORD_SEQ = T.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 INSTR(NVL(:3 , A.PRD_TY) , A.PRD_TY) > 0 -- 参数：产品类型 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 C.CUST_NM 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 A.ORD_WTH BETWEEN NVL(:16, 0) AND NVL(:17, 99999999) AND A.ORD_THK BETWEEN NVL(:18, 0) AND NVL(:19, 99999999) AND B.XSQY || '&' LIKE :20 || '%' AND A.PROD_LINE||'&' LIKE :21 || '%' -- 参数：产品类型 AND (B.DEST_PCD in (SELECT T.SM_CD FROM tbz00_commcd t WHERE t.lg_cd = 'A01009' and t.sm_cfnm like :22||'%') OR :23 IS NULL) --到站 AND NVL(:24,'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 ]]>