XinYu_LZMES/.svn/pristine/be/be8290b71299f36b417a7898b64af182aa5cc9cd.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="热轧库存">
	<query id="UIM070010_01.SELECT"  desc="库存查询">
		<![CDATA[
							
					SELECT --'' SEQ,
					      -- '' CHK,
					       A.OLD_SAMPL_NO,
					       XB_PACKAGE.GF_COMNNAME_FIND('A01004', P.PRD_TY) PRD_TY,
					       A.SPEC_STL_GRD,
					       A.COIL_THK,
					       A.COIL_WTH,
					       A.FB_YN,
					       A.FB_REASON,
					       A.INSTR_COIL_THK,
					       A.INSTR_COIL_WTH,
					       A.ACT_WGT
					     -- ,A.COIL_SERIES --系列
					     -- , A.COIL_PROD_GRADE ---产品等级
					      ,A.COIL_UN_REMARK --异常卷备注
					      ,
					       A.COIL_DESC --钢卷说明
					      ,
					       A.COIL_REMARK --备注
					      ,
					     /*  (select t.USER_NM
					          from tbz01_user t
					         where t.USER_CD = A.QK_REG_ID
					           and rownum = 1) QK_REG_ID,
					      A.QK_REG_DTIME,*/
					       DECODE(A.ORD_FL, '1', '订单材', '2', '余材', '', '未判') ORD_FL,
					       DECODE(A.QK_YN, 'Y', '清库卷') QK_YN,
					       A.ORD_NO,
					       A.ORD_SEQ,
					       A.CUR_LOAD_LOC,
					       DECODE(A.TOT_DEC_GRD,
					              '1',
					              '合格',
					              '2',
					              '不合格',
					              '3',
					              '次品',
					              '4',
					              '废品') TOT_DEC_GRD,
					       DECODE(A.INGR_DEC_GRD,
					              '0',
					              '综合判定取消',
					              '1',
					              '合格',
					              '2',
					              '不合格') INGR_DEC_GRD,
					       DECODE(A.MATLQLTY_DEC_GRD,
					              '0',
					              '无性能要求',
					              '1',
					              '合格',
					              '2',
					              '不合格') MATRL_END_CAUSE_CD,
					       DECODE(A.SIZE_DEC_RST, '1', '合格', '2', '不合格') SIZE_DEC_RST,
					       DECODE(A.EXTSHAPE_DEC_GRD,
					              '1',
					              '合格',
					              '2',
					              '订单外合格',
					              '3',
					              '次品',
					              '4',
					              '废品',
					              '待判定') EXTSHAPE_DEC_GRD,
					       DECODE(A.WGT_DEC_RST, '1', '合格', '2', '不合格') WGT_DEC_RST
					       ,G.YC_MEMO
  			 				,G.YC_GSDW
  			 				,ROUND(TO_NUMBER(SYSDATE -
              			   TO_DATE(A.MILL_DTIME, 'YYYY-MM-DD HH24:MI:SS')) * 24) ZKSJ,
					       A.EXTSHAPE_DEC_DTIME,
					       A.TOT_DEC_DTIME,
					       A.YARD_ENTRY_DTIME,
					       A.YARD_ENTRY_USE_TIME,
					       A.YZCS_DTIME,													--预装时间
					       A.YARD_ENTRY_REG,
					       A.YARD_ENTRY_SHIFT,
					       A.YARD_ENTRY_GROUP,
					       SUBSTR(B.SLAB_NO, 0, 10) CHARGE_NO,
					       (SELECT T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'A01012'
					           AND T.SM_CD = C.DLIV_TP) DLIV_TP,
					       (SELECT T.CUST_NM
					          FROM TBZ00_CUSTOMER T
					         WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD,
					        (select T.SM_CFNM from tbz00_commcd t where t.SM_CD=A.CUR_PROG_CD AND ROWNUM=1 )    
					              CUR_PROG_CD,
					     /*  DECODE(A.REM_YARD,
					              '1',
					              '禁发火车',
					              '2',
					              '卷径比超宽',
					              '3',
					              '卷规格异常',
					              '4',
					              '卷外型异常',
					              '5',
					              '禁发') REM_YARD,*/
					     --  A.HCOLOUR_REM,
					       C.ORD_DEVLMT_DATE,
					       t.ROLL_MANA_NO,
					       t.ROLL_SLAB_SEQ,
					     /*  DECODE(A.COIL_DEFECTIVE,
					              '1A',
					              '订单外合格',
					              '2A',
					              '成分不合格',
					              '2E',
					              '工序不合格',
					              '3B',
					              '尺寸不合格',
					              '3C',
					              '重量不合格',
					              '3D',
					              '外观不合格',
					              '4A',
					              '性能不合格') COIL_DEFECTIVE,
					       A.COIL_DEFECTIVE COIL_DEFECTIVE_DESC,*/
					       A.BEF_SPEC_STL_GRD,
					       E.SMP_SEND_DTIME --送样
					      ,
					       E.SMP_WORK_DTIME --检验
					      ,
					    --   E.QLTY_DCS_DTIME --综合判定
					      
					       E.SMP_INTO_DTIME --入库时间   
					      ,
					       to_char(ROUND(((to_date(E.SMP_SEND_DTIME, 'yyyy-mm-dd hh24:mi:ss') -
					                     to_date(A.MILL_DTIME, 'yyyy-mm-dd hh24:mi:ss')) *
					                     86400) / 3600,
					                     1)) GET_SEND_DTIME --取送样时间（送样-轧制）
					      ,
					       to_char(ROUND(((to_date(E.QLTY_DCS_DTIME, 'yyyy-mm-dd hh24:mi:ss') -
					                     to_date(E.SMP_SEND_DTIME, 'yyyy-mm-dd hh24:mi:ss')) *
					                     86400) / 3600,
					                     1)) TO_YANG_DTIME --出样时间（综合判定-送样）
					      ,
					       DECODE(A.BEF_MATLQLTY_DEC_GRD,
					              '0',
					              '无性能要求',
					              '1',
					              '合格',
					              '2',
					              '不合格') BEF_MATRL_END_CAUSE_CD --清库前性能
					      ,
					       DECODE(A.BEF_EXTSHAPE_DEC_GRD,
					              '1',
					              '合格',
					              '2',
					              '订单外合格',
					              '3',
					              '次品',
					              '4',
					              '废品') BEF_EXTSHAPE_DEC_GRD --清库前外观
					      ,
					       A.MILL_DTIME,
					       A.DCS_THK --判定厚度
					      ,
					       A.DCS_WTH --判定宽度
					      ,
					       C.XSQY --销售区域，生产处要求添加 TL091225
					      ,
					       XB_PACKAGE.GF_COMNNAME_FIND('A01001', P.RCVORD_CLF) RCVORD_CLF --订单类型 add by jungan 20110120
					      ,
					       XB_PACKAGE.GF_COMNNAME_FIND('B01004', P.PROD_LINE) PROD_LINE --产线  add by jungan 20110120
					       
					      ,
					       F.AFFIRMTIME || '/' || F.DLIV_TP AFFIRMTIME --下发计划确认时间
					      ,
					       (SELECT TBZ00_COMMCD.SM_CFNM
					          FROM TBZ00_COMMCD
					         WHERE TBZ00_COMMCD.SM_CD = f.DEST_PCD
					           AND ROWNUM = 1) STATIONCODE --到站
					      ,
					       C.ORD_PLAN_DATA --新增订单预排期
					  FROM TBH02_COIL_COMM A,
					       TBG02_SLAB_COMM B,
					       TBA01_ORD_LINE  C,
					       TBA01_ORD_COMM  D,
					       TBB02_LOT_COM   E,
					       TBJ01_DLIV_DIR  F,
					       TBE02_ORD_PRC   P,
					       TBF02_SPEC_MILL t,
					       JB_YC		   G
					 WHERE '1' = '1'
					   AND A.SLAB_NO = B.SLAB_NO(+)
					   AND A.ORD_NO = C.ORD_NO(+)
					   AND A.ORD_SEQ = C.ORD_SEQ(+)
					   AND A.ORD_NO = D.ORD_NO(+)
					   AND A.SAMPL_NO = E.SMP_NO(+)
					   AND A.COIL_NO = t.COIL_NO(+)
					   AND A.OLD_SAMPL_NO = G.OLD_SAMPL_NO(+)
					   AND A.DLIV_DIRNO = F.DLIV_DIRNO(+)
					   AND A.ORD_NO = P.ORD_NO(+)
					   AND A.ORD_SEQ = P.ORD_SEQ(+)
					   AND A.COIL_STAT = '2'
					   AND SUBSTR(NVL(A.YARD_ENTRY_DTIME, 99999999999999), 1, 8) >= NVL(?, 0)
					   AND SUBSTR(NVL(A.YARD_ENTRY_DTIME, 0), 1, 8) <= NVL(?, 99999999999999)
					   AND A.SPEC_STL_GRD || '&' LIKE ? || '%'
					   AND A.COIL_WTH >= NVL(?, '00000')
					   AND A.COIL_WTH <= NVL(?, '99999')
					   AND A.COIL_THK >= NVL(?, '000')
					   AND A.COIL_THK <= NVL(?, '999')
					   AND A.CUR_LOAD_LOC || '&' LIKE ? || '%'
					   AND A.OLD_SAMPL_NO LIKE ? || '%'
					   AND A.ORD_NO || '&' LIKE ? || '%'
					   AND C.ORD_DEVLMT_DATE || '&' LIKE ? || '%'
					   AND A.CUR_PROG_CD || '&' LIKE ? || '%'
					   AND E.SMP_SEND_DTIME || '&' LIKE ? || '%'--送样时间
					   AND a.SPM_YN || '&' like ? || '%'--平整
					   AND C.XSQY || '&' LIKE ? || '%'--销售区域
					   AND A.COIL_DEFECTIVE || '&' LIKE ? || '%'--次品区分 
					   AND C.DLIV_TP || '&' LIKE  ?|| '%'
					   AND P.PROD_LINE || '&' LIKE ? || '%'--产线
					   AND P.RCVORD_CLF || '&' LIKE ? || '%'
					   AND nvl(t.ROLL_MANA_NO, 'A0000') BETWEEN NVL(?, 'A0000') AND
					       NVL(?, 'Z9999')
					   
		]]>
	</query>
	
	<query id="UIM070010_02.SELECT"  desc="库存分类汇总">
		<![CDATA[
			WITH A AS(	select 
				PKG_QUALITY_COMM.PK00_COMM('E01002', t.CUR_PROG_CD) CUR_PROG_CD,
       			count(1) HZSL,
       			sum(t.ACT_WGT) / 1000 HZZL,
				SUM(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'T') then
              1
           end) RZHYSL,
       SUM(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'T') then
              t.ACT_WGT / 1000
           end) RZHYZL,
       SUM(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'C') then
              1
           end) RZQYSL,
       SUM(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'C') then
              t.ACT_WGT / 1000
           end) RZQYZL,
      /* count(case
               when ((substr(t.ORD_NO, 1, 1) = '3' or
                    substr(t.ORD_NO, 1, 2) = 'W3' or
                    substr(t.ORD_NO, 1, 3) = 'WX3' or
                    substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                    d.ORDCUST_CD in ('224902', '243187')) then
                t.ACT_WGT / 1000
             end) XCSL,
       sum(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                  d.ORDCUST_CD in ('224902', '243187')) then
              t.ACT_WGT / 1000
           end) XCZL,*/
        count(case
               when (((substr(t.ORD_NO, 1, 1) = '3' or
                    substr(t.ORD_NO, 1, 2) = 'W3' or
                    substr(t.ORD_NO, 1, 3) = 'WX3' or
                    substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                    d.ORDCUST_CD in ('224902', '243187') AND NVL(C.RZHQX,'T')<>'Z') OR NVL(C.RZHQX,'T')='Z') then
                t.ACT_WGT / 1000
             end) XCSL,
       sum(case
             when (((substr(t.ORD_NO, 1, 1) = '3' or
                    substr(t.ORD_NO, 1, 2) = 'W3' or
                    substr(t.ORD_NO, 1, 3) = 'WX3' or
                    substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                    d.ORDCUST_CD in ('224902', '243187') AND NVL(C.RZHQX,'T')<>'Z') OR NVL(C.RZHQX,'T')='Z') then
              t.ACT_WGT / 1000
           end) XCZL,    
       count(case
               when ((substr(t.ORD_NO, 1, 1) = '3' or
                    substr(t.ORD_NO, 1, 2) = 'W3' or
                    substr(t.ORD_NO, 1, 3) = 'WX3' or
                    substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                    (d.ORDCUST_CD in ('238948') OR instr(t.ORD_NO,'S')>0)) then
                t.ACT_WGT / 1000
             end) TGSL,
       sum(case
             when ((substr(t.ORD_NO, 1, 1) = '3' or
                  substr(t.ORD_NO, 1, 2) = 'W3' or
                  substr(t.ORD_NO, 1, 3) = 'WX3' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND
                  (d.ORDCUST_CD in ('238948') OR instr(t.ORD_NO,'S')>0)) then
              t.ACT_WGT / 1000
           end) TGZL,
       
       count(case
               when substr(t.ORD_NO, 1, 6) = 'XGEC-3' then
                t.ACT_WGT / 1000
             end) CKSL,
       sum(case
             when substr(t.ORD_NO, 1, 6) = 'XGEC-3' then
              t.ACT_WGT / 1000
           end) CKZL,
       count(case
               when ((substr(t.ORD_NO, 1, 1) = '4' or substr(t.ORD_NO, 1, 2) = 'W4' or
                    
                    substr(t.ORD_NO, 1, 6) = 'XGEC-4') AND  NVL(C.RZHQX,'T')<>'Z') then
                t.ACT_WGT / 1000
             end) SZSL,
       sum(case
             when ((substr(t.ORD_NO, 1, 1) = '4' or substr(t.ORD_NO, 1, 2) = 'W4' or
                  
                  substr(t.ORD_NO, 1, 6) = 'XGEC-4') AND  NVL(C.RZHQX,'T')<>'Z' )then
              t.ACT_WGT / 1000
           end) SZZL,
       
       count(case
               when (substr(t.ORD_NO, 1, 1) = '5' or substr(t.ORD_NO, 1, 2) = 'W5' or
                    
                    substr(t.ORD_NO, 1, 6) = 'XGEC-5') then
                t.ACT_WGT / 1000
             end) LTSL,
       sum(case
             when (substr(t.ORD_NO, 1, 1) = '5' or substr(t.ORD_NO, 1, 2) = 'W5' or
                  substr(t.ORD_NO, 1, 6) = 'XGEC-5') then
              t.ACT_WGT / 1000
           end) LTZL
  FROM TBH02_COIL_COMM T,
       TBG02_SLAB_COMM B,
       TBA01_ORD_LINE  C,
       TBA01_ORD_COMM  D,
       TBB02_LOT_COM   E,
       TBJ01_DLIV_DIR  F,
       TBE02_ORD_PRC   P,
       TBF02_SPEC_MILL A
 WHERE '1' = '1'
   AND T.SLAB_NO = B.SLAB_NO(+)
   AND T.ORD_NO = C.ORD_NO(+)
   AND T.ORD_SEQ = C.ORD_SEQ(+)
   AND T.ORD_NO = D.ORD_NO(+)
   AND T.SAMPL_NO = E.SMP_NO(+)
   AND T.COIL_NO = A.COIL_NO(+)
   AND T.DLIV_DIRNO = F.DLIV_DIRNO(+)
   AND T.ORD_NO = P.ORD_NO(+)
   AND T.ORD_SEQ = P.ORD_SEQ(+)
   AND T.COIL_STAT = '2'
   AND SUBSTR(NVL(T.YARD_ENTRY_DTIME, 99999999999999), 1, 8) >= NVL(?, 0)
   AND SUBSTR(NVL(T.YARD_ENTRY_DTIME, 0), 1, 8) <= NVL(?, 99999999999999)
   AND T.SPEC_STL_GRD || '&' LIKE ? || '%'
   AND T.COIL_WTH >= NVL(?, '00000')
   AND T.COIL_WTH <= NVL(?, '99999')
   AND T.COIL_THK >= NVL(?, '000')
   AND T.COIL_THK <= NVL(?, '999')
   AND T.CUR_LOAD_LOC || '&' LIKE ? || '%'
   AND T.OLD_SAMPL_NO LIKE ? || '%'
   AND T.ORD_NO || '&' LIKE ? || '%'
   AND C.ORD_DEVLMT_DATE || '&' LIKE ? || '%'
   AND T.CUR_PROG_CD || '&' LIKE ? || '%'
   AND e.SMP_SEND_DTIME || '&' LIKE ? || '%' --送样时间
   AND t.SPM_YN || '&' like ? || '%' --平整
   AND C.XSQY || '&' LIKE ? || '%' --销售区域
   AND T.COIL_DEFECTIVE || '&' LIKE ? || '%' --次品区分 
   AND C.DLIV_TP || '&' LIKE ? || '%'
   AND P.PROD_LINE || '&' LIKE ? || '%' --产线
   AND P.RCVORD_CLF || '&' LIKE ? || '%'
   AND nvl(A.ROLL_MANA_NO, 'A0000') BETWEEN NVL(?, 'A0000') AND
       NVL(?, 'Z9999')
 group by t.CUR_PROG_CD)
 
 
 select * from a union all 
  select '合计',
         sum(a.hzSL),
         sum(a.hzzL),
         sum(a.RZHYSL),
         sum(a.RZHYzL),
         sum(a.RZqYSL),
         sum(a.RZqYzL),
         sum(a.XCSL),
         sum(a.XCzL),
         sum(a.TGSL),
         sum(a.TGzL),
         sum(a.CKSL),
         sum(a.CKzL),
         sum(a.SZSL),
         sum(a.SZzL),
         sum(a.LTSL),
         sum(a.LTzL)
    from a
					   
		]]>
	</query>
	
	
	<query id="UIM070020_01.SELECT" desc="库存查询">
		<![CDATA[
							
			SELECT 
       substr(a.SLAB_NO, 3, 1) || '号转炉' BOF_NO,
       SUBSTR(E.CC_DEVNO, 2, 1) || '连铸机' CCM_NO
       -------------------------------------
      ,
       A.SLAB_NO,
       A.STL_GRD,
       A.REMARKS REMARKS2,
       A.SLAB_THK,
       A.SLAB_WTH,
       A.SLAB_LEN,
       A.SLAB_WGT,
        A.WEIGHT_WGT,
       decode(a.SLAB_OUT_TP, 'Y', '允许出库', '不允许出库') SLAB_OUT_TP,
       (SELECT TT.SM_CFNM
          FROM TBZ00_COMMCD TT
         WHERE TT.LG_CD = 'G03004'
           AND TT.SM_CD = A.CRK_CD1) CRK_CD1,
       CUR_LOAD_LOC,
       DECODE(SUBSTR(A.CUR_LOAD_LOC, 1, 1),
              'Z',
              A.SLAB_STOCK_ENT_DTIME,
              'L',
              A.SMS_YARD_ENT_DTIME) SLAB_STOCK_ENT_DTIME,
       ROUND((SYSDATE - to_date(nvl(DECODE(SUBSTR(A.CUR_LOAD_LOC, 1, 1),
                                           'Z',
                                           A.SLAB_STOCK_ENT_DTIME,
                                           'L',
                                           A.SMS_YARD_ENT_DTIME),
                                    TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')),
                                'YYYYMMDDHH24MISS')) * 24,
             3) ZKSJ,
       A.ORD_NO,
       decode(A.ORD_FL, '1', '订单材', '2', '余材') ORD_FL,
       (select sm_cfnm
          from tbz00_commcd
         where lg_cd like 'E01010%'
           and sm_cd = A.NONORD_CAUSE_CD) NONORD_CAUSE_CD,
      -- A.NONORD_OCR_DTIME,
       decode(A.SLAB_RT, 'H', '热轧厂', 'Z', '中板厂', 'P', '厚板厂') SLAB_RT,
       DECODE(A.SLAB_RT, 'H', A.ORD_NO, I.ORD_NO) ORD_NO,
       DECODE(A.SLAB_RT, 'H', A.ORD_SEQ, I.ORD_SEQ) ORD_SEQ,
       '' REMARKS,
       A.SLAB_CUT_DTIME --切割时间
      ,
       A.SLAB_INGR_DEC_GRD,
       E.INGR_DEC_DTIME
       --,A.SLAB_INGR_DEC_DTIME
      ,
       decode(A.CUT_WKSHIFT, '1', '早班', '2', '中班', '3', '晚班') CUT_WKSHIFT,
       decode(A.CUT_WKGROUP,
              'A',
              '甲班',
              'B',
              '乙班',
              'C',
              '丙班',
              'D',
              '丁班') CUT_WKGROUP,
       A.CUT_EMP_ID,
       decode(G.KEEPWARM_YN, 'Y', '有', 'N', '无', '', '无') KEEPWARM_YN --是否需要坑冷
      ,
       (SELECT T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'A01001'
           AND T.SM_CD = D.RCVORD_TP) RCVORD_TP --是否出口
      ,
       G.H_SPEC_STL_GRD SPEC_STL_GRD --订单钢号
      ,
       C.CMT_DLIV_DEVLMT_DATE --交货期
      ,
       G.ORD_THK --订单厚度
      ,
       G.ORD_WTH --订单宽度
      ,
       ROUND(C.PROD_WGT_MIN / 1000, 2) || '~' ||
       ROUND(C.PROD_WGT_MAX / 1000, 2) || 'T' ORD_WGT --订单单重范围
      ,
       DECODE(C.DLIV_TP, 'T', '火运', 'C', '汽运') DLIV_TP -- 运输方式
      ,
       F.STL_GRD PLAN_STL_GRD,
       decode(A.SLAB_INGR_DEC_GRD, '1', '合格', '2', '不合格', '', '待判') SLAB_INGR_DEC_GRD,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE SM_CD = SUBSTR(A.SLAB_NO, 3, 1)
           AND LG_CD = 'B01132') CHANDI,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE SM_CD = A.SLAB_INGR_DEC_KIND
           AND LG_CD = 'B01133') SLAB_INGR_DEC_KIND --成分判定依据
      ,
       NVL(A.SLAB_DCS_DETAIL, E.CHARGE_DCS_DETAIL) REMARKS,
       DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN,
       C.XSQY --销售区域 生产处刘树根要求添加  TL 091225
      ,
       (SELECT SM_CFNM
          FROM TBZ00_COMMCD
         WHERE LG_CD LIKE 'B01136'
           AND SM_CD = A.SLAB_TOT_DEC_GRD) SLAB_TOT_DEC_GRD,
       XB_PACKAGE.GF_COMNNAME_FIND('E01002', A.CUR_PROG_CD) CUR_PROG_CD
      -- ,G.PRODNM_CD --交货状态
      
     -- , G.C_PRODNM_CD --交货状态
      ,
       G.PRD_TY --产品类型
      ,
       H.L2_SCH_NO
  FROM TBG02_SLAB_COMM A,
       TBG04_SLAB_YARD B,
       TBA01_ORD_LINE C,
       TBA01_ORD_COMM D,
       TBG02_CHARGE_COMM E,
       TBF01_SPEC_SLAB F,
       TBE02_ORD_PRC G,
       TBD01_SLABDN_OBJ2 H,
       TBB01_ORD_MAP I,
       (SELECT TH.ORD_NO, TH.ORD_SEQ, '1' DSN_KIND
          FROM TBB01_ORD_HEAD TH
         WHERE TH.CUST_SPEC_NO IS NOT NULL) K
 WHERE A.SLAB_NO = B.SLAB_NO(+)
   AND A.PLAN_SLAB_NO = F.SLAB_MANA_NO(+)
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   and a.ORD_NO = g.ORD_NO(+)
   and a.ORD_SEQ = g.ORD_SEQ(+)
   AND A.ORD_NO = D.ORD_NO(+)
   AND A.ORD_NO = K.ORD_NO(+)
   AND A.ORD_SEQ = K.ORD_SEQ(+)
   AND A.DSN_SLAB_NO = H.DSN_SLAB_NO(+)
   and H.ORD_NO = I.ZH_ORD_NO(+)
   AND H.ORD_SEQ = I.ZH_ORD_SEQ(+)
      ----MOD BY WXY 炉次变更----100302---------
   AND SUBSTR(A.SUB_SLAB_NO, 1, 10) = E.CHARGE_NO
      ----------------------------------------     
   AND A.CUR_LOAD_LOC = B.YARD_ADDR(+) || B.STORELAY(+)
   AND A.SLAB_STAT = '2'
   --and A.SLAB_RTURN = 'Y'
   AND A.SLAB_NO LIKE ? || '%'
   AND A.STL_GRD LIKE '%' || NVL(?, A.STL_GRD) || '%'
  -- AND A.CUR_LOAD_LOC || '&' LIKE DECODE(?, 'G', '') || '%'
   AND A.CUR_LOAD_LOC || '&' LIKE ? || '%'
   AND A.SLAB_LEN >= nvl(?,'00000')
   AND A.SLAB_LEN <= nvl(?,'100000')
   AND A.ORD_NO || '&' LIKE ? || '%'
   AND A.ORD_FL || '&' LIKE ? || '%'
   AND A.SLAB_STOCK_TYPE || '&' like ? || '%' --热轧入库类型(1:热送入库 2 冷送入库 3 加回入库 4 轧回入库）
   AND A.SLAB_RT || '&' LIKE ? || '%'
  
   AND A.SLAB_INGR_DEC_GRD || '&' LIKE ? || '%'
   AND A.SLAB_WTH >= nvl(?,'0000')
   AND A.SLAB_WTH <= nvl(?,'100000')
   AND SUBSTR(nvl(C.CMT_DLIV_DEVLMT_DATE, 10000000), 1, 8) <=
       nvl(?, '50000000') --交货期
      -------------------------------------------
   AND C.DLIV_TP || '&' LIKE ? || '%' --运输方式
   AND C.KEEPWARM_YN || '&' LIKE ? || '%'
 
      --------------------ADD BY WXY 091130 生产处要求
   AND substr(A.SLAB_CUT_DTIME, 1, 8) >= ?
   AND substr(A.SLAB_CUT_DTIME, 1, 8) <= ?
   AND SUBSTR(A.SLAB_NO, 3, 1) || '&' LIKE ? || '%'
   AND SUBSTR(E.CC_DEVNO, 2, 1) || '&' LIKE ? || '%'
   AND C.XSQY || '&' LIKE ? || '%'
    AND A.SLAB_THK >= nvl(?,'0000')
   AND A.SLAB_THK <= nvl(?,'5000')
 
 ORDER BY STL_GRD, SLAB_NO, SLAB_WTH
		]]>
	</query>
	
	<query id="UIM070030_01.SELECT" desc="库存查询">
		<![CDATA[
				SELECT SUBSTR(A.YARD_ENTRY_DTIME, 1, 8) YARD_ENTRY_DTIME --入库时间
      ,
       round((select TO_DATE(a.TRNF_USE_TIME, 'YYYY-MM-DD HH24:MI:SS') -
                    TO_DATE(NVL(A.MILL_DTIME, A.COIL_END_DTIME),
                            'YYYY-MM-DD HH24:MI:SS')
               from dual),
             2) ZKSJ,
       XB_PACKAGE.GF_COMNNAME_FIND('A01004', D.PRDNM_CD) PRODNM_CD,
       A.YARD_ENTRY_USE_TIME CZSJ --操作时间
      ,
       NVL(A.MILL_DTIME, A.COIL_END_DTIME) MILL_DTIME,
       A.YZCS_DTIME,													--预装时间
       decode(A.YARD_ENTRY_SHIFT ,'1','早' ,'2','中','3','晚','') SHIFT--班次
      ,
       decode(A.YARD_ENTRY_GROUP,'A','甲' ,'B','乙','C','丙','D','丁','') WKGROUP --班组
      ,
       A.YARD_ENTRY_REG CZR --操作人
      ,
       SUBSTR(A.OLD_SAMPL_NO, 1, 10) ZPH --轧批号
      ,
       A.OLD_SAMPL_NO,
       A.SPEC_STL_GRD
       --新加字段 zmz
      ,
       XB_PACKAGE.GF_COMNNAME_FIND('A01009', C.DEST_PCD) DEST_PCD --到站
      ,
       C.ORD_DEVLMT_DATE,
       A.INSTR_COIL_THK ORD_THK,
       A.INSTR_COIL_WTH ORD_WTH,
       A.COIL_THK COIL_THK,
       A.COIL_WTH COIL_WTH,
       A.ACT_WGT,
       A.CUR_LOAD_LOC,
       E.ROLL_MANA_NO,
       E.ROLL_SLAB_SEQ,
       decode(A.TOT_DEC_GRD,
              '1',
              '合格',
              '2',
              '不合格',
              '3',
              '外观异常',
              '4',
              '废品') TOT_DEC_GRD --综合判定
      ,
       decode(A.INGR_DEC_GRD,
              '0',
              '综合判定取消',
              '1',
              '合格',
              '2',
              '不合格') INGR_DEC_GRD --成份判定
      ,
       decode(A.MATLQLTY_DEC_GRD,
              '0',
              '无性能要',
              '1',
              '合格',
              '2',
              '不合格') MATLQLTY_DEC_GRD --材质判定
      ,
       decode(A.SIZE_DEC_RST,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '不合格',
              '4',
              '废品') SIZE_DEC_RST --尺寸判定
      ,
       decode(A.EXTSHAPE_DEC_GRD,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '次品',
              '4',
              '废品',
              '待判定') EXTSHAPE_DEC_GRD --外观判定
      ,
       DECODE(A.WGT_DEC_RST, '1', '合格', '2', '不合格') WGT_DEC_RST,
       DECODE(A.QK_YN, 'Y', '清库卷') QK_YN,
       A.ORD_NO,
       a.ORD_SEQ,
       (SELECT T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'A01012'
           AND T.SM_CD = C.DLIV_TP) DLIV_TP,
       (SELECT T.CUST_NM
          FROM TBZ00_CUSTOMER T
         WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD,
       B.SLAB_NO CHARGE_NO,
       B.STL_GRD,
       a.TRNF_DTIME,
       A.TRANS_CAR_NO,
       A.DLIV_DIRNO,
       DECODE(A.COIL_DEFECTIVE,
              '1A',
              null,
              '2A',
              '成分不合格',
              '2E',
              '工序不合格',
              '3B',
              '尺寸不合格',
              '3C',
              '重量不合格',
              '3D',
              '外观不合格',
              '4A',
              '性能不合格') COIL_DEFECTIVE,
       A.BEF_SPEC_STL_GRD,
       DECODE(A.BEF_MATLQLTY_DEC_GRD,
              '0',
              '无性能要求',
              '1',
              '合格',
              '2',
              '不合格') BEF_MATRL_END_CAUSE_CD --清库前性能
      ,
       DECODE(A.BEF_EXTSHAPE_DEC_GRD,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '次品',
              '4',
              '废品') BEF_EXTSHAPE_DEC_GRD --清库前外观
      ,
       C.XSQY --销售区域  
--,C.DEST_PCD --到站
  FROM TBH02_COIL_COMM A,
       TBG02_SLAB_COMM B,
       TBA01_ORD_LINE  C,
       TBA01_ORD_COMM  D,
       TBF02_SPEC_MILL E

 WHERE '1' = '1'
   AND A.SLAB_NO = B.SLAB_NO(+)
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   AND A.ORD_NO = D.ORD_NO(+)
   AND A.COIL_NO = E.COIL_NO(+)
   AND (SUBSTR(A.YARD_ENTRY_DTIME, 1, 8) >= ?)
   AND (SUBSTR(A.YARD_ENTRY_DTIME, 1, 8) <= ?)
   AND A.OLD_SAMPL_NO LIKE ? || '%'
   AND A.COIL_THK >= NVL(? , '000')
   AND A.COIL_THK <= NVL(? , '999')
   AND A.COIL_WTH >= NVL(? , '00000')
   AND A.COIL_WTH <= NVL(? , '99999')
   AND (A.CUR_LOAD_LOC LIKE ?  || '%' OR A.CUR_LOAD_LOC IS NULL)
   AND A.SPEC_STL_GRD LIKE ?  || '%'
   AND A.YARD_ENTRY_SHIFT || '&' LIKE ?  || '%'
   AND A.YARD_ENTRY_GROUP || '&' LIKE ? || '%'
   AND A.YARD_ENTRY_REG || '&' LIKE ?  || '%'
   AND A.TRANS_CAR_NO || '&' LIKE ?  || '%'
   AND A.ORD_NO || '&' LIKE ?  || '%'
   AND A.ORD_SEQ || '&' LIKE ?  || '%'
   AND A.DLIV_DIRNO || '&' LIKE ? || '%'
   AND D.ORDCUST_CD || '&' LIKE ?  || '%'
   AND A.DLIV_TP || '&' LIKE ?  || '%'
   AND A.ORD_FL || '&' LIKE ?  || '%'
   AND A.QK_YN || '&' LIKE ?  || '%'
   AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),
            SUBSTR(A.YARD_ENTRY_DTIME, 1, 8)) >= NVL(? , '0'))
   AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),
            SUBSTR(A.YARD_ENTRY_DTIME, 1, 8)) <= NVL(? , '99999999'))
   AND A.COIL_IN_REG IS NULL -- ADD BY JUNGAN 2010-12-29 不查询盘入记录
 order by a.OLD_SAMPL_NO desc
		]]>
	</query>
	
	
	
	<query id="UIM070030_02.SELECT" desc="出库查询">
		<![CDATA[
			SELECT SUBSTR(A.YARD_ENTRY_DTIME, 1, 8) YARD_ENTRY_DTIME, --入库时间
       round((select TO_DATE(a.TRNF_USE_TIME, 'YYYY-MM-DD HH24:MI:SS') -
                    TO_DATE(NVL(A.MILL_DTIME, A.COIL_END_DTIME),
                            'YYYY-MM-DD HH24:MI:SS')
               from dual),
             2) ZKSJ
      ,
       XB_PACKAGE.GF_COMNNAME_FIND('A01004', D.PRDNM_CD) PRODNM_CD,
       A.TRNF_USE_TIME CZSJ --操作时间
      ,
       NVL(A.MILL_DTIME, A.COIL_END_DTIME) MILL_DTIME,
       A.YZCS_DTIME,													--预装时间
          decode(A.TRNF_SHIFT ,'1','早' ,'2','中','3','晚','') SHIFT--班次
      ,
       decode(A.TRNF_GROUP,'A','甲' ,'B','乙','C','丙','D','丁','') WKGROUP --班组
      ,
       A.TRNF_REG CZR --操作人
      ,
       SUBSTR(A.OLD_SAMPL_NO, 1, 10) ZPH --轧批号
      ,
       A.OLD_SAMPL_NO,
       XB_PACKAGE.GF_COMNNAME_FIND('A01009', C.DEST_PCD) DEST_PCD --到站
      ,
       A.SPEC_STL_GRD,
       C.ORD_DEVLMT_DATE,
       A.INSTR_COIL_THK ORD_THK,
       A.INSTR_COIL_WTH ORD_WTH,
       A.COIL_THK COIL_THK,
       A.COIL_WTH COIL_WTH,
       A.ACT_WGT,
       A.CUR_LOAD_LOC,
       E.ROLL_MANA_NO,
       E.ROLL_SLAB_SEQ,
       decode(A.TOT_DEC_GRD,
              '1',
              '合格',
              '2',
              '不合格',
              '3',
              '外观异常',
              '4',
              '废品') TOT_DEC_GRD --综合判定
      ,
       decode(A.INGR_DEC_GRD, '1', '合格', '2', '不合格') INGR_DEC_GRD --成份判定
      ,
       decode(A.MATLQLTY_DEC_GRD, '1', '合格', '2', '不合格') MATLQLTY_DEC_GRD --材质判定
      ,
       decode(A.SIZE_DEC_RST, '1', '合格', '2', '不合格') SIZE_DEC_RST --尺寸判定
      ,
       decode(A.EXTSHAPE_DEC_GRD,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '次品',
              '4',
              '废品',
              '待判定') EXTSHAPE_DEC_GRD --外观判定
      ,
       DECODE(A.WGT_DEC_RST, '1', '合格', '2', '不合格') WGT_DEC_RST,
       DECODE(A.QK_YN, 'Y', '清库卷') QK_YN,
       A.ORD_NO,
       a.ORD_SEQ,
       (SELECT T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'A01012'
           AND T.SM_CD = C.DLIV_TP) DLIV_TP,
       (SELECT T.CUST_NM
          FROM TBZ00_CUSTOMER T
         WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD,
       B.SLAB_NO CHARGE_NO,
       B.STL_GRD,
       a.TRNF_DTIME,
       A.TRANS_CAR_NO,
       A.DLIV_DIRNO,
       DECODE(A.COIL_DEFECTIVE,
              '1A',
              null,
              '2A',
              '成分不合格',
              '2E',
              '工序不合格',
              '3B',
              '尺寸不合格',
              '3C',
              '重量不合格',
              '3D',
              '外观不合格',
              '4A',
              '性能不合格') COIL_DEFECTIVE,
       A.BEF_SPEC_STL_GRD,
       DECODE(A.BEF_MATLQLTY_DEC_GRD,
              '0',
              '无性能要求',
              '1',
              '合格',
              '2',
              '不合格') BEF_MATRL_END_CAUSE_CD --清库前性能
      ,
       DECODE(A.BEF_EXTSHAPE_DEC_GRD,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '次品',
              '4',
              '废品') BEF_EXTSHAPE_DEC_GRD --清库前外观 
      ,
       C.XSQY --销售区域    
  FROM TBH02_COIL_COMM A,
       TBG02_SLAB_COMM B,
       TBA01_ORD_LINE  C,
       TBA01_ORD_COMM  D,
       TBF02_SPEC_MILL E
 WHERE '1' = '1'
   AND A.SLAB_NO = B.SLAB_NO(+)
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   AND A.ORD_NO = D.ORD_NO(+)
   AND A.COIL_NO = E.COIL_NO(+)
   AND (SUBSTR(A.TRNF_DTIME, 1, 8) >= ?) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)>= :2)) 
   AND (SUBSTR(A.TRNF_DTIME, 1, 8) <= ?) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)<= :4))
   and a.COIL_STAT = '3'
   AND nvl(a.ISDIVI, 1) <> '0'
   AND A.OLD_SAMPL_NO LIKE ? || '%'
   AND A.COIL_THK >= NVL(?, '000')
   AND A.COIL_THK <= NVL(?, '999')
   AND A.COIL_WTH >= NVL(?, '00000')
   AND A.COIL_WTH <= NVL(?, '99999')
   AND (A.CUR_LOAD_LOC LIKE ? || '%' OR A.CUR_LOAD_LOC IS NULL)
   AND A.SPEC_STL_GRD LIKE ? || '%'
   AND A.TRNF_SHIFT || '&' LIKE ? || '%'
   AND A.TRNF_GROUP || '&' LIKE ? || '%'
   AND A.TRNF_REG || '&' LIKE ? || '%'
   AND A.TRANS_CAR_NO || '&' LIKE ? || '%'
   AND A.ORD_NO || '&' LIKE ? || '%'
   AND A.ORD_SEQ || '&' LIKE ? || '%'
   AND A.DLIV_DIRNO || '&' LIKE ? || '%'
   AND D.ORDCUST_CD || '&' LIKE ? || '%'
   AND A.DLIV_TP || '&' LIKE ? || '%'
   --AND A.COIL_DEFECTIVE || '&' LIKE :19 || '%'
   AND A.ORD_FL || '&' LIKE ? || '%'
   AND A.QK_YN || '&' LIKE ? || '%'
  -- AND INSTR(NVL(:22, NVL(D.PRDNM_CD, 'NULL')), NVL(D.PRDNM_CD, 'NULL')) > 0
  -- AND C.XSQY || '&' like ?|| '%'
  -- and a.SPM_YN || '&' like :24 || '%'
   AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.TRNF_DTIME, 1, 8)) >=
       NVL(?, '0'))
   AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.TRNF_DTIME, 1, 8)) <=
       NVL(?, '99999999'))
  
   AND (A.SHIP_PROG_CD <> '10' or A.SHIP_PROG_CD IS NULL) -- ADD BY JUNGAN 2010-12-29 不查询盘出记录
 order by a.OLD_SAMPL_NO desc
		]]>
	</query>
	
	
	<query id="UIM070040_01.SELECT" desc="热卷收发存">
		<![CDATA[
			SELECT *
  FROM (SELECT COUNT(INVID) OVER(PARTITION BY INVID) CNT, ZZ.*
          FROM (WITH A AS (SELECT *
                             FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T.INVID ORDER BY T.CREATETIME DESC) RN,
                                          T.INVID,
                                          TO_CHAR(T.BETRANSWGT) BETRANSWGT,
                                          TO_CHAR(T.CREATETIME,
                                                  'YYYY-MM-DD HH24:MI:SS') CREATETIME
                                     FROM MATAL_DETAIL_INACCOUNTING T
                                    WHERE T.ACTIVITYID = '11AA'
                                      AND T.ACCTDATE < :1)
                            WHERE RN = 1), B AS (SELECT ROW_NUMBER() OVER(PARTITION BY T.INVID ORDER BY T.CREATETIME ASC) RN,
                                                        PKG_QUALITY_COMM.FZ00_COMM('A01016',
                                                                                   T.ACTIVITYID) ACTIVITYID,
                                                        
                                                        T.INVID,
                                                        TO_CHAR(T.BETRANSWGT) BETRANSWGT,
                                                        TO_CHAR(T.CREATETIME,
                                                                'YYYY-MM-DD HH24:MI:SS') CREATETIME,
                                                        T.ALLOYNO,
                                                        T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                                   FROM MATAL_DETAIL_INACCOUNTING T
                                                  WHERE T.ACTIVITYID != '11AA'
                                                    AND T.ACCTDATE < :2
                                                 
                                                 UNION ALL
                                                 
                                                 SELECT 1,
                                                        '初始库存',
                                                        H.OLD_SAMPL_NO,
                                                        TO_CHAR(H.ACT_WGT / 1000),
                                                        '2021-10-13 15:17:00',
                                                        
                                                        H.SPEC_STL_GRD,
                                                        '热轧',
                                                        h.ord_no,
                                                        'N',
                                                        '',
                                                        'HCR',
                                                        '20211013',
                                                        '',
                                                        '',
                                                        TO_CHAR(H.COIL_LEN),
                                                        TO_CHAR(H.COIL_THK),
                                                       TO_CHAR( H.COIL_WTH),
                                                        '',
                                                        H.SLAB_NO
                                                   FROM TBH02_COIL_STOCK H), C AS (SELECT '',
                                                                                          ROW_NUMBER() OVER(PARTITION BY T.INVID ORDER BY T.CREATETIME ASC) RN,
                                                                                          PKG_QUALITY_COMM.FZ00_COMM('A01016',
                                                                                                                     T.ACTIVITYID) ACTIVITYID,
                                                                                          
                                                                                          T.INVID,
                                                                                          TO_CHAR(T.BETRANSWGT * -1),
                                                                                          TO_CHAR(T.CREATETIME,
                                                                                                  'YYYY-MM-DD HH24:MI:SS') CREATETIME,
                                                                                          T.ALLOYNO,
                                                                                          T.MANAGEIP,
                                                                                          T.ORDERITEM,
                                                                                          T.EXECJOBFUNC,
                                                                                          T.PRODNAME,
                                                                                          T.PRODTYPENO,
                                                                                          T.ACCTDATE,
                                                                                          T.CREATEID,
                                                                                          T.INSTDISP,
                                                                                          TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                                                          TO_CHAR(T.INVTHICK) INVTHICK,
                                                                                          TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                                                          T.GRADE,
                                                                                          T.REFNOA
                                                                                     FROM MATAL_DETAIL_OUTACCOUNTING T
                                                                                    WHERE T.ACCTDATE <
                                                                                         :3)
                 SELECT TO_CHAR(A.BETRANSWGT) ACTWGT, B.*
                   FROM A, B
                  WHERE B.INVID = A.INVID(+)
                 UNION ALL
                 SELECT * FROM C) ZZ ORDER BY INVID,CREATETIME
        )
 WHERE MOD(CNT, 2) = 1
		]]>
	</query>
	
	
	<query id="UIM070040_02.SELECT" desc="热卷收发存">
		<![CDATA[
				select count(invid) over(partition by invid) cnt, zz.*
          from (WITH a as (SELECT *
                             FROM (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                          t.invid,
                                          to_char(t.betranswgt) betranswgt,
                                          to_char(t.createtime,
                                                  'yyyy-mm-dd hh24:mi:ss') createtime
                                     from matal_detail_INaccounting t
                                    WHERE T.ACTIVITYID = '11AA'
                                      AND T.ACCTDATE between :1 and :2)
                            WHERE RN = 1), b as (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                        pkg_quality_comm.FZ00_COMM('A01016',
                                                                                   t.activityid) activityid,
                                                        
                                                        t.invid,
                                                        to_char(t.betranswgt) betranswgt,
                                                        to_char(t.createtime,
                                                                'yyyy-mm-dd hh24:mi:ss') createtime,
                                                        T.ALLOYNO,
                                                        T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                                   from matal_detail_INaccounting t
                                                  WHERE T.ACTIVITYID != '11AA'
                                                    AND T.ACCTDATE between :3 and :4
                                                 
                                                 union all
                                                 
                                                 SELECT 1,
                                                        '初始库存',
                                                        H.OLD_SAMPL_NO,
                                                        to_char(H.ACT_WGT / 1000),
                                                        '2021-10-13 15:17:00',
                                                        
                                                        H.SPEC_STL_GRD,
                                                         '热轧',
                                                        h.ord_no,
                                                        'N',
                                                        '',
                                                        'HCR',
                                                        '20211013',
                                                        '',
                                                        '',
                                                        TO_CHAR(H.COIL_LEN),
                                                        TO_CHAR(H.COIL_THK),
                                                       TO_CHAR( H.COIL_WTH),
                                                        '',
                                                        H.SLAB_NO
                                                   FROM TBH02_COIL_STOCK H), c as (select '',
                                                                                          row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                                                          pkg_quality_comm.FZ00_COMM('A01016',
                                                                                                                     t.activityid) activityid,
                                                                                          
                                                                                          t.invid,
                                                                                          to_char(t.betranswgt * -1),
                                                                                          to_char(t.createtime,
                                                                                                  'yyyy-mm-dd hh24:mi:ss') createtime,
                                                                                          T.ALLOYNO,
                                                                                          T.MANAGEIP,
                                                                                          T.ORDERITEM,
                                                                                          T.EXECJOBFUNC,
                                                                                          T.PRODNAME,
                                                                                          T.PRODTYPENO,
                                                                                          T.ACCTDATE,
                                                                                          T.CREATEID,
                                                                                          T.INSTDISP,
                                                                                          TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                                                          TO_CHAR(T.INVTHICK) INVTHICK,
                                                                                          TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                                                          T.GRADE,
                                                                                          T.REFNOA
                                                                                     from matal_detail_OUTaccounting t
                                                                                    WHERE T.ACCTDATE between :5 and :6)
                 select to_char(a.betranswgt) actwgt, b.*
                   from a, b
                  where b.invid = a.invid(+)
                 union all
                 select * from c) zz order by invid
    
		]]>
	</query>
	
	
	<query id="UIM070040_03.SELECT" desc="热卷收发存">
		<![CDATA[
		select count(invid) over(partition by invid) cnt, zz.*
          from (WITH a as (SELECT *
                             FROM (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                          t.invid,
                                          to_char(t.betranswgt) betranswgt,
                                          to_char(t.createtime,
                                                  'yyyy-mm-dd hh24:mi:ss') createtime
                                     from matal_detail_INaccounting t
                                    WHERE T.ACTIVITYID = '11AA'
                                      AND T.ACCTDATE between :1 and :2)
                            WHERE RN = 1), b as (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                        pkg_quality_comm.FZ00_COMM('A01016',
                                                                                   t.activityid) activityid,
                                                        
                                                        t.invid,
                                                        to_char(t.betranswgt) betranswgt,
                                                        to_char(t.createtime,
                                                                'yyyy-mm-dd hh24:mi:ss') createtime,
                                                        T.ALLOYNO,
                                                        T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                                   from matal_detail_INaccounting t
                                                  WHERE T.ACTIVITYID != '11AA'
                                                    AND T.ACCTDATE between :3 and :4
                                                 
                                                 union all
                                                 
                                                 SELECT 1,
                                                        '初始库存',
                                                        H.OLD_SAMPL_NO,
                                                        to_char(H.ACT_WGT / 1000),
                                                        '2021-10-13 15:17:00',
                                                        H.SPEC_STL_GRD,
                                                          '热轧',
                                                        h.ord_no,
                                                        'N',
                                                        '',
                                                        'HCR',
                                                        '20211013',
                                                        '',
                                                        '',
                                                        TO_CHAR(H.COIL_LEN),
                                                        TO_CHAR(H.COIL_THK),
                                                       TO_CHAR( H.COIL_WTH),
                                                        '',
                                                        H.SLAB_NO
                                                   FROM TBH02_COIL_STOCK H)
                 select to_char(a.betranswgt) actwgt, b.*
                   from a, b
                  where b.invid = a.invid(+)
            ) zz order by invid
        
    
		]]>
	</query>
	
	
	<query id="UIM070040_04.SELECT" desc="热卷收发存">
		<![CDATA[
			select count(invid) over(partition by invid) cnt, zz.*
  from (WITH a as (SELECT *
                     FROM (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                  t.invid,
                                  to_char(t.betranswgt) betranswgt,
                                  to_char(t.createtime,
                                          'yyyy-mm-dd hh24:mi:ss') createtime
                             from matal_detail_INaccounting t
                            WHERE T.ACTIVITYID = '11AA'
                              AND T.ACCTDATE between :1 and :2)
                    WHERE RN = 1), c as (select 
                                                row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                pkg_quality_comm.FZ00_COMM('A01016',
                                                                           t.activityid) activityid,
                                                
                                                t.invid,
                                                to_char(t.betranswgt * -1) betranswgt,
                                                to_char(t.createtime,
                                                        'yyyy-mm-dd hh24:mi:ss') createtime,
                                                T.ALLOYNO,
                                                  T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                           from matal_detail_OUTaccounting t
                                          WHERE T.ACCTDATE between :3 and :4
                                                )
         select to_char(a.betranswgt) actwgt, c.*
           from a, c
          where c.invid = a.invid(+)) zz
          order by invid
    
		]]>
	</query>
	
	
	<query id="UIM070040_05.SELECT" desc="热卷收发存">
		<![CDATA[
			select *
  from (select count(invid) over(partition by invid) cnt, zz.*
          from (WITH a as (SELECT *
                             FROM (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                          t.invid,
                                          to_char(t.betranswgt) betranswgt,
                                          to_char(t.createtime,
                                                  'yyyy-mm-dd hh24:mi:ss') createtime
                                     from matal_detail_INaccounting t
                                    WHERE T.ACTIVITYID = '11AA'
                                      AND T.ACCTDATE between :1 and :2)
                            WHERE RN = 1), b as (select row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                          pkg_quality_comm.FZ00_COMM( 'A01016',t.activityid) activityid,
                                                     
                                                        t.invid,
                                                        to_char(t.betranswgt) betranswgt,
                                                        to_char(t.createtime,
                                                                'yyyy-mm-dd hh24:mi:ss') createtime,
                                                        T.ALLOYNO,
                                                          T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                                   from matal_detail_INaccounting t
                                                  WHERE T.ACTIVITYID != '11AA'
                                                    AND T.ACCTDATE between :3 and :4
                                                    
                                                 union all
                                                 
                                                 SELECT 1,
                                                        '初始库存',
                                                        H.OLD_SAMPL_NO,
                                                        to_char(H.ACT_WGT / 1000),
                                                        '2021-10-13 15:17:00',
                                                        
                                                        H.SPEC_STL_GRD,
                                                    
                                                          '热轧',
                                                        h.ord_no,
                                                        'N',
                                                        '',
                                                        'HCR',
                                                        '20211013',
                                                        '',
                                                        '',
                                                        TO_CHAR(H.COIL_LEN),
                                                        TO_CHAR(H.COIL_THK),
                                                       TO_CHAR( H.COIL_WTH),
                                                        '',
                                                        H.SLAB_NO
                                                   FROM TBH02_COIL_STOCK H), c as (select '',
                                                                                          row_number() over(partition by t.invid order by t.createtime desc) rn,
                                                                                            pkg_quality_comm.FZ00_COMM( 'A01016',t.activityid) activityid,
                                                     
                                                                                          t.invid,
                                                                                          to_char(t.betranswgt * -1),
                                                                                          to_char(t.createtime,
                                                                                                  'yyyy-mm-dd hh24:mi:ss') createtime,
                                                                                          T.ALLOYNO,
                                                                                            T.MANAGEIP,
                                                        T.ORDERITEM,
                                                        T.EXECJOBFUNC,
                                                        T.PRODNAME,
                                                        T.PRODTYPENO,
                                                        T.ACCTDATE,
                                                        T.CREATEID,
                                                        T.INSTDISP,
                                                        TO_CHAR(T.INVLENGTH) INVLENGTH,
                                                        TO_CHAR(T.INVTHICK) INVTHICK,
                                                        TO_CHAR(T.INVWIDTH) INVWIDTH,
                                                        T.GRADE,
                                                        T.REFNOA
                                                                                     from matal_detail_OUTaccounting t
                                                                                    WHERE T.ACCTDATE between :5 and :6
                                                                                          )
                 select to_char(a.betranswgt) actwgt, b.*
                   from a, b
                  where b.invid = a.invid(+)
                 union all
                 select * from c) zz order by invid
        )
 where mod(cnt, 2) = 0
    
		]]>
	</query>
	
	<query id="UIM070050_01.SELECT" desc="平整指定">
		<![CDATA[
		WITH A AS(	select 
				to_char(t.rtime,'yyyymmdd') ht,t.spmtype,
       			count(1) HZSL,
       			sum(t.actwgt) / 1000 HZZL,
				SUM(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'T') then
              1
           end) RZHYSL,
       SUM(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'T') then
              t.ACTWGT / 1000
           end) RZHYZL,
       SUM(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'C') then
              1
           end) RZQYSL,
       SUM(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND c.DLIV_TP = 'C') then
              t.ACTWGT / 1000
           end) RZQYZL,
       count(case
               when ((substr(t.ORDNO, 1, 1) = '3' or
                    substr(t.ORDNO, 1, 2) = 'W3' or
                    substr(t.ORDNO, 1, 3) = 'WX3' or
                    substr(t.ORDNO, 1, 6) = 'XGEC-3') AND
                    d.ORDCUST_CD in ('224902', '243187')) then
                t.ACTWGT / 1000
             end) XCSL,
       sum(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND
                  d.ORDCUST_CD in ('224902', '243187')) then
              t.ACTWGT / 1000
           end) XCZL,
       count(case
               when ((substr(t.ORDNO, 1, 1) = '3' or
                    substr(t.ORDNO, 1, 2) = 'W3' or
                    substr(t.ORDNO, 1, 3) = 'WX3' or
                    substr(t.ORDNO, 1, 6) = 'XGEC-3') AND
                    (d.ORDCUST_CD in ('238948') OR instr(t.ORDNO,'S')>0)) then
                t.ACTWGT / 1000
             end) TGSL,
       sum(case
             when ((substr(t.ORDNO, 1, 1) = '3' or
                  substr(t.ORDNO, 1, 2) = 'W3' or
                  substr(t.ORDNO, 1, 3) = 'WX3' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-3') AND
                  (d.ORDCUST_CD in ('238948') OR instr(t.ORDNO,'S')>0)) then
              t.ACTWGT / 1000
           end) TGZL,
    
       count(case
               when (substr(t.ORDNO, 1, 1) = '4' or substr(t.ORDNO, 1, 2) = 'W4' or
                    
                    substr(t.ORDNO, 1, 6) = 'XGEC-4') then
                t.ACTWGT / 1000
             end) SZSL,
       sum(case
             when (substr(t.ORDNO, 1, 1) = '4' or substr(t.ORDNO, 1, 2) = 'W4' or
                  
                  substr(t.ORDNO, 1, 6) = 'XGEC-4') then
              t.ACTWGT / 1000
           end) SZZL,
       
       count(case
               when (substr(t.ORDNO, 1, 1) = '5' or substr(t.ORDNO, 1, 2) = 'W5' or
                    
                    substr(t.ORDNO, 1, 6) = 'XGEC-5') then
                t.ACTWGT / 1000
             end) LTSL,
       sum(case
             when (substr(t.ORDNO, 1, 1) = '5' or substr(t.ORDNO, 1, 2) = 'W5' or
                  substr(t.ORDNO, 1, 6) = 'XGEC-5') then
              t.ACTWGT / 1000
           end) LTZL
  FROM spm_report T,  TBA01_ORD_COMM  D,
       TBA01_ORD_LINE  C
    
 WHERE '1' = '1'
   AND T.ORDNO = C.ORD_NO(+)
   AND T.ORDSEQ = C.ORD_SEQ(+)
   AND T.ORDNO = d.ORD_NO(+)
  and to_char(t.rtime,'yyyymmdd') between ? and ?
 group by t.spmtype,to_char(t.rtime,'yyyymmdd')) --order by 
 
 select * from (
 select * from a 
 
 union all 
  select '合计',a.spmtype,
         sum(a.hzSL),
         sum(a.hzzL),
         sum(a.RZHYSL),
         sum(a.RZHYzL),
         sum(a.RZqYSL),
         sum(a.RZqYzL),
         sum(a.XCSL),
         sum(a.XCzL),
         sum(a.TGSL),
         sum(a.TGzL),
         sum(a.SZSL),
         sum(a.SZzL),
         sum(a.LTSL),
         sum(a.LTzL)
    from a group by a.spmtype
					  ) order by ht asc
    
		]]>
	</query>
	
	<query id="UIM070060_01.SELECT" desc="平整指定">
		<![CDATA[
		SELECT T.COILNO,
       T.SPMTYPE,
       T.ORDNO,
       T.ORDSEQ,
       T.ORDSEQ,
       T.ACTWGT,
       T.REGID,
       TO_CHAR(T.RTIME, 'YYYY-MM-DD HH24:MI:SS') RTIME
  FROM SPM_REPORT T
 WHERE T.COILNO LIKE ?||'%'
   AND T.ORDNO LIKE ?||'%'
   AND TO_CHAR(T.RTIME, 'YYYYMMDD') BETWEEN ? AND ?
   ORDER BY T.COILNO, T.SPMTYPE,  T.ORDNO
		]]>
	</query>
</queryMap>