create or replace procedure fentan is
  var_apportion_lot inventory_transaction.apportion_lot%type;
  last_id inventory_transaction.id%type;
begin

  --return;
  select max(id) into last_id from INVENTORY_TRANSACTION2;
  var_apportion_lot := to_char(sysdate, 'yyyyMMddHH24miss');
  /*轧差为0的先打分摊标记*/
  update INVENTORY_TRANSACTION2 tt
     set apportion_lot = var_apportion_lot || '轧差为0', apportion_flag = 'Y'
   where bjsj >= sysdate - 30
     and APPORTION_FLAG <> 'Y'
     and exists (select 1
            from fentan_lot x, shop_order s
           where s.shop_order = x.shop_order
             and x.shop_order_inv = tt.itremark)
     and uuid in (with a as
                  (SELECT uuid, sum(QTY) over (PARTITION BY ITREMARK, item) sqty
                     FROM INVENTORY_TRANSACTION2 it
                    WHERE bjsj >= sysdate - 30
                      and APPORTION_FLAG <> 'Y'
                      and exists
                    (select 1
                             from fentan_lot x, shop_order s
                            where s.shop_order = x.shop_order
                              and x.shop_order_inv = it.itremark))
                   select uuid from a where sqty = 0
                  );
  /*提取待摊UUID*/
  delete FENTAN_UUID;
  INSERT INTO FENTAN_UUID
    (uuid, ITREMARK, item)
    SELECT uuid, ITREMARK, item
      FROM INVENTORY_TRANSACTION2 it
     WHERE bjsj >= sysdate - 30
       and APPORTION_FLAG <> 'Y'
       and exists (select 1
              from fentan_lot x, shop_order s
             where s.shop_order = x.shop_order
               and x.shop_order_inv = it.itremark);
  /*分摊到临时表*/
  /*create table INVENTORY_TRANSACTION_temp as*/
  delete INVENTORY_TRANSACTION_temp;
  insert into INVENTORY_TRANSACTION_temp
    with a as /*全部待摊*/
     (SELECT ITREMARK, ITEM, QTY
        FROM INVENTORY_TRANSACTION2 it
       WHERE APPORTION_FLAG <> 'Y'
         and exists
       (select 1 from FENTAN_UUID x where x.uuid = it.uuid)),
    b as /*按发料范围汇总*/
     (select ITREMARK, ITEM, sum(QTY) sqty
        from a
       group by ITREMARK, ITEM
      having abs(sum(QTY)) <> 0),
    c as /*匹配工单号*/
     (select b.*, t2.shop_order, row_number() over (partition by ITREMARK, ITEM order by SHOP_ORDER) seq
        from b, fentan_lot t2
       where b.ITREMARK = t2.shop_order_inv),
    d as /*查询消耗量*/
     (select SHOP_ORDER, ITEM, sum(QTY) sqty2
        from STAND_ASSY t9
       where exists (select 1
                from c
               where c.SHOP_ORDER = t9.SHOP_ORDER
                 and c.ITEM = t9.ITEM)
       group by SHOP_ORDER, ITEM),
    e as /*计算消耗量比例*/
     (select c.*, d.sqty2, d.sqty2 / SUM(d.sqty2) OVER (PARTITION BY c.ITREMARK, d.ITEM) pp
        from c, d
       where c.SHOP_ORDER = d.SHOP_ORDER
         and c.ITEM = d.ITEM),
    f as
     (select distinct site, SHOP_ORDER, COMPONENT_GBO, SALES_ORDER so_num, SO_LINE, WBS wbs_num, unit, Q_OR_E
        from bom t7
       where exists (select 1
                from d
               where d.SHOP_ORDER = t7.SHOP_ORDER
                 and d.ITEM = t7.component_gbo)),
    g as
     (select e.SHOP_ORDER, ITREMARK, ITEM, sqty,CASE
               WHEN MAX(e.seq) OVER(PARTITION BY e.ITREMARK, e.ITEM) = 1 THEN
                e.sqty
               WHEN e.seq = 1 THEN
                e.sqty -
                NVL(SUM(ROUND(e.sqty * e.pp, 3))
                    OVER(PARTITION BY e.ITREMARK, e.ITEM ORDER BY e.seq
                          ROWS BETWEEN 1
                          FOLLOWING AND UNBOUNDED
                          FOLLOWING), 0)
               ELSE
                ROUND(e.sqty * e.pp, 3)
             END AS qty, so_num, SO_LINE, wbs_num, site, unit, Q_OR_E sobkz,case
               when e.shop_order is not null and sqty < 0 then
                'Z13'
               when e.shop_order is not null and sqty > 0 then
                'Z14'
               else
                null
             end transaction_reason_code,case
               when e.shop_order is not null and sqty < 0 then
                'NONBOM_ASSEMBLE'
               when e.shop_order is not null and sqty > 0 then
                'NONBOM_ASSEMBLE_REVERSE'
               else
                null
             end transaction_type
        from e, f
       where f.shop_order = e.shop_order
         and f.component_gbo = e.item)
    select * from g;

  /*分摊结果写入表1*/
  insert into INVENTORY_TRANSACTION
    (SHOP_ORDER, ITREMARK, ITEM, QTY, SO_NUM, SO_LINE, WBS_NUM, SITE, UNIT, APPORTION_LOT, SOBKZ, TRANSACTION_REASON_CODE, TRANSACTION_TYPE, warehouse, system, transaction_time, account_date, created_date_time, data_from, created_user)
    select SHOP_ORDER, ITREMARK, ITEM, abs(QTY) qty, SO_NUM, SO_LINE, WBS_NUM, SITE, UNIT, var_apportion_lot APPORTION_LOT, SOBKZ, TRANSACTION_REASON_CODE, TRANSACTION_TYPE, site warehouse, 'MES' system, sysdate transaction_time, sysdate account_date, sysdate created_date_time, 'c#fentan' data_from, '20151763' created_user
      from INVENTORY_TRANSACTION_temp last;

  --删除分摊不到工单的uuid
  DELETE fentan_uuid t
   WHERE NOT EXISTS (SELECT 1
            FROM INVENTORY_TRANSACTION_TEMP x
           WHERE x.ITEM = t.item
             AND x.ITREMARK = t.ITREMARK);
  /*更新分摊标识*/
  update inventory_transaction2 x
     set apportion_lot = var_apportion_lot, apportion_flag = 'Y'
   where exists (select 1 from FENTAN_UUID u where x.uuid = u.uuid);
  commit;
end fentan;

create or replace procedure oldfentan is
  var_apportion_lot inventory_transaction.apportion_lot%type;
begin
  var_apportion_lot := to_char(sysdate, 'yyyyMMddHH24miss');
  delete fentan_list;
  insert into fentan_list
    (WAREHOUSE, INVENTORY, ITEM, UNIT, SO_NUM, SO_LINE_NUM, WBS_NUM, QTY, CREATED_DATE_TIME, SITE, ID)
    select x.warehouse, x.inventory, x.item, x.unit, inv.so_num, inv.so_line_num, inv.wbs_num, x.qty, x.created_date_time, x.site, x.id
      from inventory_transaction2 x, inventory inv
     where inv.inventory = x.inventory
       and inv.site = x.site
       and x.apportion_flag <> 'Y';
  delete fentan_temp;
  insert into fentan_temp
    (key, item, unit, so_num, so_line_num, wbs_num, warehouse, diffqty, shop_order, created_date_time, useqty, sobkz, qty, site, work_center, seq, cost_center)
    with a as /*查所有差异明细*/
     (select warehouse, inventory, item, unit, so_num, so_line_num, wbs_num, qty, created_date_time, site, id
        from fentan_list),
    a1 as /*差异明细汇总*/
     (select site || item || warehouse || nvl(so_num, 'A') ||
              nvl(so_line_num, 0) || nvl(wbs_num, 'A') handle, site, item, warehouse, unit, so_num, so_line_num, wbs_num, sum(qty) diffqty /*差异数量*/
        from a
       group by site || item || warehouse || nvl(so_num, 'A') ||
                 nvl(so_line_num, 0) || nvl(wbs_num, 'A'), site, item, warehouse, unit, so_num, so_line_num, wbs_num
      having sum(qty) <> 0),
    a2 as /*差异明细汇总*/
     (select rownum key, a1.*
        from a1
       where diffqty >= 0.001
          or diffqty <= -0.001),
    t1 as /*查物料在本仓上次盘点时间*/
     (select x.site || x.item || x.warehouse || nvl(inv.so_num, 'A') ||
              nvl(inv.so_line_num, 0) || nvl(inv.wbs_num, 'A') handle, x.item, x.warehouse, inv.so_num, inv.so_line_num, inv.wbs_num, max(x.created_date_time) maxcreated_date_time
        from inventory_transaction2 x
       inner join inventory inv
          on inv.inventory = x.inventory
         and inv.site = x.site
       where exists (select 1
                from a2
               where x.site || x.item || x.warehouse ||
                     nvl(inv.so_num, 'A') || nvl(inv.so_line_num, 0) ||
                     nvl(inv.wbs_num, 'A') = a2.handle)
         and x.apportion_flag = 'Y'
       group by x.site || x.item || x.warehouse || nvl(inv.so_num, 'A') ||
                 nvl(inv.so_line_num, 0) || nvl(inv.wbs_num, 'A'), x.item, x.warehouse, inv.so_num, inv.so_line_num, inv.wbs_num),
    a3 as /*差异合计串查上次盘点时间*/
     (select /*+ materialize*/
       a2.*, nvl2(trim(a2.so_num), 'E', nvl2(trim(a2.wbs_num), 'Q', null)) sobkz, nvl2(t1.maxcreated_date_time, greatest(t1.maxcreated_date_time, trunc(sysdate, 'mm')), trunc(sysdate, 'mm')) previous_check_time /*上次盘点时间*/
        from a2
        left join t1
          on t1.handle = a2.handle),
    b as /*差异合计串查上次盘点时间  串查物料消耗明细*/
     (select /*+ materialize*/
       a3.key, a3.item, a3.unit, a3.so_num, a3.so_line_num, a3.wbs_num, a3.warehouse, a3.diffqty /*差异数量*/, sa.shop_order, sa.created_date_time /*投料时间*/, sa.qty useqty /*工单投料数量*/, a3.sobkz, round(sa.qty /
              sum(sa.qty)
              over(partition by
                   a3.key) *
              a3.diffqty, 3) qty, a3.site, sa.work_center
        from a3, stand_assy sa, bom b1, shop_order so
       where b1.reserved_number = sa.reserved_number
         and b1.reserved_line = sa.reserved_line
         and so.shop_order = b1.shop_order
         and sa.site || sa.item || sa.warehouse ||
             nvl2(q_or_e, nvl(so.sales_order, 'A'), 'A') ||
             nvl2(q_or_e, nvl(so.so_line, 0), 0) ||
             nvl2(q_or_e, nvl(so.wbs, 'A'), 'A') = a3.handle
         and sa.created_date_time >= a3.previous_check_time),
    c as
     (select key, item, unit, so_num, so_line_num, wbs_num, warehouse, diffqty, shop_order, created_date_time, useqty, sobkz, qty, site, work_center, row_number() over (partition by key order by created_date_time) seq, sum(qty) over (partition by key) sumqty
        from b
      union all
      select key, item, unit, so_num, so_line_num, wbs_num, warehouse, diffqty, null shop_order, null created_date_time, 0 useqty, sobkz, diffqty qty, site, null work_center, 1 seq, diffqty sumqty
        from a3
       where not exists (select 1 from b where a3.key = b.key))
    select c.key, c.item, c.unit, c.so_num, c.so_line_num, c.wbs_num, c.warehouse, c.diffqty, c.shop_order, c.created_date_time, c.useqty, c.sobkz,case
             when seq = 1 and sumqty <> diffqty then
              c.qty + (diffqty - sumqty) /*分摊后的小数位差异加到第一行上*/
             else
              c.qty
           end qty, c.site, c.work_center, c.seq, w.default_cost_center cost_center
      from c
      join warehouse w
        on w.warehouse = c.warehouse
     order by key, seq;
  insert into inventory_transaction
    (site, system, transaction_reason_code, transaction_type, transaction_time, account_date, shop_order, work_center, costcenter_code, warehouse, inventory, item, qty, unit, sobkz, so_num, so_line, wbs_num, reserved_number, reserved_line, data_from, itremark, created_user, created_date_time, apportion_lot)
    select site, 'MES' system,case
             when shop_order is not null and qty < 0 then
              'Z13'
             when shop_order is not null and qty > 0 then
              'Z14'
             when shop_order is null and so_num is not null and qty < 0 then
              'Z23'
             when shop_order is null and so_num is not null and qty > 0 then
              'Z24'
             when shop_order is null and wbs_num is not null and qty < 0 then
              'Z21'
             when shop_order is null and wbs_num is not null and qty > 0 then
              'Z22'
             when shop_order is null and sobkz is null and qty < 0 then
              'Z01'
             when shop_order is null and sobkz is null and qty > 0 then
              'Z02'
             else
              null
           end transaction_reason_code,case
             when shop_order is not null and qty < 0 then
              'NONBOM_ASSEMBLE'
             when shop_order is not null and qty > 0 then
              'NONBOM_ASSEMBLE_REVERSE'
             when shop_order is null and so_num is not null and qty < 0 then
              'INVENTORY_LOSS_E'
             when shop_order is null and so_num is not null and qty > 0 then
              'INVENTORY_ADD_E'
             when shop_order is null and wbs_num is not null and qty < 0 then
              'WBS_OUT'
             when shop_order is null and wbs_num is not null and qty > 0 then
              'WBS_IN'
             when shop_order is null and sobkz is null and qty < 0 then
              'INVENTORY_LOSS'
             when shop_order is null and sobkz is null and qty > 0 then
              'INVENTORY_ADD'
             else
              null
           end transaction_type, sysdate transaction_time, sysdate account_date, shop_order, work_center, cost_center costcenter_code, site warehouse, null inventory, item, abs(qty) qty, unit, sobkz, so_num, case
             so_line_num
              when 0 then
               null
              else
               so_line_num
            end so_line_num, wbs_num, null reserved_number, null reserved_line, 'fentan_dbjob' data_from, to_char(sysdate, 'yyyymmdd') || '分摊' itremark, '20151763' created_user, sysdate created_date_time, var_apportion_lot
    
      from fentan_temp t
     where qty <> 0;
  update inventory_transaction2 x
     set apportion_lot = var_apportion_lot, apportion_flag = 'Y'
   where exists (select 1 from fentan_list f where f.id = x.id);
  commit;
end oldfentan;