三个作业去去去
wdj.wip_entity_id 工单头id,wro.operation_seq_num 工序,wdj.organization_id 组织id。l_available_qty := XXFIN_HTML_CST_REPORT.Get_Available_Qty(t.物料id,(r.工单头id,r.工序,r.组织id,t.物料id,t.批次,t.批次数量,t.供应子库,'个',35);(r.工单
create or replace package body Three_job is
PROCEDURE AAAAA(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
entityName IN VARCHAR2) IS
lack number;--缺料数量
lack2 number;
l_available_qty NUMBER;
l_available_qty2 NUMBER;
v_sep varchar2(500);
p_org_id number;
v_org_name varchar2(500);
v_line_str varchar2(500);
v_line_str_1 varchar2(500);
v_line number;
p_err_msg varchar2(500);
--l_mtl_txn_rec varchar2(500);
l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
l_mtl_txn_lot_rec mtl_transaction_lots_interface%ROWTYPE;
l_item_cost number;
x_err_msg varchar2(500);
l_retval number;
p_code varchar2(10);
l_msg_count number;
l_trans_count number;
--发料失败
CURSOR c_errs(p_transaction_interface_id IN NUMBER) IS
SELECT mti.error_code, mti.error_explanation
FROM mtl_transactions_interface mti
WHERE mti.transaction_interface_id = p_transaction_interface_id;
--根据工单查未完工物料
cursor re is
select wro.segment1 物料,
wdj.attribute1 项目号,
wro.mps_required_quantity - wro.quantity_issued 缺料数量,
wdj.wip_entity_id 工单头id,
wro.operation_seq_num 工序,
wdj.organization_id 组织id
from WIP_DISCRETE_JOBS_V wdj
inner join WIP_REQUIREMENT_OPERATIONS wro
on wdj.wip_entity_id = wro.wip_entity_id
where wdj.wip_entity_name = entityName
and wdj.status_type = 4
and wro.mps_required_quantity - wro.quantity_issued > 0;
--临时表
cursor issue_data is
select * from ops2;
--不携带项目号
cursor re2 is
select wro.segment1 物料号,
wdj.attribute1 项目号,
wro.mps_required_quantity - wro.quantity_issued 缺料数量,
wdj.wip_entity_id 工单头id,
wro.operation_seq_num 工序,
wdj.organization_id 组织id
from WIP_DISCRETE_JOBS_V wdj
inner join WIP_REQUIREMENT_OPERATIONS wro
on wdj.wip_entity_id = wro.wip_entity_id
where wdj.wip_entity_name = entityName
and wdj.status_type = 4
and wro.mps_required_quantity - wro.quantity_issued > 0;
begin
--清空临时表
--EXECUTE IMMEDIATE 'TRUNCATE TABLE ops2';
--for循环游标
for r in re LOOP
lack := r.缺料数量;
--for循环批次
for t in (select moqd.lot_number 批次,
moqd.primary_transaction_quantity 批次数量,
moqd.subinventory_code 供应子库,
msib.inventory_item_id 物料id,
moqd.locator_id 货位,
moqd.TRANSACTION_UOM_CODE 单位,
SUBSTR(moqd.lot_number,
INSTR(moqd.lot_number, '[') + 1,
INSTR(moqd.lot_number, ']') -
INSTR(moqd.lot_number, '[') - 1) 项目号
from mtl_system_items_b msib
inner join mtl_onhand_quantities_detail moqd
on msib.inventory_item_id = moqd.inventory_item_id
where msib.segment1 = r.物料 --外层游标循环出的物料
and msib.organization_id = 247
and SUBSTR(moqd.lot_number,
INSTR(moqd.lot_number, '[') + 1,
INSTR(moqd.lot_number, ']') -
INSTR(moqd.lot_number, '[') - 1) = r.项目号
order by msib.creation_date) LOOP
--查询真实批次物料数量
l_available_qty := XXFIN_HTML_CST_REPORT.Get_Available_Qty(t.物料id,
r.组织id,
t.供应子库,
t.货位,
null,
p_Lot_Number => t.批次);
if l_available_qty >= lack then
--wdj.wip_entity_id 工单头id
--wro.operation_seq_num 工序
--wdj.organization_id 组织id
--msib.inventory_item_id 物料id
--moqd.lot_number 批次
--发料数量=缺料数量
--moqd.subinventory_code 供应子库
insert into ops2
(wei, osn, oi, iii, lnn, nums, sc, um, tt)
values
(r.工单头id,
r.工序,
r.组织id,
t.物料id,
t.批次,
lack,
t.供应子库,
t.单位,
35);
EXIT;
ELSIF l_available_qty < lack then
--发料数量=批次数量
insert into ops2
(wei, osn, oi, iii, lnn, nums, sc, um, tt)
values
(r.工单头id,
r.工序,
r.组织id,
t.物料id,
t.批次,
--t.批次数量,
l_available_qty,--批次数量不一定是真实数据，所以不能用批次数量
t.供应子库,
t.单位,
35);
--减小缺量数量的值
lack := lack - l_available_qty;
END IF;
end LOOP;
end LOOP;
--第二次循环
--for循环物料
for r in re2 loop
lack2 := r.缺料数量;
--for循环批次
for sd in (select mbb.lot_number 批次,
mbb.primary_transaction_quantity 批次数量,
mbb.subinventory_code 供应子库,
mss.inventory_item_id 物料id,
mbb.locator_id 货位,
mbb.TRANSACTION_UOM_CODE 单位,
SUBSTR(mbb.lot_number,
INSTR(mbb.lot_number, '[') + 1,
INSTR(mbb.lot_number, ']') -
INSTR(mbb.lot_number, '[') - 1) 项目号
from mtl_system_items_b mss
inner join mtl_onhand_quantities_detail mbb
on mss.inventory_item_id = mbb.inventory_item_id
where mss.segment1 = r.物料号
-- and mbb.subinventory_code like 'XB%'
and mss.organization_id = 247
and nvl(SUBSTR(mbb.lot_number,
INSTR(mbb.lot_number, '[') + 1,
INSTR(mbb.lot_number, ']') -
INSTR(mbb.lot_number, '[') - 1),
1) != r.项目号
order by mss.creation_date) LOOP
--查询真实库存
l_available_qty2 := XXFIN_HTML_CST_REPORT.Get_Available_Qty(sd.物料id,
r.组织id,
sd.供应子库,
sd.货位,
null,
p_Lot_Number => sd.批次);
--将数据放入临时表
if l_available_qty2 >= lack2 then
--wdj.wip_entity_id 工单头id
--wro.operation_seq_num 工序
--wdj.organization_id 组织id
--msib.inventory_item_id 物料id
--moqd.lot_number 批次
--发料数量=缺料数量
--moqd.subinventory_code 供应子库
insert into ops2
(wei, osn, oi, iii, lnn, nums, sc, um, tt)
values
(r.工单头id,
r.工序,
r.组织id,
sd.物料id,
sd.批次,
lack2,
sd.供应子库,
sd.单位,
35); --缺料数量
EXIT;
ELSIF l_available_qty2 < lack2 then
--发料数量=批次数量
insert into ops2
(wei, osn, oi, iii, lnn, nums, sc, um, tt)
values
(r.工单头id,
r.工序,
r.组织id,
sd.物料id,
sd.批次,
--sd.批次数量,
l_available_qty2,
sd.供应子库,
sd.单位,
35); --物料批次数量
--减小缺量数量的值
lack2 := lack2 - l_available_qty2;
END IF;
end loop; --一循环
end loop; --二循环
--发料
FOR issue IN issue_data LOOP
p_err_msg := NULL;
l_mtl_txn_rec := NULL;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_txn_rec.transaction_interface_id
FROM dual;
l_mtl_txn_rec.transaction_header_id := l_mtl_txn_rec.transaction_interface_id;
l_mtl_txn_rec.transaction_source_id := issue.wei; --工单头id
l_mtl_txn_rec.operation_seq_num := issue.osn; --工序
l_mtl_txn_rec.inventory_item_id := issue.iii; --物料id
l_mtl_txn_rec.organization_id := issue.oi; --组织id
l_mtl_txn_rec.subinventory_code := issue.sc; --供应子库
l_mtl_txn_rec.transaction_quantity := -issue.nums; --发料数量
l_mtl_txn_rec.transaction_uom := issue.um; --单位
l_mtl_txn_rec.transaction_date := to_date(to_char(sysdate,
'YYYY-MM-DD hh24:mi:ss'),
'YYYY-MM-DD hh24:mi:ss'); --fnd_date.canonical_to_date(issue.transaction_date);
l_mtl_txn_rec.transaction_type_id := issue.tt; --物料事物类型 这里你自己写死 数字 35 就行
l_mtl_txn_rec.transaction_source_type_id := 5; --生产管理
l_mtl_txn_rec.source_code := 'XXMFG_WIP_ISSUE_IMP_PKG';
l_mtl_txn_rec.source_line_id := issue.wei;
l_mtl_txn_rec.source_header_id := issue.wei;
l_mtl_txn_rec.creation_date := SYSDATE;
l_mtl_txn_rec.created_by := fnd_global.user_id;
l_mtl_txn_rec.last_update_date := SYSDATE;
l_mtl_txn_rec.last_updated_by := fnd_global.user_id;
l_mtl_txn_rec.last_update_login := fnd_global.login_id;
/************transaction_mode*********************
* transaction_mode
*2 Concurrent, Process transaction interface不处理，
* 需要程序调用Inventory transaction worker处理
*3 Background, 由Process transaction interface处理
***************************************************/
l_mtl_txn_rec.transaction_mode := 3;
l_mtl_txn_rec.process_flag := 1;
IF issue.lnn IS NOT NULL THEN
--Lot Number 处理
l_mtl_txn_lot_rec := NULL;
l_mtl_txn_lot_rec.lot_number := issue.lnn;
l_mtl_txn_lot_rec.transaction_quantity := l_mtl_txn_rec.transaction_quantity;
l_mtl_txn_lot_rec.transaction_interface_id := l_mtl_txn_rec.transaction_interface_id;
l_mtl_txn_lot_rec.creation_date := SYSDATE;
l_mtl_txn_lot_rec.created_by := fnd_global.user_id;
l_mtl_txn_lot_rec.last_update_date := SYSDATE;
l_mtl_txn_lot_rec.last_updated_by := fnd_global.user_id;
l_mtl_txn_lot_rec.last_update_login := fnd_global.login_id;
INSERT INTO mtl_transaction_lots_interface
VALUES l_mtl_txn_lot_rec;
END IF;
INSERT INTO mtl_transactions_interface VALUES l_mtl_txn_rec;
select nvl(max(ITEM_COST), 0)
into l_item_cost
from cst_item_costs cic, mtl_system_items_b msi
where cic.inventory_item_id = issue.iii
and cic.inventory_item_id = msi.inventory_item_id
and cic.organization_id = msi.organization_id
and msi.costing_enabled_flag = 'Y'
and cic.organization_id = issue.oi;
x_err_msg := null;
if l_item_cost = 0 then
l_retval := 1;
x_err_msg := '@物料标准成本为 0，请联系成本科维护标准成本！';
else
l_retval := inv_txn_manager_pub.process_transactions(p_api_version => 1,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
--not commit
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => p_code,
x_msg_count => l_msg_count,
x_msg_data => p_err_msg,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => l_mtl_txn_rec.transaction_header_id);
end if;
/* IF l_retval <> 0 THEN
IF x_err_msg is not null THEN
update XXMFG_AUTO_ISSUE_TMP2 xai
set xai.err_msg = '发料失败！' || x_err_msg
where xai.wip_entity_id = issue.wei
and xai.item_id = issue.iii
and xai.operation_seq_num = issue.osn;
else
FOR r_err IN c_errs(l_mtl_txn_rec.transaction_interface_id) LOOP
p_err_msg := p_err_msg || r_err.error_code || ':' ||
r_err.error_explanation;
END LOOP;
ROLLBACK;
update XXMFG_AUTO_ISSUE_TMP2 xai
set xai.err_msg = p_err_msg
where xai.wip_entity_id = issue.wei
and xai.item_id = issue.iii
and xai.operation_seq_num = issue.osn;
END IF;
ELSE
update XXMFG_AUTO_ISSUE_TMP2 xai
set xai.err_msg = '发料成功！'
where xai.wip_entity_id = issue.wei
and xai.item_id = issue.iii
and xai.operation_seq_num = issue.osn;
END IF;*/
DELETE FROM mtl_transactions_interface mti
WHERE mti.transaction_header_id =
l_mtl_txn_rec.transaction_header_id;
DELETE FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id =
l_mtl_txn_rec.transaction_interface_id;
END LOOP;
COMMIT;
end AAAAA;
end Three_job;
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
4
0- 0
已为社区贡献1条内容
所有评论(0)