月结数据流水平衡排查 数据库 月结报表

lake 2020-1-2 1823

 WITH beginStock AS
       (SELECT    t.barcode, 
                  t.quantity, 
                  nvl(t.wh_to_id, '-1') as wh_id, 
                  t.material_id
        FROM      tb_usc_material_flow t
       WHERE      t.del_flag = 'N'
         AND      t.barcode is not null
      UNION ALL
      SELECT      js.barcode,
                  -js.jit_stock_sum_qty as quantity,
                  nvl(js.wh_id, '-1'),
                  js.material_id
        FROM      tb_usc_jit_stock js

       WHERE      js.del_flag = 'N'
         AND      js.barcode is not null
       )
        SELECT distinct t.whId   "whId"
         FROM (select barcode, 
                      sum(quantity) quantity,
                      wh_id as whId,
                      material_id
                 from beginStock
                group by barcode,wh_id,material_id) t
        WHERE t.quantity != 0

WITH  beginStock AS(
          SELECT    t.material_id,
                    t.quantity, t.monthly_bal_id
          FROM      tb_usc_monthly_bal_flw t 
          WHERE     t.del_flag = 'N' 

          UNION ALL
          SELECT    js.material_id,
                    -js.jit_stock_sum_qty as quantity, js.monthly_bal_id
          FROM      tb_usc_monthly_bal_end_jit js 
          WHERE     js.del_flag = 'N' 

        UNION ALL
        SELECT    fs.material_id,
                  fs.jit_stock_sum_qty as quantity,fs.monthly_bal_id
        FROM      tb_usc_monthly_bal_begin_jit fs 
        WHERE     fs.del_flag = 'N' 

        )
             SELECT  t.material_id    "materialId"
         FROM (select material_id, 
                      sum(quantity) quantity
               from beginStock 
               group by material_id,monthly_bal_id) t
         WHERE t.quantity != 0   

drop table tb_usc_monthly_balance_dtl_flw
drop table tb_usc_monthly_balance_dtl_jit
drop table tb_usc_monthly_balance_mst
最新回复 (0)
全部楼主
返回