XinYu_LZMES/.svn/pristine/69/69a1d9c8f3d2e193978d66a1f0a1e2adb7405bb3.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="热轧库存">
	<query id="UIM070021_01bak.SELECT"  desc="库存查询">
		<![CDATA[
							
			SELECT INVID,
       sum(nvl(actwgt, BETRANSWGT)) ASUM,
       max(ALLOYNO) ALLOYNO,
       max(INVTHICK)  INVTHICK,
       max(INVWIDTH) INVWIDTH,
       max(PRODNAME) PRODNAME,
       max(ORDERITEM),
       max(PRODTYPENO) PRODTYPENO,
       max(ACCTDATE) ACCTDATE,
       max(CREATEID) CREATEID,
       max(INSTDISP) INSTDISP
  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 AND staus='N')
                    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 between :3 and :4
                                           AND staus='N'
                                         UNION ALL
                                         
                                         SELECT 1,
                                                '期初库存',
                                                H.OLD_SAMPL_NO,
                                                TO_CHAR(H.ACT_WGT / 1000),
                                                '2022-04-13',
                                                
                                                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
                                          where h.trnf_dtime =:5 --只查询当月期初
                                         ), 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 between :6 and :7
                                                   AND staus='N')
         SELECT TO_CHAR(A.BETRANSWGT) ACTWGT, B.*
           FROM A, B
          WHERE B.INVID = A.INVID(+)
         UNION ALL
         SELECT *
           FROM C) ZZ
          group by invid
         having sum(nvl(actwgt, BETRANSWGT)) <> '0'
					   
		]]>
	</query>
	<query id="UIM070021_01.SELECT"  desc="库存查询">
		<![CDATA[
							
			SELECT INVID,
       sum(BETRANSWGT) ASUM,
       max(ALLOYNO) ALLOYNO,
       max(INVTHICK) INVTHICK,
       max(INVWIDTH) INVWIDTH,
       max(PRODNAME) PRODNAME,
       max(ORDERITEM) ORDERITEM,
       max(PRODTYPENO) PRODTYPENO,
       max(ACCTDATE) ACCTDATE,
       max(CREATEID) CREATEID,
       max(INSTDISP) INSTDISP
  FROM (WITH A 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.Acctdate between :1 and :2
                                            AND staus = 'N'
                                         UNION ALL
                                         
                                         SELECT 1,
                                                '期初库存',
                                                H.OLD_SAMPL_NO,
                                                TO_CHAR(H.ACT_WGT / 1000),
                                                '2022-04-13',
                                                
                                                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
                                          where h.trnf_dtime = :3 --只查询当月期初
                                         ), 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 * -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 :4 and :5
                                                     AND staus = 'N')
         SELECT  *
           FROM  A
         UNION ALL
         SELECT *
           FROM B) ZZ
          group by invid
         having sum( BETRANSWGT) <> '0'
					   
		]]>
	</query>
	
	
	
	<query id="UIM070021_02.SELECT"  desc="交易档查询">
		<![CDATA[
				
	select * from (
SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) ACTIVITYID,
       'False' chk,
       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,
       t.priguid
  FROM MATAL_DETAIL_inACCOUNTING T
 WHERE T.INVID like ? || '%' and t.staus='N'
 

union all
SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) ACTIVITYID,
          'False' chk,
       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,
       t.priguid
  FROM MATAL_DETAIL_outACCOUNTING T
 WHERE T.INVID like ? || '%' and t.staus='N'

union all

SELECT '期初库存',
   'False' chk,
       H.OLD_SAMPL_NO,
       TO_CHAR(H.ACT_WGT / 1000),
       '2022-04-13',
       
       H.SPEC_STL_GRD,
       '热轧',
       h.ord_no,
       'N',
       '',
       'HCR',
       '20220413',
       '',
       '',
       TO_CHAR(H.COIL_LEN),
       TO_CHAR(H.COIL_THK),
       TO_CHAR(H.COIL_WTH),
       '',
       H.SLAB_NO,
       ''
  FROM TBH02_COIL_STOCK H
 WHERE h.old_sampl_no like ? || '%') order by CREATETIME asc
                                                 
     ]]>
	</query>
	
	
	<query id="UIM070021_04.SELECT"  desc="查询一体化表的履历">
		<![CDATA[
	SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) HD,
       T.INVID,
       T.DATATYPEIDX,
       T.APPID,
       T.ACTIVITYID,
       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_ACCOUNTING T
 WHERE T.Invid like ?||'%' order by T.CREATETIME asc
     ]]>
	</query>
	<query id="UIM070021_31.SELECT"  desc="实点库存比对（包含空中、互相不在库）">
		<![CDATA[
				
select cl.ACT_WGT / 1000 - TEMP.ASUM EFLAG,
       cl.OLD_SAMPL_NO,
       cl.ACT_WGT / 1000 ACT_WGT,
       temp.*
  from (SELECT INVID,
               sum(BETRANSWGT) ASUM,
               max(ALLOYNO) ALLOYNO,
               max(INVTHICK) INVTHICK,
               max(INVWIDTH) INVWIDTH,
               max(ORDERITEM) ORDERITEM,
               max(PRODTYPENO) PRODTYPENO,
               max(ACCTDATE) ACCTDATE,
               max(CREATEID) CREATEID,
               max(INSTDISP) INSTDISP
          FROM (WITH A 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.Acctdate between  ? and ?
                              AND staus = 'N'
                           UNION ALL
                           
                           SELECT 1,
                                  '期初库存',
                                  H.OLD_SAMPL_NO,
                                  TO_CHAR(H.ACT_WGT / 1000),
                                  '2022-04-13',
                                  
                                  H.SPEC_STL_GRD,
                                  '热轧',
                                  h.ord_no,
                                  'N',
                                  '',
                                  'HCR',
                                  '20220424',
                                  '',
                                  '',
                                  TO_CHAR(H.COIL_LEN),
                                  TO_CHAR(H.COIL_THK),
                                  TO_CHAR(H.COIL_WTH),
                                  '',
                                  H.SLAB_NO
                             FROM TBH02_COIL_STOCK H
                            where h.trnf_dtime = ?), 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 * -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
                                                                           ? and ?
                                                                       AND staus = 'N')
                 SELECT *
                   FROM A
                 UNION ALL
                 SELECT *
                   FROM B) ZZ
                  group by invid
                 having sum(BETRANSWGT) <> '0'
        ) temp,
       (select *
          from tbh02_coil_comm cl
         where cl.COIL_STAT = '2'
           AND cl.COIL_WTH >= NVL('', '00000')
           AND cl.COIL_WTH <= NVL('', '99999')
           AND cl.COIL_THK >= NVL('', '000')
           AND cl.COIL_THK <= NVL('', '999')
       
           ) cl
 WHERE cl.OLD_SAMPL_NO = temp.INVID(+)
union



select cl.ACT_WGT / 1000 - TEMP.ASUM EFLAG,
       cl.OLD_SAMPL_NO,
       cl.ACT_WGT / 1000 ACT_WGT,
       temp.*
  from (SELECT INVID,
               sum(BETRANSWGT) ASUM,
               max(ALLOYNO) ALLOYNO,
               max(INVTHICK) INVTHICK,
               max(INVWIDTH) INVWIDTH,
               max(ORDERITEM) ORDERITEM,
               max(PRODTYPENO) PRODTYPENO,
               max(ACCTDATE) ACCTDATE,
               max(CREATEID) CREATEID,
               max(INSTDISP) INSTDISP
          FROM (WITH A 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.Acctdate between  ? and ?
                              AND staus = 'N'
                           UNION ALL
                           
                           SELECT 1,
                                  '期初库存',
                                  H.OLD_SAMPL_NO,
                                  TO_CHAR(H.ACT_WGT / 1000),
                                  '2022-04-13',
                                  
                                  H.SPEC_STL_GRD,
                                  '热轧',
                                  h.ord_no,
                                  'N',
                                  '',
                                  'HCR',
                                  '20220424',
                                  '',
                                  '',
                                  TO_CHAR(H.COIL_LEN),
                                  TO_CHAR(H.COIL_THK),
                                  TO_CHAR(H.COIL_WTH),
                                  '',
                                  H.SLAB_NO
                             FROM TBH02_COIL_STOCK H
                            where h.trnf_dtime = ?), 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 * -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
                                                                           ? and ?
                                                                           
                                                                       AND staus = 'N')
                 SELECT *
                   FROM A
                 UNION ALL
                 SELECT *
                   FROM B) ZZ
                  group by invid
                 having sum(BETRANSWGT) <> '0'
        ) temp,
       (select *
          from tbh02_coil_comm cl
         where (cl.COIL_STAT = '2' AND cl.COIL_WTH >= NVL('', '00000') AND
               cl.COIL_WTH <= NVL('', '99999') AND
               cl.COIL_THK >= NVL('', '000') AND
               cl.COIL_THK <= NVL('', '999'))
            OR CL.OLD_SAMPL_NO IN (
                                   
                                   select T.OLD_SAMPL_NO
                                     fROM c_tbk02_coil_comm t
                                    where t.CUR_PROG_CD = 'PAB'
                                      AND T.COIL_STAT = '1')
        
        ) cl
 WHERE temp.INVID = cl.OLD_SAMPL_NO(+)
 
                                                 
     ]]>
	</query>
	
	<query id="UIM070021_03.SELECT"  desc="实点库存核对">
		<![CDATA[
	
select cl.ACT_WGT / 1000 - TEMP.ASUM EFLAG,
       cl.OLD_SAMPL_NO,
       cl.ACT_WGT / 1000 ACT_WGT,
       temp.*
  from (SELECT INVID,
               sum(BETRANSWGT) ASUM,
               max(ALLOYNO) ALLOYNO,
               max(INVTHICK) INVTHICK,
               max(INVWIDTH) INVWIDTH,
               max(ORDERITEM) ORDERITEM,
               max(PRODTYPENO) PRODTYPENO,
               max(ACCTDATE) ACCTDATE,
               max(CREATEID) CREATEID,
               max(INSTDISP) INSTDISP
          FROM (WITH A 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.Acctdate between ? and ?
                              AND staus = 'N'
                           UNION ALL
                           
                           SELECT 1,
                                  '期初库存',
                                  H.OLD_SAMPL_NO,
                                  TO_CHAR(H.ACT_WGT / 1000),
                                  '2022-04-13',
                                  
                                  H.SPEC_STL_GRD,
                                  '热轧',
                                  h.ord_no,
                                  'N',
                                  '',
                                  'HCR',
                                  '20220424',
                                  '',
                                  '',
                                  TO_CHAR(H.COIL_LEN),
                                  TO_CHAR(H.COIL_THK),
                                  TO_CHAR(H.COIL_WTH),
                                  '',
                                  H.SLAB_NO
                             FROM TBH02_COIL_STOCK H
                            where h.trnf_dtime = ?), 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 * -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
                                                                          ? and ?
                                                                       AND staus = 'N')
                 SELECT *
                   FROM A
                 UNION ALL
                 SELECT *
                   FROM B) ZZ
                  group by invid
                 having sum(BETRANSWGT) <> '0'
        ) temp,
       (select *
          from tbh02_coil_comm cl
         where cl.COIL_STAT = '2'
           AND cl.COIL_WTH >= NVL('', '00000')
           AND cl.COIL_WTH <= NVL('', '99999')
           AND cl.COIL_THK >= NVL('', '000')
           AND cl.COIL_THK <= NVL('', '999')
       
           ) cl
 WHERE cl.OLD_SAMPL_NO = temp.INVID(+)

 ORDER BY cl.ACT_WGT / 1000 - TEMP.ASUM
     ]]>
	</query>
	<query id="UIM070021_04.SELECT"  desc="查询一体化表的履历">
		<![CDATA[
	SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) HD,
       T.INVID,
       T.DATATYPEIDX,
       T.APPID,
       T.ACTIVITYID,
       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_ACCOUNTING T
 WHERE T.Invid like ?||'%' order by T.CREATETIME asc
     ]]>
	</query>
	<query id="UIM070021_05.SELECT"  desc="异常交易档查询">
		<![CDATA[
				
	select * from (
SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) ACTIVITYID,
          'False' chk,
       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,
       t.priguid
  FROM MATAL_DETAIL_inACCOUNTING T
 WHERE T.INVID like ? || '%' and t.staus='D'

union all
SELECT PKG_QUALITY_COMM.FZ00_COMM('A01016', T.ACTIVITYID) ACTIVITYID,
          'False' chk,
       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,
       t.priguid
  FROM MATAL_DETAIL_outACCOUNTING T
 WHERE T.INVID like ? || '%' and t.staus='D'
) order by CREATETIME asc
                                                 
     ]]>
	</query>
	
	
	<query id="UIM070021_06.SELECT"  desc="酸轧原料库存查询">
		<![CDATA[
							
			SELECT INVID,
       sum(BETRANSWGT) ASUM,
       max(ALLOYNO) ALLOYNO,
       max(INVTHICK) INVTHICK,
       max(INVWIDTH) INVWIDTH,
       max(PRODNAME) PRODNAME,
       max(ORDERITEM) ORDERITEM,
       max(PRODTYPENO) PRODTYPENO,
       max(ACCTDATE) ACCTDATE,
       max(CREATEID) CREATEID,
       max(INSTDISP) INSTDISP
  FROM (WITH A 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 szyl_inaccounting T
                                          WHERE T.Acctdate between :1 and :2
                                            AND staus = 'N'
                                         UNION ALL
                                         
                                         SELECT 1,
                                                '期初库存',
                                                H.OLD_SAMPL_NO,
                                                TO_CHAR(H.ACT_WGT / 1000),
                                                '2022-04-13',
                                                
                                                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 C_TBk02_STOCK H
                                          where h.trnf_dtime = :3 --只查询当月期初
                                         ), 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 * -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 szyl_outaccounting T
                                                   WHERE T.Acctdate between :4 and :5
                                                     AND staus = 'N')
         SELECT  *
           FROM  A
         UNION ALL
         SELECT *
           FROM B) ZZ
          group by invid
         having sum( BETRANSWGT) <> '0'
					   
		]]>
	</query>
	
	<query id="UIM070021_07.SELECT"  desc="酸轧原料实点库存核对">
		<![CDATA[
	
select cl.ACT_WGT / 1000 - TEMP.ASUM EFLAG,
       cl.OLD_SAMPL_NO,
       cl.ACT_WGT / 1000 ACT_WGT,
       temp.*
  from (SELECT INVID,
               sum(BETRANSWGT) ASUM,
               max(ALLOYNO) ALLOYNO,
               max(INVTHICK) INVTHICK,
               max(INVWIDTH) INVWIDTH,
               max(ORDERITEM) ORDERITEM,
               max(PRODTYPENO) PRODTYPENO,
               max(ACCTDATE) ACCTDATE,
               max(CREATEID) CREATEID,
               max(INSTDISP) INSTDISP
          FROM (WITH A 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 szyl_inaccounting T
                            WHERE T.Acctdate between ? and ?
                              AND staus = 'N'
                           UNION ALL
                           
                           SELECT 1,
                                  '期初库存',
                                  H.OLD_SAMPL_NO,
                                  TO_CHAR(H.ACT_WGT / 1000),
                                  '2022-04-13',
                                  
                                  H.SPEC_STL_GRD,
                                  '热轧',
                                  h.ord_no,
                                  'N',
                                  '',
                                  'HCR',
                                  '20220424',
                                  '',
                                  '',
                                  TO_CHAR(H.COIL_LEN),
                                  TO_CHAR(H.COIL_THK),
                                  TO_CHAR(H.COIL_WTH),
                                  '',
                                  H.SLAB_NO
                             FROM C_tbk02_stock H
                            where h.trnf_dtime = ?), 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 * -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 szyl_OUTaccounting T
                                                                     WHERE T.Acctdate between
                                                                          ? and ?
                                                                       AND staus = 'N')
                 SELECT *
                   FROM A
                 UNION ALL
                 SELECT *
                   FROM B) ZZ
                  group by invid
                 having sum(BETRANSWGT) <> '0'
        ) temp,
       (select *
          from c_tbk02_coil_comm cl
         where cl.COIL_STAT = '2'
           AND cl.CUR_LOAD_LOC like '1%'
       
           ) cl
 WHERE cl.OLD_SAMPL_NO = temp.INVID(+)
 ORDER BY cl.ACT_WGT / 1000 - TEMP.ASUM
     ]]>
	</query>
	
	<query id="UIM070021_08.SELECT"  desc="酸轧库存查询">
		<![CDATA[
							
			SELECT INVID,
       sum(BETRANSWGT) ASUM,
       max(ALLOYNO) ALLOYNO,
       max(INVTHICK) INVTHICK,
       max(INVWIDTH) INVWIDTH,
       max(PRODNAME) PRODNAME,
       max(ORDERITEM) ORDERITEM,
       max(PRODTYPENO) PRODTYPENO,
       max(ACCTDATE) ACCTDATE,
       max(CREATEID) CREATEID,
       max(INSTDISP) INSTDISP
  FROM (WITH A 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_SZ T
                                          WHERE T.Acctdate between :1 and :2
                                            AND staus = 'N'
                                         UNION ALL
                                         
                                         SELECT 1,
                                                '期初库存',
                                                H.OLD_SAMPL_NO,
                                                TO_CHAR(H.ACT_WGT / 1000),
                                                '2022-04-13',
                                                
                                                H.SPEC_STL_GRD,
                                                '酸轧成品',
                                                h.ord_no,
                                                'N',
                                                '',
                                                'CCR',
                                                '20211013',
                                                '',
                                                '',
                                                TO_CHAR(H.COIL_LEN),
                                                TO_CHAR(H.COIL_THK),
                                                TO_CHAR(H.COIL_WTH),
                                                '',
                                                H.SLAB_NO
                                           FROM C_TBL02_STOCK H
                                          where h.trnf_dtime = :3 --只查询当月期初
                                         ), 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 * -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_SZ T
                                                   WHERE T.Acctdate between :4 and :5
                                                     AND staus = 'N')
         SELECT  *
           FROM  A
         UNION ALL
         SELECT *
           FROM B) ZZ
          group by invid
         having sum( BETRANSWGT) <> '0'
					   
		]]>
	</query>
	
	<query id="UIM070021_09.SELECT"  desc="酸轧成品实点库存核对">
		<![CDATA[
	
select cl.ACT_WGT / 1000 - TEMP.ASUM EFLAG,
       cl.OLD_SAMPL_NO,
       cl.ACT_WGT / 1000 ACT_WGT,
       temp.*
  from (SELECT INVID,
               sum(BETRANSWGT) ASUM,
               max(ALLOYNO) ALLOYNO,
               max(INVTHICK) INVTHICK,
               max(INVWIDTH) INVWIDTH,
               max(ORDERITEM) ORDERITEM,
               max(PRODTYPENO) PRODTYPENO,
               max(ACCTDATE) ACCTDATE,
               max(CREATEID) CREATEID,
               max(INSTDISP) INSTDISP
          FROM (WITH A 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_SZ T
                            WHERE T.Acctdate between ? and ?
                              AND staus = 'N'
                           UNION ALL
                           
                           SELECT 1,
                                  '期初库存',
                                  H.OLD_SAMPL_NO,
                                  TO_CHAR(H.ACT_WGT / 1000),
                                  '2022-04-13',
                                  
                                  H.SPEC_STL_GRD,
                                  '酸轧成品',
                                  h.ord_no,
                                  'N',
                                  '',
                                  'HCR',
                                  '20220424',
                                  '',
                                  '',
                                  TO_CHAR(H.COIL_LEN),
                                  TO_CHAR(H.COIL_THK),
                                  TO_CHAR(H.COIL_WTH),
                                  '',
                                  H.SLAB_NO
                             FROM C_tbL02_stock H
                            where h.trnf_dtime = ?), 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 * -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_SZ T
                                                                     WHERE T.Acctdate between ? and ?
                                                                       AND staus = 'N')
                 SELECT *
                   FROM A
                 UNION ALL
                 SELECT *
                   FROM B) ZZ
                  group by invid
                 having sum(BETRANSWGT) <> '0'
        ) temp,
       (select *
  from C_TBL02_COIL_COMM 
 WHERE COIL_STAT = '2'
   AND NVL(LINE_TP, '0') <> 'S' --屏蔽酸洗线 20171222
   AND NVL(EXTSHAPE_QUALITY, 'NULLP') NOT IN ('FP') ---屏蔽废次卷
   AND CUR_LOAD_LOC LIKE '' || '%'
           ) cl
 WHERE cl.OLD_SAMPL_NO = temp.INVID(+)
 ORDER BY cl.ACT_WGT / 1000 - TEMP.ASUM
     ]]>
	</query>
</queryMap>