库存临时表应用实例

最新推荐文章于 2021-04-16 06:00:01 发布

cmf46457

最新推荐文章于 2021-04-16 06:00:01 发布

阅读量184

点赞数

CREATE OR REPLACE PROCEDURE CUXWIP_TMP(errbuf OUT VARCHAR2,
JETCODE OUT number) IS
----------------------------------外部参数
p_date DATE;
p_trx_type_id number := 105;--生产批量发料 ---121子库存转移
p_item_id number;
p_subinv varchar2(10);
p_Locator_id number;
p_subinv_to varchar2(10);
p_Locator_id_to number;
p_transaction_quantity number;
p_source_id number;
p_issue_line_id number;
p_user_id number;
----------------------------------外部参数

g_org_id NUMBER := 84; ---**组织ID
--x_acct_id NUMBER;
--x_request_id NUMBER;
--x_qty_disp NUMBER;
--x_oraphase VARCHAR2(240);
--x_return VARCHAR2(240);
--x_phase VARCHAR2(240);
--x_status VARCHAR2(240);
--x_message VARCHAR2(240);
--x_req_status BOOLEAN;
--x_precision NUMBER;
g_period_id number;
g_group_id number;
g_cost number;
/* g_item_id number := 944;*/ ---****物料ID--23884;
g_mtx mtl_material_transactions_temp%ROWTYPE;
/*g_user_id number := -1; --**事务处理人员*/

--g_trx_type_id number := 121; --***事务处理类型,子库存转移--41-----账户别名接收
g_serial_prefix varchar2(30);
g_lot_prefix varchar2(30);
v_data_count number; ---统计表数据
cursor C_transaction_data is
select ctt.transaction_date,
ctt.inventory_item_id,
--ctt.organization_id,
ctt.subinventory_code,
ctt.locator_id,
ctt.transfer_subinventory,
ctt.transfer_to_location,
ctt.transaction_quantity,
--ctt.message,
ctt.source_id,
ctt.issue_line_id,
ctt.created_by
from CUXWIP_TRANSACTON_TEMP ctt
where ctt.message is null;
BEGIN

select count(*) INTO v_data_count from CUXWIP_TRANSACTON_TEMP;
if v_data_count = 0 then
fnd_file.put_line(2,
'临时表CUXWIP_TRANSACTON_TEMP没有任何的数据，请检查是否已提交“导入临时表的程序”');
RETURN;
end if;

OPEN C_transaction_data;
LOOP
FETCH C_transaction_data
INTO p_date, p_item_id, p_subinv, p_Locator_id, p_subinv_to, p_Locator_id_to, p_transaction_quantity, p_source_id, p_issue_line_id, p_user_id;
EXIT WHEN C_transaction_data%NOTFOUND;
begin
select acct_period_id
into g_period_id
from org_acct_periods oap
where oap.organization_id = 84 /*g_org_id*/
and p_date between oap.period_start_date and
oap.schedule_close_date;
exception
when others then
fnd_file.put_line(2, sqlerrm);
end;
begin
select cic.item_cost
into g_cost
from Cst_Item_Cost_Type_v cic
where cic.organization_id = 84
and cic.inventory_item_id = p_item_id --408
and cic.cost_type_id = 1020;
exception
when others then
fnd_file.PUT_LINE(2, sqlerrm);
end;
BEGIN
select mtl_material_transactions_s.nextval into g_group_id from dual;
Exception
when others then
fnd_file.put_line(2, sqlerrm);
END;

--x_trx_qty := 1; ----****事务处理数量
--g_trx_type_id := 121; --子库存转移

g_mtx.allowed_units_lookup_code := 3;
g_mtx.created_by := p_user_id;
g_mtx.creation_date := p_date;
g_mtx.last_update_date := p_date;
g_mtx.last_update_login := p_user_id;
g_mtx.last_updated_by := p_user_id;
g_mtx.final_completion_flag := 'N';

BEGIN
select organization_id,
inventory_item_id,
description,
a.inventory_asset_flag,
a.location_control_code,
a.lot_control_code,
a.primary_uom_code,
a.restrict_subinventories_code,
a.restrict_locators_code,
a.revision_qty_control_code,
a.segment1,
a.serial_number_control_code,
a.shelf_life_code,
a.shelf_life_days,
a.mtl_transactions_enabled_flag,
a.primary_uom_code,
a.auto_serial_alpha_prefix,
a.auto_lot_alpha_prefix
into g_mtx.organization_id,
g_mtx.inventory_item_id,
g_mtx.item_description,
g_mtx.item_inventory_asset_flag,
g_mtx.item_location_control_code,
g_mtx.item_lot_control_code,
g_mtx.item_primary_uom_code,
g_mtx.item_restrict_subinv_code,
g_mtx.item_restrict_locators_code,
g_mtx.item_revision_qty_control_code,
g_mtx.item_segments,
g_mtx.item_serial_control_code,
g_mtx.item_shelf_life_code,
g_mtx.item_shelf_life_days,
g_mtx.item_trx_enabled_flag,
g_mtx.transaction_uom,
g_serial_prefix,
g_lot_prefix
from mtl_system_items_b a
where organization_id = g_org_id
and inventory_item_id = p_item_id;
Exception
when others then
fnd_file.put_line(2, sqlerrm);
END;
g_mtx.lock_flag := 'N';
g_mtx.posting_flag := 'Y';
g_mtx.process_flag := 'Y';
g_mtx.source_line_id := -1;
g_mtx.transaction_header_id := g_group_id;
g_mtx.transaction_mode := 3;
g_mtx.transaction_status := 1;
g_mtx.transaction_temp_id := g_group_id;
BEGIN
select transaction_type_id,
transaction_action_id,
transaction_source_type_id
into g_mtx.transaction_type_id,
g_mtx.transaction_action_id,
g_mtx.transaction_source_type_id
from mtl_transaction_types a
where a.transaction_type_id = p_trx_type_id;
Exception
when others then
fnd_file.put_line(2, sqlerrm);
END;

g_mtx.acct_period_id := g_period_id;
g_mtx.primary_quantity := p_transaction_quantity; --x_trx_qty;
g_mtx.subinventory_code := p_subinv;
g_mtx.supply_locator_id := '';
g_mtx.transaction_date := p_date;
g_mtx.transaction_cost := g_cost; --1.19658;
g_mtx.transaction_quantity := p_transaction_quantity; --x_trx_qty;
g_mtx.transaction_source_id := ''; --子库存转移时为空
g_mtx.transaction_source_name := '';
g_mtx.operation_seq_num := '';
g_mtx.department_id := '';
g_mtx.wip_entity_type := '';
g_mtx.distribution_account_id := ''; -- 1220;
g_mtx.TRANSFER_SUBINVENTORY := p_subinv_to; --'913'; --转移至子库
g_mtx.TRANSFER_ORGANIZATION := 84;
g_mtx.TRANSFER_TO_LOCATION := p_Locator_id; -- ''; --转移至货位ID
g_mtx.LOCATOR_ID := p_Locator_id_to; --''; --转移货位ID
begin
insert into mtl_material_transactions_temp values g_mtx;
IF SQLCODE = 0 THEN
UPDATE CUXWIP_TRANSACTON_TEMP CT
SET CT.MESSAGE = 'OK'
where ct.source_id = p_source_id
and ct.issue_line_id = p_issue_line_id;
fnd_file.PUT_LINE(2,
'成功导入CUXWIP_TRANSACTON_TEMP表中ID为' ||
p_issue_line_id || '的行');
errbuf := '成功导入CUXWIP_TRANSACTON_TEMP表中ID为' ||
p_issue_line_id || '的行' ;
else
rollback;
fnd_file.PUT_LINE(2,
'将表CUXWIP_TRANSACTON_TEMP中数据发生异常，表中ISSUE_LINE_ID是' ||
p_issue_line_id);
errbuf := '将表CUXWIP_TRANSACTON_TEMP中数据发生异常，表中ISSUE_LINE_ID是' ||
p_issue_line_id ;
END IF;

--rollback;
commit;
exception
when others then
fnd_file.put_line(2, sqlerrm);
rollback;
end;

END LOOP;
COMMIT;
CLOSE C_transaction_data;
JETCODE := 1 ;
END CUXWIP_TMP;