库房正常业务,有次未在测试环境就直接更改了生产环境的库房费用帐户,一周后发现现有量出现负值,期间已有几万条数据。
经查发现是inv.mtl_onhand_quantities 中 COST_GROUP_ID导致。
还原数据步骤:
1 删除 inv.mtl_onhand_quantities 表 new COST_GROUP_ID 数据
2 根据inv.mtl_material_transactions业务数据重新生成现有量
create table crtname.SU_INV_MOQ_B as select t.* from inv.mtl_onhand_quantities t where 1=2;
select APPS.INV_MOQ_FORDATE(32,'902', 1062, 1359, 1) from dual;
insert into crtname.SU_INV_MOQ_B select from crtname.SU_INV_MOQ;
CREATE OR REPLACE FUNCTION APPS.INV_MOQ_FORDATE(v_organization_id number,v_subinventory_code varchar2, old_cost number, new_cost number,tranid number)
RETURN number IS
/*
库存事务处理还原
苏朝军 2013.7.5
v_subinventory_code 库房代码
due_date 截止日期 (yyyy-mm-dd hh24:mi:ss)
v_organization_id 组织ID
tranid 事务ID,报表用 select jnreport.FDZL_TEMP_S.nextval from dual
exec dbms_stats.gather_table_stats('CRTNAME','SU_INV_MOQ',CASCADE=>TRUE);
***********************
** 未计算单位换算 **
***********************
---------------------
*/
last_trans_temp_in number;
last_trans_temp_out number;
curr_trans_temp number;
curr_rev_temp number;
curr_loc_temp number;
curr_item_temp number;
trans_temp number;
rev_temp number;
loc_temp number;
date_temp date;
item_temp number;
qty_temp number;
lot_temp varchar2(30);
lot_curr_trans_temp number;
lot_curr_rev_temp number;
lot_curr_loc_temp number;
lot_curr_item_temp number;
lot_curr_qty_temp number;
lot_curr_lot_temp varchar2(30);
lot_curr_trans_temp_b number;
lot_curr_rev_temp_b number;
lot_curr_loc_temp_b number;
lot_curr_item_temp_b number;
lot_curr_qty_temp_b number;
lot_curr_lot_temp_b varchar2(30);
lot_count number;
cur_moq inv.mtl_onhand_quantities%rowtype;
trans inv.mtl_material_transactions%rowtype;
last_trans inv.mtl_material_transactions%rowtype;
cursor c_cur_moq is select t.* from inv.mtl_onhand_quantities t
where t.organization_id = v_organization_id
and t.subinventory_code = v_subinventory_code
and t.cost_group_id = old_cost;
cursor c_trans is select t.* from inv.mtl_material_transactions t where t.organization_id = v_organization_id
and t.transaction_date >= to_date('2015-02-16','yyyy-mm-dd')
and t.subinventory_code = v_subinventory_code and t.cost_group_id = new_cost
order by t.transaction_id desc;
cursor c_lot_last_in is select distinct mmt.transaction_id,mmt.transaction_date,
mmt.inventory_item_id,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mtln.transaction_quantity
and sim.lot_number = mtln.lot_number
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity > 0
and mtln.lot_number = lot_curr_lot_temp_b
and nvl(mmt.revision,-1) = lot_curr_rev_temp_b
and nvl(mmt.locator_id,-1) = lot_curr_loc_temp_b
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = lot_curr_item_temp_b
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_cur_out is select distinct mmt.transaction_id,mmt.inventory_item_id,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,-mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.transaction_id = curr_trans_temp;
cursor c_lot_last_out is select distinct mmt.transaction_id,mmt.transaction_quantity,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and mmt.transaction_quantity < 0
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_last_in is select mmt.transaction_id,mmt.transaction_quantity,
mmt.transaction_date,mmt.inventory_item_id,
nvl(mmt.locator_id,-1) loc,nvl(mmt.revision,-1) rev
from inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mmt.transaction_quantity
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity > 0
and nvl(mmt.revision,-1) = curr_rev_temp
and nvl(mmt.locator_id,-1) = curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = curr_item_temp
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_last_out is select mmt.transaction_id,mmt.transaction_quantity,
mmt.transaction_date,mmt.inventory_item_id,
nvl(mmt.locator_id,-1) loc,nvl(mmt.revision,-1) rev
from inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mmt.transaction_quantity
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity < 0
and nvl(mmt.revision,-1) = curr_rev_temp
and nvl(mmt.locator_id,-1) = curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = curr_item_temp
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
n number;
crt_trans_id number;
crt_qty number;
cur_qty number;
left_qty number;
last_trans_id number;
item_id number;
moq_su_tid number;
moq_su_qty number;
moq_su_left_qty number;
na number;
nb number;
nc number;
nd number;
begin
delete from crtname.SU_INV_MOQ; --最多30个报表同时运行
commit;
open c_cur_moq;
loop
fetch c_cur_moq into cur_moq;
exit when c_cur_moq%notfound;
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (cur_moq.INVENTORY_ITEM_ID,cur_moq.ORGANIZATION_ID,
cur_moq.DATE_RECEIVED,cur_moq.LAST_UPDATE_DATE,cur_moq.LAST_UPDATED_BY,
cur_moq.CREATION_DATE,cur_moq.CREATED_BY,cur_moq.LAST_UPDATE_LOGIN,
cur_moq.TRANSACTION_QUANTITY,cur_moq.SUBINVENTORY_CODE,cur_moq.REVISION,
cur_moq.LOCATOR_ID,cur_moq.CREATE_TRANSACTION_ID,cur_moq.UPDATE_TRANSACTION_ID,
cur_moq.LOT_NUMBER,cur_moq.ORIG_DATE_RECEIVED,cur_moq.COST_GROUP_ID,
cur_moq.CONTAINERIZED_FLAG,cur_moq.PROJECT_ID,cur_moq.TASK_ID,
tranid);
end loop;
close c_cur_moq;
commit;
open c_trans;
loop
fetch c_trans into trans;
--查询是否带批次入出库
exit when c_trans%notfound;
select count(mtln.transaction_id)
into lot_count
from inv.mtl_transaction_lot_numbers mtln
where mtln.transaction_id = trans.transaction_id;
if lot_count = 0 then --无批次号
if trans.TRANSACTION_QUANTITY > 0 then --无批次入库,库存值允许为负值
select nvl(max(ta.create_transaction_id),0),nvl(max(ta.transaction_quantity),0)
into moq_su_tid,moq_su_qty
from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if moq_su_qty>0 and trans.transaction_quantity = moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif moq_su_qty>0 and trans.transaction_quantity > moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
moq_su_left_qty := trans.transaction_quantity - moq_su_qty;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_out;
fetch c_last_out into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_out;
if -qty_temp <= moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif -qty_temp > moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity > 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty <= 0;
end loop;
elsif moq_su_qty = 0 then --无法找到入库记录
moq_su_left_qty := trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_out;
fetch c_last_out into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_out;
if -qty_temp <= moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif -qty_temp > moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity > 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty <= 0;
end loop;
end if;
-----------------------------------------------------------------
elsif trans.TRANSACTION_QUANTITY < 0 then --无批次出库
select nvl(max(ta.create_transaction_id),0),nvl(max(ta.transaction_quantity),0) --查询是否有负库存
into moq_su_tid,moq_su_qty
from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if moq_su_qty<0 and trans.transaction_quantity = moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif moq_su_qty<0 and trans.transaction_quantity < moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
moq_su_left_qty := trans.transaction_quantity - moq_su_qty;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= -moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > -moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty >= 0;
end loop;
elsif moq_su_qty = 0 then --无法找到入库记录
--是否未发完
select count(ta.inventory_item_id)
into n
from crtname.SU_INV_MOQ ta
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if n=1 then
--查询总数量
select ta.create_transaction_id,ta.transaction_quantity
into crt_trans_id,cur_qty
from crtname.SU_INV_MOQ ta
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
select mmt.transaction_quantity
into crt_qty
from inv.mtl_material_transactions mmt
where mmt.transaction_id = crt_trans_id;
--更新现有量表数量
if cur_qty - trans.transaction_quantity < crt_qty then --现有量 + 出库数量 < 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = ta.transaction_quantity - trans.transaction_quantity
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
--查询最后一次记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(trans.revision,-1)
and nvl(mmt.locator_id,-1) = nvl(trans.locator_id,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = trans.inventory_item_id
and mmt.transaction_id < trans.TRANSACTION_ID;
--更新
update crtname.SU_INV_MOQ ta
set ta.update_transaction_id = last_trans_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif cur_qty - trans.transaction_quantity = crt_qty then --现有量 + 出库数量 = 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif cur_qty - trans.transaction_quantity > crt_qty then --涉及多条数据
na := crt_qty;
nb := cur_qty;
nc := trans.transaction_quantity;
nd := -nc -(na - nb);
--left_qty := (crt_qty-cur_qty) + trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
else
nd := -trans.transaction_quantity;
--left_qty := (crt_qty-cur_qty) + trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
end if;
end if;
elsif lot_count >= 1 then --有批次号
if trans.TRANSACTION_QUANTITY > 0 then --入库则直接删除
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif trans.TRANSACTION_QUANTITY < 0 then --出库有点麻烦
curr_trans_temp := trans.TRANSACTION_ID;
open c_cur_out; --本次出库分几个批次,每个批次分别哪个 货位 和 数量(负)
loop
fetch c_cur_out into lot_curr_trans_temp,lot_curr_item_temp,lot_curr_loc_temp,
lot_curr_rev_temp,lot_curr_qty_temp,lot_curr_lot_temp;
exit when c_cur_out%notfound;
--每个批次有没有出库完
select count(ta.inventory_item_id)
into n
from crtname.SU_INV_MOQ ta
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if n=1 then
--查询总数量
select ta.create_transaction_id,ta.transaction_quantity
into crt_trans_id,cur_qty
from crtname.SU_INV_MOQ ta
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
select max(mmt.transaction_quantity)
into crt_qty
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mtln.lot_number = lot_curr_lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.transaction_id = crt_trans_id;
--更新现有量表数量
if cur_qty - lot_curr_qty_temp < crt_qty then --现有量 + 出库数量 < 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = ta.transaction_quantity - lot_curr_qty_temp
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
--查询最后一次记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_curr_lot_temp
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = lot_curr_rev_temp
and nvl(mmt.locator_id,-1) = lot_curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = lot_curr_item_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.transaction_id < curr_trans_temp;
--更新
update crtname.SU_INV_MOQ ta
set ta.update_transaction_id = last_trans_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
elsif cur_qty - lot_curr_qty_temp = crt_qty then --现有量 + 出库数量 = 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
elsif cur_qty - lot_curr_qty_temp > crt_qty then --涉及多条数据 5>3
na := crt_qty; --当初入库数量 3
nb := cur_qty; --当前现有量 2
nc := lot_curr_qty_temp; --本次出库数量 (负) -3
nd := -nc -(na - nb); --2
lot_curr_trans_temp_b := lot_curr_trans_temp;
lot_curr_rev_temp_b := lot_curr_rev_temp;
lot_curr_loc_temp_b := lot_curr_loc_temp;
lot_curr_item_temp_b := lot_curr_item_temp;
lot_curr_qty_temp_b := lot_curr_qty_temp;
lot_curr_lot_temp_b := lot_curr_lot_temp;
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
loop
open c_lot_last_in;
fetch c_lot_last_in into trans_temp,date_temp,item_temp,loc_temp,rev_temp,qty_temp,lot_temp;
close c_lot_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
else
nd := -lot_curr_qty_temp;
lot_curr_trans_temp_b := lot_curr_trans_temp;
lot_curr_rev_temp_b := lot_curr_rev_temp;
lot_curr_loc_temp_b := lot_curr_loc_temp;
lot_curr_item_temp_b := lot_curr_item_temp;
lot_curr_qty_temp_b := lot_curr_qty_temp;
lot_curr_lot_temp_b := lot_curr_lot_temp;
loop
open c_lot_last_in;
fetch c_lot_last_in into trans_temp,date_temp,item_temp,loc_temp,rev_temp,qty_temp,lot_temp;
close c_lot_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
end loop;
close c_cur_out;
end if;
end if;
end loop;
close c_trans;
commit;
insert into crtname.MOQA(INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,TASK_ID)
select INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,TASK_ID
from crtname.SU_INV_MOQ;
commit;
return (0);
end;
/
经查发现是inv.mtl_onhand_quantities 中 COST_GROUP_ID导致。
还原数据步骤:
1 删除 inv.mtl_onhand_quantities 表 new COST_GROUP_ID 数据
2 根据inv.mtl_material_transactions业务数据重新生成现有量
create table crtname.SU_INV_MOQ_B as select t.* from inv.mtl_onhand_quantities t where 1=2;
select APPS.INV_MOQ_FORDATE(32,'902', 1062, 1359, 1) from dual;
insert into crtname.SU_INV_MOQ_B select from crtname.SU_INV_MOQ;
CREATE OR REPLACE FUNCTION APPS.INV_MOQ_FORDATE(v_organization_id number,v_subinventory_code varchar2, old_cost number, new_cost number,tranid number)
RETURN number IS
/*
库存事务处理还原
苏朝军 2013.7.5
v_subinventory_code 库房代码
due_date 截止日期 (yyyy-mm-dd hh24:mi:ss)
v_organization_id 组织ID
tranid 事务ID,报表用 select jnreport.FDZL_TEMP_S.nextval from dual
exec dbms_stats.gather_table_stats('CRTNAME','SU_INV_MOQ',CASCADE=>TRUE);
***********************
** 未计算单位换算 **
***********************
---------------------
*/
last_trans_temp_in number;
last_trans_temp_out number;
curr_trans_temp number;
curr_rev_temp number;
curr_loc_temp number;
curr_item_temp number;
trans_temp number;
rev_temp number;
loc_temp number;
date_temp date;
item_temp number;
qty_temp number;
lot_temp varchar2(30);
lot_curr_trans_temp number;
lot_curr_rev_temp number;
lot_curr_loc_temp number;
lot_curr_item_temp number;
lot_curr_qty_temp number;
lot_curr_lot_temp varchar2(30);
lot_curr_trans_temp_b number;
lot_curr_rev_temp_b number;
lot_curr_loc_temp_b number;
lot_curr_item_temp_b number;
lot_curr_qty_temp_b number;
lot_curr_lot_temp_b varchar2(30);
lot_count number;
cur_moq inv.mtl_onhand_quantities%rowtype;
trans inv.mtl_material_transactions%rowtype;
last_trans inv.mtl_material_transactions%rowtype;
cursor c_cur_moq is select t.* from inv.mtl_onhand_quantities t
where t.organization_id = v_organization_id
and t.subinventory_code = v_subinventory_code
and t.cost_group_id = old_cost;
cursor c_trans is select t.* from inv.mtl_material_transactions t where t.organization_id = v_organization_id
and t.transaction_date >= to_date('2015-02-16','yyyy-mm-dd')
and t.subinventory_code = v_subinventory_code and t.cost_group_id = new_cost
order by t.transaction_id desc;
cursor c_lot_last_in is select distinct mmt.transaction_id,mmt.transaction_date,
mmt.inventory_item_id,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mtln.transaction_quantity
and sim.lot_number = mtln.lot_number
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity > 0
and mtln.lot_number = lot_curr_lot_temp_b
and nvl(mmt.revision,-1) = lot_curr_rev_temp_b
and nvl(mmt.locator_id,-1) = lot_curr_loc_temp_b
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = lot_curr_item_temp_b
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_cur_out is select distinct mmt.transaction_id,mmt.inventory_item_id,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,-mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.transaction_id = curr_trans_temp;
cursor c_lot_last_out is select distinct mmt.transaction_id,mmt.transaction_quantity,nvl(mmt.locator_id,-1) loc,
nvl(mmt.revision,-1) rev,mtln.transaction_quantity,mtln.lot_number
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and mmt.transaction_quantity < 0
and mmt.transaction_id = mtln.transaction_id
and mmt.subinventory_code = v_subinventory_code
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_last_in is select mmt.transaction_id,mmt.transaction_quantity,
mmt.transaction_date,mmt.inventory_item_id,
nvl(mmt.locator_id,-1) loc,nvl(mmt.revision,-1) rev
from inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mmt.transaction_quantity
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity > 0
and nvl(mmt.revision,-1) = curr_rev_temp
and nvl(mmt.locator_id,-1) = curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = curr_item_temp
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
cursor c_last_out is select mmt.transaction_id,mmt.transaction_quantity,
mmt.transaction_date,mmt.inventory_item_id,
nvl(mmt.locator_id,-1) loc,nvl(mmt.revision,-1) rev
from inv.mtl_material_transactions mmt
where mmt.organization_id = v_organization_id
and 0 =
(select count(rowid) from crtname.SU_INV_MOQ sim
where sim.organization_id = mmt.organization_id
and nvl(sim.locator_id,-1) = nvl(mmt.locator_id,-1)
and nvl(sim.revision,-1) = nvl(mmt.revision,-1)
and sim.transaction_quantity = mmt.transaction_quantity
and sim.inventory_item_id = mmt.inventory_item_id
and sim.create_transaction_id = mmt.transaction_id
)
and mmt.transaction_quantity < 0
and nvl(mmt.revision,-1) = curr_rev_temp
and nvl(mmt.locator_id,-1) = curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = curr_item_temp
and mmt.transaction_id < curr_trans_temp
order by mmt.transaction_id desc;
n number;
crt_trans_id number;
crt_qty number;
cur_qty number;
left_qty number;
last_trans_id number;
item_id number;
moq_su_tid number;
moq_su_qty number;
moq_su_left_qty number;
na number;
nb number;
nc number;
nd number;
begin
delete from crtname.SU_INV_MOQ; --最多30个报表同时运行
commit;
open c_cur_moq;
loop
fetch c_cur_moq into cur_moq;
exit when c_cur_moq%notfound;
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (cur_moq.INVENTORY_ITEM_ID,cur_moq.ORGANIZATION_ID,
cur_moq.DATE_RECEIVED,cur_moq.LAST_UPDATE_DATE,cur_moq.LAST_UPDATED_BY,
cur_moq.CREATION_DATE,cur_moq.CREATED_BY,cur_moq.LAST_UPDATE_LOGIN,
cur_moq.TRANSACTION_QUANTITY,cur_moq.SUBINVENTORY_CODE,cur_moq.REVISION,
cur_moq.LOCATOR_ID,cur_moq.CREATE_TRANSACTION_ID,cur_moq.UPDATE_TRANSACTION_ID,
cur_moq.LOT_NUMBER,cur_moq.ORIG_DATE_RECEIVED,cur_moq.COST_GROUP_ID,
cur_moq.CONTAINERIZED_FLAG,cur_moq.PROJECT_ID,cur_moq.TASK_ID,
tranid);
end loop;
close c_cur_moq;
commit;
open c_trans;
loop
fetch c_trans into trans;
--查询是否带批次入出库
exit when c_trans%notfound;
select count(mtln.transaction_id)
into lot_count
from inv.mtl_transaction_lot_numbers mtln
where mtln.transaction_id = trans.transaction_id;
if lot_count = 0 then --无批次号
if trans.TRANSACTION_QUANTITY > 0 then --无批次入库,库存值允许为负值
select nvl(max(ta.create_transaction_id),0),nvl(max(ta.transaction_quantity),0)
into moq_su_tid,moq_su_qty
from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if moq_su_qty>0 and trans.transaction_quantity = moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif moq_su_qty>0 and trans.transaction_quantity > moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
moq_su_left_qty := trans.transaction_quantity - moq_su_qty;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_out;
fetch c_last_out into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_out;
if -qty_temp <= moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif -qty_temp > moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity > 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty <= 0;
end loop;
elsif moq_su_qty = 0 then --无法找到入库记录
moq_su_left_qty := trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_out;
fetch c_last_out into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_out;
if -qty_temp <= moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif -qty_temp > moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity > 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty <= 0;
end loop;
end if;
-----------------------------------------------------------------
elsif trans.TRANSACTION_QUANTITY < 0 then --无批次出库
select nvl(max(ta.create_transaction_id),0),nvl(max(ta.transaction_quantity),0) --查询是否有负库存
into moq_su_tid,moq_su_qty
from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if moq_su_qty<0 and trans.transaction_quantity = moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif moq_su_qty<0 and trans.transaction_quantity < moq_su_qty then
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
moq_su_left_qty := trans.transaction_quantity - moq_su_qty;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= -moq_su_left_qty then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > -moq_su_left_qty then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
-moq_su_left_qty,v_subinventory_code,
decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
moq_su_left_qty := moq_su_left_qty + qty_temp;
exit when moq_su_left_qty >= 0;
end loop;
elsif moq_su_qty = 0 then --无法找到入库记录
--是否未发完
select count(ta.inventory_item_id)
into n
from crtname.SU_INV_MOQ ta
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if n=1 then
--查询总数量
select ta.create_transaction_id,ta.transaction_quantity
into crt_trans_id,cur_qty
from crtname.SU_INV_MOQ ta
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
select mmt.transaction_quantity
into crt_qty
from inv.mtl_material_transactions mmt
where mmt.transaction_id = crt_trans_id;
--更新现有量表数量
if cur_qty - trans.transaction_quantity < crt_qty then --现有量 + 出库数量 < 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = ta.transaction_quantity - trans.transaction_quantity
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
--查询最后一次记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(trans.revision,-1)
and nvl(mmt.locator_id,-1) = nvl(trans.locator_id,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = trans.inventory_item_id
and mmt.transaction_id < trans.TRANSACTION_ID;
--更新
update crtname.SU_INV_MOQ ta
set ta.update_transaction_id = last_trans_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif cur_qty - trans.transaction_quantity = crt_qty then --现有量 + 出库数量 = 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif cur_qty - trans.transaction_quantity > crt_qty then --涉及多条数据
na := crt_qty;
nb := cur_qty;
nc := trans.transaction_quantity;
nd := -nc -(na - nb);
--left_qty := (crt_qty-cur_qty) + trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
else
nd := -trans.transaction_quantity;
--left_qty := (crt_qty-cur_qty) + trans.transaction_quantity;
curr_trans_temp := trans.TRANSACTION_ID;
curr_rev_temp := nvl(trans.revision,-1);
curr_loc_temp := nvl(trans.locator_id,-1);
curr_item_temp := trans.inventory_item_id;
loop
open c_last_in;
fetch c_last_in into trans_temp,qty_temp,date_temp,item_temp,loc_temp,rev_temp;
close c_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
null,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
null,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
end if;
end if;
elsif lot_count >= 1 then --有批次号
if trans.TRANSACTION_QUANTITY > 0 then --入库则直接删除
delete from crtname.SU_INV_MOQ ta
where ta.create_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
elsif trans.TRANSACTION_QUANTITY < 0 then --出库有点麻烦
curr_trans_temp := trans.TRANSACTION_ID;
open c_cur_out; --本次出库分几个批次,每个批次分别哪个 货位 和 数量(负)
loop
fetch c_cur_out into lot_curr_trans_temp,lot_curr_item_temp,lot_curr_loc_temp,
lot_curr_rev_temp,lot_curr_qty_temp,lot_curr_lot_temp;
exit when c_cur_out%notfound;
--每个批次有没有出库完
select count(ta.inventory_item_id)
into n
from crtname.SU_INV_MOQ ta
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
if n=1 then
--查询总数量
select ta.create_transaction_id,ta.transaction_quantity
into crt_trans_id,cur_qty
from crtname.SU_INV_MOQ ta
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = trans.TRANSACTION_ID
and ta.TRANSID = tranid;
select max(mmt.transaction_quantity)
into crt_qty
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mtln.lot_number = lot_curr_lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.transaction_id = crt_trans_id;
--更新现有量表数量
if cur_qty - lot_curr_qty_temp < crt_qty then --现有量 + 出库数量 < 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = ta.transaction_quantity - lot_curr_qty_temp
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
--查询最后一次记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_curr_lot_temp
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = lot_curr_rev_temp
and nvl(mmt.locator_id,-1) = lot_curr_loc_temp
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = lot_curr_item_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.transaction_id < curr_trans_temp;
--更新
update crtname.SU_INV_MOQ ta
set ta.update_transaction_id = last_trans_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
elsif cur_qty - lot_curr_qty_temp = crt_qty then --现有量 + 出库数量 = 当初入库数量(只涉及一条数据)
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
elsif cur_qty - lot_curr_qty_temp > crt_qty then --涉及多条数据 5>3
na := crt_qty; --当初入库数量 3
nb := cur_qty; --当前现有量 2
nc := lot_curr_qty_temp; --本次出库数量 (负) -3
nd := -nc -(na - nb); --2
lot_curr_trans_temp_b := lot_curr_trans_temp;
lot_curr_rev_temp_b := lot_curr_rev_temp;
lot_curr_loc_temp_b := lot_curr_loc_temp;
lot_curr_item_temp_b := lot_curr_item_temp;
lot_curr_qty_temp_b := lot_curr_qty_temp;
lot_curr_lot_temp_b := lot_curr_lot_temp;
update crtname.SU_INV_MOQ ta
set ta.transaction_quantity = crt_qty,
ta.update_transaction_id = ta.create_transaction_id
where ta.lot_number = lot_curr_lot_temp
and ta.update_transaction_id = curr_trans_temp
and ta.TRANSID = tranid;
loop
open c_lot_last_in;
fetch c_lot_last_in into trans_temp,date_temp,item_temp,loc_temp,rev_temp,qty_temp,lot_temp;
close c_lot_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
else
nd := -lot_curr_qty_temp;
lot_curr_trans_temp_b := lot_curr_trans_temp;
lot_curr_rev_temp_b := lot_curr_rev_temp;
lot_curr_loc_temp_b := lot_curr_loc_temp;
lot_curr_item_temp_b := lot_curr_item_temp;
lot_curr_qty_temp_b := lot_curr_qty_temp;
lot_curr_lot_temp_b := lot_curr_lot_temp;
loop
open c_lot_last_in;
fetch c_lot_last_in into trans_temp,date_temp,item_temp,loc_temp,rev_temp,qty_temp,lot_temp;
close c_lot_last_in;
if qty_temp <= nd then
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
qty_temp,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,trans_temp,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
elsif qty_temp > nd then
--查询最后一次出库记录
select max(mmt.transaction_id)
into last_trans_id
from inv.mtl_transaction_lot_numbers mtln,inv.mtl_material_transactions mmt
where mmt.transaction_quantity < 0
and mtln.lot_number = lot_temp
and mmt.transaction_id = mtln.transaction_id
and mmt.organization_id = v_organization_id
and nvl(mmt.revision,-1) = nvl(rev_temp,-1)
and nvl(mmt.locator_id,-1) = nvl(loc_temp,-1)
and mmt.subinventory_code = v_subinventory_code
and mmt.inventory_item_id = item_temp
and (mmt.transaction_id > trans_temp and mmt.transaction_id < curr_trans_temp);
insert into crtname.SU_INV_MOQ (INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,
CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,
LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,
TASK_ID,TRANSID) values (item_temp,v_organization_id,date_temp,
date_temp,trans.created_by,date_temp,trans.created_by,-1,
nd,v_subinventory_code,decode(rev_temp,-1,null,rev_temp),decode(loc_temp,-1,null,loc_temp),
trans_temp,last_trans_id,
lot_temp,date_temp,old_cost,2,null,
null,tranid);
end if;
nd := nd - qty_temp;
exit when nd <= 0;
end loop;
end if;
end loop;
close c_cur_out;
end if;
end if;
end loop;
close c_trans;
commit;
insert into crtname.MOQA(INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,TASK_ID)
select INVENTORY_ITEM_ID,ORGANIZATION_ID,DATE_RECEIVED,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,TRANSACTION_QUANTITY,SUBINVENTORY_CODE,REVISION,LOCATOR_ID,CREATE_TRANSACTION_ID,UPDATE_TRANSACTION_ID,LOT_NUMBER,ORIG_DATE_RECEIVED,COST_GROUP_ID,CONTAINERIZED_FLAG,PROJECT_ID,TASK_ID
from crtname.SU_INV_MOQ;
commit;
return (0);
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28692050/viewspace-1988466/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28692050/viewspace-1988466/