| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148 |
- <?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 -- 品质设计确认时间
- , 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('A01012', B.DLIV_TP) DLIV_TP --运输方式
- , XB_PACKAGE.GF_COMNNAME_FIND('A01001', A.RCVORD_CLF) RCVORD_CLF -- 订货区分
- , XB_PACKAGE.GF_COMNNAME_FIND('B01001', C.SPEC_ABBSYM) PROD_NM --产品分类
- , XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.PRD_TY) PRODNM_CD -- 产品代码
- , A.STL_GRD -- 厂内牌号
- , B.SPEC_STL_GRD -- 成品牌号
- , A.SPEC_ABBSYM -- 标准号
- , N.SLAB_CUT_DTIME -- 炼钢时间
- , O.E_SLAB_REP_DTIME -- 板坯充当最小时间
- , P.E_COIL_REP_DTIME -- 钢卷充当最小时间
- , Q.SLAB_STOCK_ENT_DTIME -- 热轧库入库时间
- , R.EXTRACT_DTIME -- 轧制日期(加热炉出炉时间)
- , A.PROD_END_DATE --生产完成时间
- , 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 -- 交货日期（到达）
- , PKG_QUALITY_COMM.FID070010(A.ORD_NO , A.ORD_SEQ) SUM_WGT
- , 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 -- 剩余发货量
- , DECODE(A.PROD_END_GP,'4','',A.SHIP_END_DATE) SHIP_END_DATE --TL 091229 取出新添加的发货结束时间
- , 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>
|
